Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

sql serversql-server-2005sql-server-2008

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      503.5    504.6499  508.7374
01/10/11 505.45    506.35   504      504.7     508.2999
01/10/11 504.7     505.5    504.2    505.5     508.0208
01/10/11 505.35    505.7    503.1    503.6499  507.4499
01/10/11 504.5     504.5    499.5499 500.5     506.6416
01/10/11 500.45    502      500.25   501       505.8291
01/10/11 501       501.2999 499.5499 500.3999  504.9499
01/10/11 500.45    500.7999 498.6499 498.6499  503.9124
01/10/11 498.7     499.25   498.0499 498.35    503.0166
01/10/11 498.75    499.95   498.7    499       502.2249
01/10/11 499.25    499.6499 498.6499 499.45    501.5957
01/10/11 499.2999  501.1499 499.1    500.8999  501.3957
01/10/11 501.1     502.5    500.5499 502.5     501.2166
01/10/11 502.35    502.95   501      501.5     500.9499
01/10/11 501.5     501.5    500      500.5     500.5333
01/10/11 500       501.35   499.5    499.7999  500.2124
01/10/11 499.95    500.3999 499.2999 500.2999  500.1957
01/10/11 500       501.3999 499.5    499.6499  500.0832
01/10/11 499.7999  501.25   499.6499 500.0499  500.0541

Now I want write a SQL Query to get results in new column called C12WR for the below quetion.

I want to exclude (use NULL) for the first 11 rows in C12WR Column, and in the 12th row of C12WR column "use a static value which is in AMPS12_C (In the above shown table results the value is 510.3958 marked as bold). This value will change every time I import the data to my table so this will change dynamically every time. And in the AMPS12_C Column it should calculate the below formula after 13th row to end of the table.

After the 13th row, the C12WR column = (the value of above row (that is currunt row number -1) from C12WR * 11 + current row value from I_C_O Column) / 12

So if I caluculate it should represent above formulas as below. (I don't want to use any static values but for the example of this formula I am taking a static values here just for the ease of explanation)

= ( 510.3958 * 11 + 510.2 ) / 12

And after I run the desired query I should get the output similar to this:

I_Date   I_O_P       I_O_H       I_O_L       I_C_O       AMPS12_C    C12WR
01/10/11 509.75      515         508         512.4500122 512.45      NULL
01/10/11 511.7000122 511.7000122 506.1499939 506.5499878 509.4999    NULL
01/10/11 507.1499939 510.25      507.1499939 510.25      509.7499    NULL
01/10/11 510         512.3499756 509.2999878 512.3499756 510.3999    NULL
01/10/11 512.5       512.5       511.1499939 512         510.7199    NULL
01/10/11 512.25      512.5       510.1000061 510.9500122 510.7583    NULL
01/10/11 510.5499878 511.7999878 510         511.7999878 510.9071    NULL
01/10/11 511.1000061 511.8500061 508.1499939 508.8999939 510.6562    NULL
01/10/11 508.8999939 510         508.5       509.9500122 510.5777    NULL
01/10/11 509.8999939 509.8999939 508.5       508.8500061 510.4049    NULL
01/10/11 509.5       511.2000122 509         510.5       510.4136    NULL
01/10/11 510.5       511.7999878 510.1000061 510.2000122 510.3958333 510.3958333
01/10/11 510.2999878 511.3500061 510.25      510.75      510.2541657 510.3795149
01/10/11 510.3500061 512         510.3500061 510.9500122 510.6208344 510.4103887
01/10/11 510.9500122 511.7999878 510.6000061 511.1000061 510.6916682 510.4553573
01/10/11 511.0499878 511.3500061 509.1000061 509.1000061 510.4208374 510.509078
01/10/11 509.5       509.5       508.1000061 508.5       510.1291707 510.3916554
01/10/11 508.4500122 508.9500122 507         507         509.8000031 510.2340174
01/10/11 507         508.2000122 503.2999878 503.2999878 509.0916697 509.964516
01/10/11 504         505         503.5       504.6499939 508.7375031 509.4091386
01/10/11 505.4500122 506.3500061 504         504.7000122 508.3000031 509.0125432
01/10/11 504.7000122 505.5       504.2000122 505.5       508.0208359 508.6531656
01/10/11 505.3500061 505.7000122 503.1000061 503.6499939 507.450002  508.3904018
01/10/11 504.5       504.5       499.5499878 500.5       506.6416677 507.9953678
01/10/11 500.4500122 502         500.25      501         505.8291677 507.3707539
01/10/11 501         501.2999878 499.5499878 500.3999939 504.9499995 506.8398577
01/10/11 500.4500122 500.7999878 498.6499939 498.6499939 503.9124985 506.3032024
01/10/11 498.7000122 499.25      498.0499878 498.3500061 503.0166651 505.665435
01/10/11 498.75      499.9500122 498.7000122 499         502.2249985 505.0558159
01/10/11 499.25      499.6499939 498.6499939 499.4500122 501.5958328 504.5511646
01/10/11 499.2999878 501.1499939 499.1000061 500.8999939 501.3958333 504.1260686
01/10/11 501.1000061 502.5       500.5499878 502.5       501.2166672 503.857229
01/10/11 502.3500061 502.9500122 501         501.5       500.9499995 503.7441266
01/10/11 501.5       501.5       500         500.5       500.5333328 503.557116
01/10/11 500         501.3500061 499.5       499.7999878 500.212499  503.3023564
01/10/11 499.9500122 500.3999939 499.2999878 500.2999878 500.1958313 503.0104923
01/10/11 500         501.3999939 499.5       499.6499939 500.0833308 502.784617
01/10/11 499.7999878 501.25      499.6499939 500.0499878 500.0541636 502.5233984

EDITED BY: drachenstern ~ I also merged the two questions. For @BestBoy ~ Notice the reformatting. This should help people understand things just a little more quickly. That makes people want to help you just a little more. 😉

Best Answer

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

Related Question