# 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

• 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 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 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.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• [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 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.