none
TSQL Get Previous values for each group by

    Question

  • Dear Friends,

    I have a problem with my TSQL statment. 

    I need to get previous value for each day and specific unit. Everything goes fine if I have always 2 unit in each day. But if same day has just one unit or less, I cannot have the previous value in the current record.

    The Output is this one:

    SK_DAY SK_UNIT VALUE VALUE_PREVIUS_DAY
    20131112 2 30.00 NULL
    20131112 3 34.00 NULL
    20131113 2 40.00 30.00
    20131113 3 45.00 34.00
    20131114 2 50.00 40.00

    I dont have the second record for 2013-11-14, because in this date I just have value for unit 2.

    The final output should include the record:

    20131114 3 0 45.00

    The Statment I have is:

    SELECT MAIN.SK_DAY, MAIN.SK_UNIT, SUM(MAIN.VALUE) AS VALUE

    ,(
     SELECT SUM(VND1.VALUE) AS VALUE
     FROM FCT_TEST VND1
     WHERE CONVERT(DATE,CONVERT(VARCHAR(10),VND1.SK_DAY))>=DATEADD(dd,-1,CONVERT(DATE,CONVERT(VARCHAR(10),MAIN.SK_DAY)))
    AND CONVERT(DATE,CONVERT(VARCHAR(10),VND1.SK_DAY))<CONVERT(DATE,CONVERT(VARCHAR(10),MAIN.SK_DAY))

    AND VND1.SK_UNIT=MAIN.SK_UNIT

    ) AS VALUE_PREVIUS_DAY

    FROM FCT_TEST MAIN
    GROUP BY SK_DAY, MAIN.SK_UNIT

    SQL CREATE SCRIPT:

    CREATE TABLE [dbo].[FCT_TEST](
    [SK_DAY] [int] NOT NULL,
    [SK_UNIT] [int] NOT NULL,
    [VALUE] [decimal](18, 2) NULL,
     CONSTRAINT [PK_FCT_TEST] PRIMARY KEY CLUSTERED 
    (
    [SK_DAY] ASC,
    [SK_UNIT] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Thank you!!!

    PPSQL

    Saturday, December 21, 2013 11:31 AM

Answers

  • Correct, I realised later that forgot the units. I assume that you have a table with the units:

    ; WITH dates (
       SELECT DISTINCT date FROM tbl
       WHERE  date BETWEEN @startdate AND @enddate
    )
    SELECT d.date, u.unit, now.qty, prev.qty
    FROM   dates d
    CROSS  JOIN  units u
    LEFT   JOIN tbl now ON now.date = d.date
                       AND now.unit = u.unit
    LEFT   JOIN tbl prev ON prev.date = dateadd(DAY, -1, d.date)
                        AND prev.unit = u.unit


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by PPSQL Sunday, December 22, 2013 5:38 PM
    Saturday, December 21, 2013 6:36 PM

All replies

  • You can fill in missing date values with NULL or zero data:

    http://www.sqlusa.com/bestpractices2005/sequence/

    See the "Get previous  12 quarters excluding current quarter - QUARTER SEQUENCE GENERATOR" example.  Similarly you can generate a date sequence.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, December 21, 2013 12:41 PM
  • Thank you Kalman,

    But my problem is not with date! Fortunately I have dates for all days.

    The problem is for dimension tables like UNITS that I dont have values for all days, and when I try to get the previous value for previous day and each unit I dont have records. 

    20131112 2  30.00 NULL
    20131112 3  34.00 NULL
    20131113 2  40.00 30.00
    20131113 3  45.00 34.00
    20131114 2  50.00 40.00

    20131114 3  0 45.00 [I NEED THIS RECORD AS IS]

    Could you help me??

    Thank you!!


    Saturday, December 21, 2013 12:51 PM
  • Can you fill in gaps with NULL, 0  or other value? Something which will not make difference in the desired end result:

    http://www.sqlusa.com/bestpractices2005/missingdates/

    Related thread: http://stackoverflow.com/questions/16819365/how-to-fill-the-gaps


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Saturday, December 21, 2013 1:06 PM
  • In that case to find the missing dates you may have to build a calender table and LEFT JOIN it with your data.

    Where ever NULL is returned replace with previous days data.

    Sample calender table script http://letuslookintosqlserver.blogspot.in/2013/08/calender-table-in-sql-server.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, December 21, 2013 1:10 PM
  • "In that case to find the missing dates you may have to build a calender table and LEFT JOIN it with your data."

    Again, my problem is not date.

    the problem is get previous record for each combination date + unit


    Saturday, December 21, 2013 1:17 PM
  • No, your problem is really the dates. What if you have:

    SK_DAY SK_UNIT VALUE VALUE_PREVIUS_DAY
    20131112 2 30.00 NULL
    20131112 3 34.00 NULL
    20131113 2 40.00 30.00
    20131113 3 45.00 34.00
    20131114 2 50.00 40.00
    20131116 2 60.00 ?

    That is, there is a gap for unit 2. What should you now show from Previous day? And should you display rows for the 15th at all?

    Maybe you have a dimension table with dates, and in that case you are all set. But you can't assume that the table have all dates, when there obviously are gaps.

    It's difficult to post a query when the table and the query are completely messed up. The table has int for SK_DAY column, while the query uses dateadd. And there is SUM which does not fit anything in your narrative.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 21, 2013 1:53 PM
  • Dear Erland,

    I have always the 15 day present.

    Could you assume that in order to help this issue?

    Thank you!!!!

    Saturday, December 21, 2013 2:02 PM
  • ; WITH dates (
       SELECT DISTINCT date FROM tbl
    )
    SELECT d.date, now.unit, now.qty, prev.qty
    FROM   dates d
    LEFT   JOIN tbl now ON now.date = d.date
    LEFT   JOIN tbl prev ON now.date = dateadd(DAY, -1, d.date)

    But you should have a dates table instead of that CTE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 21, 2013 4:18 PM
  • this doesnt work.

    we need to join date and unit to the previous record

    Saturday, December 21, 2013 5:43 PM
  • Correct, I realised later that forgot the units. I assume that you have a table with the units:

    ; WITH dates (
       SELECT DISTINCT date FROM tbl
       WHERE  date BETWEEN @startdate AND @enddate
    )
    SELECT d.date, u.unit, now.qty, prev.qty
    FROM   dates d
    CROSS  JOIN  units u
    LEFT   JOIN tbl now ON now.date = d.date
                       AND now.unit = u.unit
    LEFT   JOIN tbl prev ON prev.date = dateadd(DAY, -1, d.date)
                        AND prev.unit = u.unit


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by PPSQL Sunday, December 22, 2013 5:38 PM
    Saturday, December 21, 2013 6:36 PM
  • Works, but the performance is dramatic!

    Thank you!

    Sunday, December 22, 2013 5:37 PM
  • >> I need to get previous sk_value for each day and specific unit. Everything goes fine if I have always 2 unit in each day. But if same day has just one unit or less, I cannot have the previous sk_value in the current record [rows are not records; this is a fundamental concept].

    We use CAST() and not the 1970's Sybase CONVERT string function today. Unlike BASIC, COBOL, etc we have temporal data in SQL. Using integers for dates is wrong. Also, the only display format allowed in ANSI/ISO Standard SQL is ISO-8601, with dashes. 

    When you post code, try to keep it neat, readable and minimal. And provide INSERT INTO statements for sample data. 

    CREATE TABLE Fct_Tests
    (sk_date DATE NOT NULL, --- correct data type
     sk_unit INTEGER NOT NULL, 
     sk_value DECIMAL(18,2) NOT NULL
      DEFAULT 0.00, --- why do big? Researched it? 
     PRIMARY KEY (sk_date, sk_unit));

    INSERT INTO Fct_Tests
    VALUES
    ('2013-11-12', 2, 30.00, NULL), 
    ('2013-11-12', 3, 34.00, NULL), 
    ('2013-11-13', 2, 40.00, 30.00), 
    ('2013-11-13', 3, 45.00, 34.00), 
    ('2013-11-14', 2, 50.00, 40.00);

    Look up the LEAD() and LAG() functions 

    CREATE VIEW Fct_Tests_2
    AS
    SELECT sk_date, sk_unit, sk_value,
           LAG(sk_value) OVER (sk_date, sk_unit)
           AS previous_sk_value
      FROM Fct_Tests;

    This is the basic use of the LAG(). 

    >> I do not have the second record [sic] for 2013-11-14, because in this date I just have sk_value for unit 2. The final output should include the record [sic]:

    ('2013-11-14', 3, 0, 45.00) <<

    Do you know what a Calendar table is? Use it to fill the Fct_Tests table with (sk_date, sk_unit) that have a sk_value of zero. As you update the sk_values, the VIEW will become current and correct. This is why I put a DEFAULT in the DDL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 22, 2013 10:19 PM
  • Celko, if you cared to read the thread, you would see that LAG() does not work here, even if PPSQL is on SQL 2012. Of course, if you cared to read the thread you would also find that PPSQL seems to have sorted out his problem already, so there is little reason to waste your time at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 22, 2013 10:39 PM