Calculating Delta values for each day

  • I have a table with a datetime column and a value column. I'm trying to get the delta value between days. i.e. the MAX of one day minus the MAX of the previous day without resorting to a cursor. Any ideas?

    IF OBJECT_ID('TempDB..#Data','U') IS NOT NULL DROP TABLE #Data

    CREATE TABLE #Data (ID Int,Timestamp DateTime, eTotal Real)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (1,'1 Jan 2011 01:00',50)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (2,'1 Jan 2011 02:00',55)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (3,'2 Jan 2011 01:00',60)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (4,'2 Jan 2011 02:00',65)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (5,'3 Jan 2011 01:00',70)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (6,'3 Jan 2011 02:00',76)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (7,'4 Jan 2011 01:00',80)

    INSERT INTO #Data (ID,Timestamp,eTotal) VALUES (8,'4 Jan 2011 02:00',88)

    SELECT * FROM #Data

    The output should be:

    01/01/2011 10

    02/01/2011 11

    03/01/2011 12

  • First one needs to get the date only from the datetime column using this method:

    1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.

    2. convert to an integer having the number of days since January 1, 1753. This conversion has implicit rounding, so that anything at noon or later is rounded to the next day (hence the subtracting of 1/2 day)

    3. convert the integer to a datetime data type.

    As SQL Server 2008 has a date datatype, the method is simply CAST(myTS as date).

    Second, get the maximum value within the date.

    Third, get the date of the "next" row (the minimum date that is greater than the date)

    Finally, get both the "current" row and the "next" row and calculate the difference.

    WITH DataDate (MyDate, eTotal ) as

    (select cast(cast(dateadd(ms,-43200002,Timestamp) as integer)as datetime)

    ,eTotal

    from #Data

    )

    ,DataDateMax ( MyDate, eTotalMax ) as

    (selectMyDate, MAX(eTotal)

    from DataDate

    group by MyDate

    )

    ,DateRange (MyDate, eTotalMax, NextPeriodDate) as

    (select Base.MyDate, Base.eTotalMax, MIN(NextPeriod.MyDate)

    fromDataDateMax as Base

    JOINDataDateMaxas NextPeriod

    on NextPeriod.MyDate > Base.MyDate

    group by Base.MyDate, Base.eTotalMax

    )

    select DateRange.MyDate

    ,DataDateMax.eTotalMax - DateRange.eTotalMax as eTotalDelta

    --DateRange.NextPeriodDate

    fromDateRange

    joinDataDateMax

    on DataDateMax.MyDate = DateRange.NextPeriodDate

    order by DateRange.MyDate

    SQL = Scarcely Qualifies as a Language

  • Thanks, I found this post (http://www.sqlservercentral.com/Forums/Topic869737-338-1.aspx) that used APPLY but I couldn't get it working with MAX unless I went via a temp table.

  • Carl Federl (1/18/2011)


    First one needs to get the date only from the datetime column using this method:

    1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.

    2. convert to an integer having the number of days since January 1, 1753. This conversion has implicit rounding, so that anything at noon or later is rounded to the next day (hence the subtracting of 1/2 day)

    3. convert the integer to a datetime data type.

    This is an overly complicated way of obtaining just the date portion of the date. The preferred way of finding the date portion is

    1. Finding the difference in days between the zero date and the data date.

    2. Add this difference to the zero date.

    SELECT DateAdd(Day, DateDiff(Day, 0, YourDateField), 0)

    FROM YourTable

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Something like this?

    Step 1: get the max value per day and number the result set.

    Step 2: perform a self join with a row offset of 1.

    ; WITH cte AS

    (

    SELECT

    ROW_NUMBER() OVER(ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, TIMESTAMP), 0) ) AS ROW,

    DATEADD(DAY, DATEDIFF(DAY, 0, TIMESTAMP), 0) AS DateVal,

    MAX(eTotal) AS max_eTotal

    FROM #DATA

    GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, TIMESTAMP), 0)

    )

    SELECT c1.DateVal, c2.max_eTotal-c1.max_eTotal

    FROM cte c1 INNER JOIN cte c2 ON c1.row=c2.row-1



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Very neat!

    Is there a way of grouping by other intervals e.g. a week or a month?

  • Nicky Murphy (1/18/2011)


    Very neat!

    Is there a way of grouping by other intervals e.g. a week or a month?

    Yes.

    Just change GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, TIMESTAMP), 0) to the range you want to group by (e.g. replace DAY with MONTH).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • drew.allen (1/18/2011)


    Carl Federl (1/18/2011)


    First one needs to get the date only from the datetime column using this method:

    1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.

    2. convert to an integer having the number of days since January 1, 1753. This conversion has implicit rounding, so that anything at noon or later is rounded to the next day (hence the subtracting of 1/2 day)

    3. convert the integer to a datetime data type.

    This is an overly complicated way of obtaining just the date portion of the date. The preferred way of finding the date portion is

    1. Finding the difference in days between the zero date and the data date.

    2. Add this difference to the zero date.

    SELECT DateAdd(Day, DateDiff(Day, 0, YourDateField), 0)

    FROM YourTable

    Drew

    Hi Drew,

    That is, indeed, the method I used to use because of it's speed. I've recently found something just a bit faster and every bit helps me because of the number of rows I usually end up dealing with at work.

    SELECT CAST(DateDiff(Day, 0, YourDateField) AS DATETIME)

    FROM YourTable

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • [font="Comic Sans MS"][/font]

    WITH S As (select CONVERT(varchar,Timestamp,101) Dtpart,* from #data)

    , S1 as (Select Row_number() over (order by MAX(etotal) ) Rid, MAX(etotal) MaxeTotal,Dtpart from S group by Dtpart)

    select

    S1.Dtpart,T.MaxeTotal - S1.MaxeTotal [Difference]

    from

    S1

    JOIN S1 T ON S1.Rid + 1 = T.Rid

  • LutzM (1/18/2011)


    Nicky Murphy (1/18/2011)


    Very neat!

    Is there a way of grouping by other intervals e.g. a week or a month?

    Yes.

    Just change GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, TIMESTAMP), 0) to the range you want to group by (e.g. replace DAY with MONTH).

    I replaced all the 'DAY's with 'WEEK's and it now gives me a week by week delta, the only issue is that the week seems to start on a Tuesday and I can't offset it back.

    So, on the live data I get 2011-01-04, 2011-01-11, 2011-01-18 etc.

  • That's weird. What is the result of the following statement? It should point to Monday of the current week.

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101')

    If both code snippet return Monday, then please post the complete query you're using together with table def and some sample data.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (2/3/2011)


    That's weird. What is the result of the following statement? It should point to Monday of the current week.

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101')

    If both code snippet return Monday, then please post the complete query you're using together with table def and some sample data.

    They return Monday the 7th, but it's currently Sunday the 6th. Were you expecting the date to be in the future?

  • Nicky Murphy (2/6/2011)


    LutzM (2/3/2011)


    That's weird. What is the result of the following statement? It should point to Monday of the current week.

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)

    SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101')

    If both code snippet return Monday, then please post the complete query you're using together with table def and some sample data.

    They return Monday the 7th, but it's currently Sunday the 6th. Were you expecting the date to be in the future?

    It will point to the current Monday on each Monday. To the following Monday on each Sunday and to the previous Monday for all other weekdays.

    I recommend you draw a more detailed picture of what you're really looking for instead of asking one question at a time. As a side note: The answer if you still get weekly totals displaying Tuesday still needs to be answered... 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • The Tuesday issue was my fault, I'd got a bracket in the wrong place so I was adding one to the day instead of one to the week!

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply