CODEWITHSUNDEEP
sqlexcelt-sqlsql-server-2008-r2excel-formula
Shantanu
Shantanu

Reputation: 867

SQL Server 2008 R2 - Calculation referencing Previous Row

I am looking to design a query, where a calculation compares a value in a cell with a value in the previous cell in the same column, and another calculation uses the previous calculations result to reference a previous cell for a calculation as well. The data will be in the following order from the sample image below - Material, Plant and YearWeek

Sample Data Image

From the image below, the blue column headers represent the raw data, and the yellow column headers represent the calculation rows.

The formulae for the calculation rows are as follows:

From my investigation, the LEAD and LAG functions can help accomplish this, but since I am restricted to SQL Server 2008 R2, I am unable to use them. Please find the query to create the table.

    CREATE TABLE ZDAYS ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4)

Final Data Output

 YearWeek   Material    Plant   Inventory   Demand  First?  Actual Available
 2015-42    ABC          101        20        5     First         15             
 2015-43    ABC          101        20        3     No            12             
 2015-44    ABC          101        20        2     No            10
 2015-42    ABC          201        30        10    First         20
 2015-43    ABC          201        30        8     No            12
 2015-44    ABC          201        30        4     No            8
 2015-42    XYZ          101        10        3     First         7
 2015-43    XYZ          101        10        2     No            5
 2015-44    XYZ          201        20        4     First         16

Upvotes: 2

Views: 274

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In earlier versions of SQL Server you can use apply:

select z.yearweek, z.material, z.plant, z.inventory, z.demand,
       (case when seqnum = 1 then 'Yes' else 'No' end) as isFirst,
       (inventory - cume.demand) as ActualAvailable
from (select z.*,
             row_number() over (partition by material, plant order by yearweek) as seqnum
      from zdays z
     ) z outer apply
     (select sum(z2.demand) as demand
      from zdays z2
      where z2.material = z.material and z2.plant = z.plant and
            z2.yearweek <= z.yearweek
     ) cume

Upvotes: 0

Shep
Shep

Reputation: 628

Adapted from answer 'No Joins and No Analytic Functions' at:

http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

declare  @ZDAYS TABLE ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4);

WITH T1 (RowNum, UniqueID, YearWeek, Material, Plant, Inventory, Demand)
AS
    (SELECT Row_Number() OVER(ORDER BY z.Material,z.Plant,z.YearWeek ) N
        ,1.0 + floor(10000 * RAND(convert(varbinary, newid()))) as UniqueID 
        ,z.YearWeek
        ,z.Material
        ,z.Plant
        ,z.Inventory
        ,z.Demand
        FROM @ZDAYS z
    )
select T1.*,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN UniqueID END) OVER (Partition BY (RowNum+1)/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN UniqueID END) OVER (Partition BY RowNum/2) END LeadValUniqueID,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN UniqueID END) OVER (Partition BY RowNum/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN UniqueID END) OVER (Partition BY (RowNum+1)/2) END LagValUniqueID
FROM T1
ORDER BY T1.Material, T1.Plant, T1.YearWeek
GO

or, the same logic with the Lead() and Lag() rows concatenated for identification instead of generating a unique id.

declare  @ZDAYS TABLE ([YearWeek] VARCHAR(7), 
    [Material] VARCHAR(3), [Plant] VARCHAR(3),
    [Inventory] INT, [Demand] INT)

    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '101', 20, 5)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '101', 20, 3)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '101', 20, 2)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'ABC', '201', 30, 10)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'ABC', '201', 30, 8)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'ABC', '201', 30, 4)
    INSERT INTO @ZDAYS VALUES ('2015-42', 'XYZ', '101', 10, 3)
    INSERT INTO @ZDAYS VALUES ('2015-43', 'XYZ', '101', 10, 2)
    INSERT INTO @ZDAYS VALUES ('2015-44', 'XYZ', '201', 20, 4);

-- LAG and LEAD result rows concatenated
WITH T1 (RowNum,  YearWeek, Material, Plant, Inventory, Demand)
AS
    (SELECT Row_Number() OVER(ORDER BY z.Material,z.Plant,z.YearWeek ) N
        ,z.YearWeek
        ,z.Material
        ,z.Plant
        ,z.Inventory
        ,z.Demand
        FROM @ZDAYS z
    )
select T1.*,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY (RowNum+1)/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY RowNum/2) END LeadRowValues,
CASE WHEN RowNum%2=1 THEN MAX(CASE WHEN RowNum%2=0 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY RowNum/2) ELSE MAX(CASE WHEN RowNum%2=1 THEN YearWeek + ' ' + Material + ' ' + Plant + ' ' + cast(Inventory as varchar(5)) + ' ' + cast(Demand as varchar(5)) END) OVER (Partition BY (RowNum+1)/2) END LagRowValues
FROM T1
ORDER BY T1.Material, T1.Plant, T1.YearWeek
GO

Upvotes: 1

Related Questions