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))

Thank you for your help in advance.2015-09-25_9-31-26.jpg
robthomas09Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ste5anSenior 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;

Open in new window

Brian CroweDatabase AdministratorCommented:
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

Open in new window

PortletPaulEE Topic AdvisorCommented:
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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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

Open in new window

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
;

Open in new window

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 |

Open in new window

also see: http://sqlfiddle.com/#!6/e7c59/1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
robthomas09, do you still need help with this question?
robthomas09Author Commented:
Sorry fellas got pulled to another project and this got put on hold.  Thanks for all help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.