Date Rounding for a View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Rounding for a View

Author

Topic

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 14:02:41

I am trying to incorporate a few columns into a view that each shows a certain value based on a logged datetime specific to the value. Unfortunately, the logtimes are accurate down to milliseconds, and each value has it's own logtime. (They're suppsoed to log at midnight, but sometimes log a few seconds early or late).

I want to be able to round up to 00:00:00 if it's 23:59:59 and down to the same time if it's 00:00:01. I can't very well just drop the time component because if a device logged at 00:00:01 on Aug 4 for the Aug 3rd average, and 23:59:59 for the Aug. 4rd data, then I'd have two Aug 4th values and zero Aug. 3rd values.

Additionally, I need to keep this in a datetime format for reporting purposed in Crystal Reports.

Am I asking too much?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-01 : 14:13:28

Not at all! here at sqlteam, the recommend way is to simply round your datatime to the day at midnight, just the same way you'd round decimal numbers to a whole number at .000.

The easiest formula is this one:

dateadd(dd,0, datediff(dd,0, YourDate ))

so it would be something like this:

select [Date], sum(value) as Total
from
(
select dateadd(dd,0, datediff(dd,0, YourDate )) as [Date], Value
from yourtable
) x
group by [date]

update: forgot the GROUP BY!

- Jeff
http://weblogs.sqlteam.com/JeffS

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 14:15:48

thanks Jeff!

I've also found that using

convert(datetime, (CONVERT(int, t.historydatetime))-1)

works to round to the correct date. Any warnings for this?

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 14:20:07

that might be weird out of context, so the select statement for the view looks something like this:

-----------------------

SELECT

distinct historydatetime = convert(datetime, (CONVERT(int, t.historydatetime))-1),

StoreID = T.StoreID,

Col1 = (select Top 1 S.historyvalue from dbo.Table S with (nolock) where CONVERT(int, s.historydatetime) =CONVERT(int, t.historydatetime) and S.storeid = T.storeid and S.historyID = 11300),

Col2 = (select Top 1 S.historyvalue from dbo.Table S with (nolock) where CONVERT(int, s.historydatetime) =CONVERT(int, t.historydatetime) and S.storeid = T.storeid and S.historyID = 11301)

from dbo.Table T with (NOLOCK)where t.storeid=198

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-01 : 14:39:59

IN general, math is better than string parsing -- and CONVERT does string parsing .... So, I recommend the date math formula that I gave earlier.

As for your SQL statement -- it is hard to figure out what you are trying to do, I am sure you can write it much simpler and much more efficient. If you give some sample data and expected results we can help you. You really should just be doing a simple GROUP BY, as I demonstrated, perhaps with a CASE. Also, never use NOLOCK unless it is absolutely necessary (it rarely is).

Something like this should work for you:


select StoreID, [Date], sum(col1), sum(col2)
from
(
select StoreID, dateadd(dd,0, datediff(dd,0, YourDate )) as [Date], 
   case when historyID = 11300 then historyvalue else 0 end as col1,
   case when historyID = 11301 then historyvalue else 0 end as col2
from Table
) x
group by StoreID, [Date]
order by StoreID, [Date]

(note: I forgot the key part -- GROUP BY -- in my original example for you)

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 14:49:47

"dateadd(dd,0, datediff(dd,0, YourDate ))"

Couple of pedantic points Jeff:

Should really be DATEADD(datepart, number, date) - rather than DATEADD(datepart, date, number). Won't matter when adding Days, but would matter if adding, for example, Months.

Also, the OP wants to "approximate"/ROUND to midnight, rather than "just drop the time component".

I'm not sure of a good way to do that, maybe add 5 minutes (say) to the date, and THEN "drop the time component"


-- Drop the time component:
SELECT	DATEADD(Day, DATEDIFF(Day, 0, '20070930 23:59:59'), 0),
	DATEADD(Day, DATEDIFF(Day, 0, '20071001 00:00:00'), 0),
	DATEADD(Day, DATEDIFF(Day, 0, '20071001 00:00:01'), 0)

-- Add 5 minutes
SELECT	DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, '20070930 23:59:59')), 0),
	DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, '20071001 00:00:00')), 0),
	DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, '20071001 00:00:01')), 0)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-01 : 14:54:16

Great points, Kristen -- I missed them both!

For the rounding, that's right, all you need to do is define how you'd like to round, and then add (or substract) the necessary value to make it work. i.e., to round after 10PM to the next day, just add 2 hours, and so on.....

I guess I missed that because I had never heard of an application that rounds values to the next day, usually it is just rounding all different times to the same day.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:05:49

"I had never heard of an application that rounds values to the next day"

me neither, but I've stored this away for future reference because I think a "logs at midnight" application is bound to phone-home any time between 11:56 and 00:01.

And that's assuming that it doesn't call home "on the hour" and we have to then deal with the day that Daylight Saving Time flips

Kristen

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 15:07:51

Our tables are populated by a SQL logger running on another server. For each device there are several points being logged, so each device has its own table and each point is specified by its own historyID. This data logs vertically (as you can see) which the view will then organize horizontally..

Current Table


StoreID HistoryID   HistoryDateTime             Value
198	11300	    2006-10-02 23:59:57.327	24.0
198	11300	    2006-10-04 00:00:03.527	24.0
198	11300	    2006-10-04 23:59:59.743	23.869281768798828
198	11300	    2006-10-06 00:00:08.077	24.0
198	11300	    2006-10-07 00:00:04.620	24.0
198	11300	    2006-10-07 23:59:59.720	23.837421417236328
198	11301	    2006-10-02 23:59:57.357	0.0
198	11301	    2006-10-04 00:00:03.570	0.0
198	11301	    2006-10-04 23:59:59.773	1.0
198	11301	    2006-10-06 00:00:08.240	0.0
198	11301	    2006-10-07 00:00:04.653	0.0
198	11301	    2006-10-07 23:59:59.760	1.0

View


StoreId   HistoryDateTime   11300     11301
198       2006-10-02        24.0      0.0
198       2006-10-03        24.0      0.0
198       2006-10-04        23.86...  1.0

I copy/pasted that code you just supplied, and it ran amazingly quick, yet was not able to separate the days correctly:


198	2006-10-02 00:00:00.000	24.0	0.0
198	2006-10-04 00:00:00.000	47.869281768798828	1.0
198	2006-10-06 00:00:00.000	24.0	0.0
198	2006-10-07 00:00:00.000	47.837421417236328	1.0

I really appreciate your help on this! thanks!

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:09:56

"I copy/pasted that code you just supplied"

Which one? Jeff's original was "chop off the time", my stuff a bit later was "add 5 minutes" ...

Kristen

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 15:12:47

sorry, Jeff's code; I was typing out that reply during you and Jeff's convo. I'm putting yours in now and checking it out

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-01 : 15:18:12

Show us your exact SQL code, we cannot help troubleshoot if we cannot see what you are actually using!

- Jeff
http://weblogs.sqlteam.com/JeffS

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 15:30:47

Jeff,

Save the actualy column/table names, The SQL code I provided earlier is the exact code I've been using. I've incorporated Kristen's 5-min add code to both that code and yours and seem to see things looking just fine. Here are the two code options as they stand:

Code #1


SELECT

distinct historydatetime =   DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)-1, 
StoreID				=	T.StoreID,
DeliAHU_Cool1OnTime		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11300),
DeliAHU_Cool1Starts		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11301),
DeliAHU_Cool2OnTime		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11302),
DeliAHU_Cool2Starts		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11303),
DeliAHU_ExhFanStarts		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11305),
DeliAHU_Heat1Starts		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11309),
DeliAHU_Heat2Starts		=	(select Top 1 S.historyvalue from dbo.DeliAHUDaily S with (nolock) where DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, s.historydatetime)), 0)  =DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, t.historydatetime)), 0)  and S.storeid = T.storeid and S.historyID = 11311)

from dbo.DeliAHUDaily T with (NOLOCK)

Code #2


select StoreID, [Date], sum(DeliAHU_Cool1OnTime), sum(DeliAHU_Cool1Starts)
from
(
select StoreID, DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0) as [Date], 
   case when historyID = 11300 then historyvalue else 0 end as DeliAHU_Cool1OnTime,
   case when historyID = 11301 then historyvalue else 0 end as DeliAHU_Cool1Starts
from DeliAHUDaily
) x
group by StoreID, [Date]
order by StoreID, [Date]

Obviously the second doesn't have all the columns built in, but you get the idea. As far as nolock, I'm sure I can do away with that w/out error. I've never built a view before and code #1 was based off of what I was given as a template. I'm assuming code #2 can be made into a view as well?

Thanks for the patience, I'm trying to be as accomodating as I can! :)

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:36:45

You should avoid NOLOCK for all production code.

Ignore that rule only after major consideration of the circumstances.

And if the data is only being inserted at Midnight (I don;t know that for sure, but) then there is clsoe to zero chance that you need NOLOCK.

Code #2 could possible be done without the Nested select:


select StoreID, 
      DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0) AS [Date], 
      sum(case when historyID = 11300 then historyvalue else 0 end),
      sum(case when historyID = 11301 then historyvalue else 0 end)
group by StoreID, DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0)
order by StoreID, [Date]

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-01 : 15:41:48

quote:

Originally posted by Kristen
Code #2 could possible be done without the Nested select:


select StoreID, 
      DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0) AS [Date], 
      sum(case when historyID = 11300 then historyvalue else 0 end),
      sum(case when historyID = 11301 then historyvalue else 0 end)
group by StoreID, DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0)
order by StoreID, [Date]

Kristen

I strongly recommend that you use the nested select to keep your code shorter and simpler; otherwise you need to repeat your long DateAdd/DateDiff/DateAdd expression twice which can lead to frustrating errors to track down!

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:48:42

"you need to repeat your long DateAdd/DateDiff/DateAdd expression twice which can lead to frustrating errors to track down!"

Good point.

Another reason to hate Cursors - repeating the @Variable List on both the initial get, and the loop get, leading to maintenance errors

Kristen

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 15:49:50

I think I'm going to stick with the nested select simply because I hit an error without it (pertained to views, can't remember the exact error code though).

I also will be creating views for similar data that logs on an hourly basis, and I believe that this code is a good stepping stone in getting that view done as well, so thank you both!

~~one last question though - the query returns no column name for any of the data columns. Is this something I can simply define?~~
EDIT: figured it out - musta been a brainfart

final code:

select StoreID, [Date], 
sum(DeliAHU_Cool1OnTime), 
sum(DeliAHU_Cool1Starts), 
sum(DeliAHU_Cool2OnTime),
sum(DeliAHU_Cool2Starts),
sum(DeliAHU_ExhFanStarts),
sum(DeliAHU_Heat1Starts),
sum(DeliAHU_Heat2Starts)
from
(
select StoreID, DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0) as [Date], 
   case when historyID = 11300 then historyvalue else 0 end as DeliAHU_Cool1OnTime,
   case when historyID = 11301 then historyvalue else 0 end as DeliAHU_Cool1Starts,
   case when historyID = 11302 then historyvalue else 0 end as DeliAHU_Cool2OnTime,
   case when historyID = 11303 then historyvalue else 0 end as DeliAHU_Cool2Starts,
   case when historyID = 11305 then historyvalue else 0 end as DeliAHU_ExhFanStarts,
   case when historyID = 11309 then historyvalue else 0 end as DeliAHU_Heat1Starts,
   case when historyID = 11311 then historyvalue else 0 end as DeliAHU_Heat2Starts
from DeliAHUDaily
) x
group by StoreID, [Date]
order by StoreID, [Date]

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:51:51

[code]
select StoreID, [Date],
sum(DeliAHU_Cool1OnTime) AS [TOTAL_DeliAHU_Cool1OnTime],
sum(DeliAHU_Cool1Starts) AS [TOTAL_DeliAHU_Cool1Starts]
from
(
select StoreID, DATEADD(Day, DATEDIFF(Day, 0, DATEADD(Minute, 5, historydatetime)), 0) as [Date],
case when historyID = 11300 then historyvalue else 0 end as DeliAHU_Cool1OnTime,
case when historyID = 11301 then historyvalue else 0 end as DeliAHU_Cool1Starts
from DeliAHUDaily
) x
group by StoreID, [Date]
order by StoreID, [Date]
[/code]
Kristen

rhurle02
Starting Member

8 Posts

Posted - 2007-10-01 : 15:52:23

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources