# Running calculation on a table

Hello Experts,
I am needing to perform a certain calculation on a table based upon an id column.  The "id" column on the table is column that the calculation needs to be based upon.  The calculation can only be run on an id value in the id column that has at least two entries in the table.  Also the two highest values in the rowno column (I have highlighted the rows in the sample tables attached to this post for demostration) are the rows that are needed for the calculation.  The desired output format is the table labeled Desired Output on sample tables.

For example for id value = bb the high values would be rowno = 5 and the low values would be rowno=4.

Calculation Requirements -
Highest Row number
@highquantity = quantity
@highrenewals = renewals
@highstart_date = start_date

Next Highest Row number
@lowquantity = quantity
@lowrenewals = renewals
@lowstart_date = start_date

Calculation itself:
(@highquantity x @highrenewals) / ((@lowstart_date - @highstart_date) + (@lowquantity x @lowrenewals))

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
Well, I'm not sure that I understand it..

But it seems that ROW_NUMBER() would do it.

WITH    Ordered
AS ( SELECT   * ,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY RowNo DESC ) AS RN
FROM     yourTable
)
SELECT  L.ID ,
L.column1 * R.column2 AS SomeMath
FROM    Ordered L
INNER JOIN Ordered R ON R.RN = L.RN - 1;
try this...

WITH cteTable AS
(
SELECT id, start_date, quantity, renewals, rowno, id_2,
ROW_NUMBER(PARTITION BY id ORDER BY rowno DESC) AS RowNumber
FROM MyTable
)
SELECT T1.id,
T1.quantity * T1.renewals / (DATEDIFF(DAY, T2.start_date, T1.start_date) + (T2.quantity * T2.Renewals)),
T1.id_2
FROM cteTable AS T1
INNER JOIN cteTable AS T2
ON T1.id = T2.id
AND T2.RowNumber = 2
WHERE T1.RowNumber = 1
What a pity you only provided an image of the data, I don't enjoy recreating your data to test with.

Also, can you confirm which version of MSSQL Sever you actually use please (e.g. MSSQL 2012 has features MSSQL 2008 does not)
More than just a use of row_number() is required I believe.

I suggest this to gather the wanted rows:
SELECT ... (see next code block)
FROM (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY start_date DESC) AS rowno
, COUNT(*) OVER (PARTITION BY id) AS numrows
FROM MyTable
) AS d
WHERE numrows >= 2
AND rowno IN (1, 2)
GROUP BY id
, id_2
Using COUNT(*) OVER(...) ensures we only get those id values that have 2 or more rows. Using descending order: ROW_NUMBER() OVER(.... ORDER BY start_date DESC)  ensures we get the most recent and second most recent rows for the id.

Once we have the right records, then a GROUP BY is needed and liberal use of MAX( case when rowno = 1 (or 2) ) to collapse the output to 1 row per id/id_2 and perform the wanted calculation.
SELECT
id
, id_2
, MAX(CASE
WHEN rowno = 1 THEN quantity
END) highquantity
, MAX(CASE
WHEN rowno = 1 THEN renewals
END) highrenewals
, DATEDIFF(DAY, MAX(CASE
WHEN rowno = 2 THEN START_DATE
END)
, MAX(CASE
WHEN rowno = 1 THEN START_DATE
END)
) start_date_diff
, MAX(CASE
WHEN rowno = 2 THEN quantity
END) lowquantity
, MAX(CASE
WHEN rowno = 2 THEN renewals
END) lowrenewals
/* (@highquantity x @highrenewals) / ((@lowstart_date - @highstart_date) + (@lowquantity x @lowrenewals)) */
, (MAX(CASE
WHEN rowno = 1 THEN quantity
END)
* MAX(CASE
WHEN rowno = 1 THEN renewals
END))
/
DATEDIFF(DAY, MAX(CASE
WHEN rowno = 2 THEN START_DATE
END)
, MAX(CASE
WHEN rowno = 1 THEN START_DATE
END) )
+
(MAX(CASE
WHEN rowno = 2 THEN quantity
END)
* MAX(CASE
WHEN rowno = 2 THEN renewals
END)) the_calculation
FROM (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY start_date DESC) AS rowno
, COUNT(*) OVER (PARTITION BY id) AS numrows
FROM MyTable
) AS d
WHERE numrows >= 2
AND rowno IN (1, 2)
GROUP BY id
, id_2
;
CREATE TABLE MyTable
([id] varchar(2), [start_date] date, [quantity] int, [renewals] int, [id_2] varchar(2))
;

INSERT INTO MyTable
([id], [start_date], [quantity], [renewals], [id_2])
VALUES
('aa', '20150707', 60, 1,'a1'),
('aa', '20150128', 30, 2,'a1')
;

| id | id_2 | highquantity | highrenewals | start_date_diff | lowquantity | lowrenewals | the_calculation |
|----|------|--------------|--------------|-----------------|-------------|-------------|-----------------|
| aa |   a1 |           60 |            1 |             160 |          30 |           2 |              60 |
also see: http://sqlfiddle.com/#!6/e7c59/1

Experts Exchange Solution brought to you by