January 18, 2011 at 3:47 am
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
January 18, 2011 at 5:54 am
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
January 18, 2011 at 8:14 am
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.
January 18, 2011 at 9:20 am
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
January 18, 2011 at 10:18 am
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
January 18, 2011 at 1:44 pm
Very neat!
Is there a way of grouping by other intervals e.g. a week or a month?
January 18, 2011 at 4:05 pm
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).
January 18, 2011 at 8:59 pm
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
Change is inevitable... Change for the better is not.
January 19, 2011 at 12:22 am
[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
February 3, 2011 at 12:44 am
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.
February 3, 2011 at 12:11 pm
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.
February 6, 2011 at 6:42 am
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?
February 6, 2011 at 7:49 am
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... 😉
February 13, 2011 at 7:05 am
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply