How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be static values. I know the formula that I want to calculate each row value with.

Below is the DDL for my view

CREATE VIEW [v_AMP_C] AS

    SELECT     in.I_Date  ,--Date
               in.I_O_P   ,--Money
               in.I_O_H   ,--Money
               in.I_O_L   ,--Money
               in.I_C_O   ,--Money 
               c.AMPS12_C  --Money
    FROM       dbo.IC_Raw_In in
    INNER JOIN dbo.AMPS12_C c ON in.I_Serial = c.i_serial

and the data is imported to this table by using a bulk insert dbo.IC_Raw_In with data type of Money on all columns except I_Date.

Then when I ran this query SELECT * FROM v_AMP_C I got the below as output

```
I_Date I_O_P I_O_H I_O_L I_C_O AMPS12_C
01/10/11 509.75 515 508 512.45 512.45
01/10/11 511.7 511.7 506.1499 506.5499 509.4999
01/10/11 507.1499 510.25 507.1499 510.25 509.7499
01/10/11 510 512.3499 509.2999 512.3499 510.3999
01/10/11 512.5 512.5 511.1499 512 510.7199
01/10/11 512.25 512.5 510.1 510.95 510.7583
01/10/11 510.5499 511.7999 510 511.7999 510.9071
01/10/11 511.1 511.85 508.1499 508.8999 510.6562
01/10/11 508.8999 510 508.5 509.95 510.5777
01/10/11 509.8999 509.8999 508.5 508.85 510.4049
01/10/11 509.5 511.2 509 510.5 510.4136
01/10/11 510.5 511.7999 510.1 510.2 510.3958
01/10/11 510.2999 511.35 510.25 510.75 510.2541
01/10/11 510.35 512 510.35 510.95 510.6208
01/10/11 510.95 511.7999 510.6 511.1 510.6916
01/10/11 511.0499 511.35 509.1 509.1 510.4208
01/10/11 509.5 509.5 508.1 508.5 510.1291
01/10/11 508.45 508.95 507 507 509.7999
01/10/11 507 508.2 503.2999 503.2999 509.0916
01/10/11 504 505

So this isn't a great answer, this is kind of a starting answer for somebody else to take on and refine this better. But I'll make a stab at it.

First I have a question: Are you trying to retain this in a view? I don't think you can for what you're wanting to do, it's kinda complicated, so let's examine the operations that you need to do to actually do what you want.

You stated that you want the first 12 rows to be static every time, and they should always have their last column set as NULL, and the others should retain their value. So that's a business rule that we need to encode in SQL. But before we encode this as a rule, let's ask if there's a way to ENSURE that those 12 rows are the RIGHT rows every time. If we can make that assumption, then we can do this as part of the next step.

You're next requirement is to do a calculation on each row with the previous row. Since the first 12 rows are static (and I presume not calculated) then we don't have to ask "what about the first row". So the easiest way to do calculations on the previous row is to assign a rownum to each row, then use the rownum ID in a comparison. This meshes with the previous requirement.

So we should start by doing our select and assigning a rownum as well, like this:

SELECT     
    ROW_NUMBER() OVER (ORDER BY in.I_Date) AS rownum,
    in.I_Date  ,--Date
    in.I_O_P   ,--Money
    in.I_O_H   ,--Money
    in.I_O_L   ,--Money
    in.I_C_O   ,--Money 
    c.AMPS12_C  --Money
    CAST(0.0 AS Money) AS C12WR
FROM
    dbo.IC_Raw_In in
INNER JOIN 
    dbo.AMPS12_C c ON in.I_Serial = c.i_serial

But for the way I would do this, I would funnel these values into a temp table, and then use that to work out what I need. That way you can just refer to the columns in subsequent calls, like this:

UPDATE t 
SET C12WR = NULL
FROM temptable t
WHERE t.rownum < 12 -- see how we set the values = null here?

UPDATE t 
SET C12WR = 510.3958
FROM temptable t
WHERE t.rownum = 12 -- see how we set the value to something static? 
                    -- If this were a stored procedure we could use a value passed in here

and then we continue with:

UPDATE t 
SET C12WR = ( ( t2.C12WR * 11.0 ) + t.I_C_O ) / 12.0
FROM temptable t
INNER JOIN temptable t2 ON t.rownum = (t2.rownum - 1) -- this let's us get the previous row
WHERE t.rownum > 12

Using this logic: After the 13th row, the C12WR column = (prevrow.C12WR * 11 + currow.I_C_O Column) / 12

And then you would just return the values that you wanted from the temptable.

Notice: the things I left off. I did not define the temp table, I did not get rid of the temptable. I did not use appropriate syntax for the temptable addressing. I did not validate anything. I presumed that this was going to be used in a stored procedure. I did not illustrate how to use the static value as a stored procedure passed parameter.

Hope this helps. Hope someone else helps make this a better answer ;)

StackExchange Database Administrators Q#1791, answer score: 4