Retrieve time based totals - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieve time based totals

Author

Topic

iainiow
Starting Member

2 Posts

Posted - 2010-04-22 : 03:52:19

Hi all,

Ive got my head in a spin on this one and wonder if anyone might be able to help!!

I have a table of 250,000 rows, the date field of this table stores date/time values. I want to prepare some totals based on time cut offs.

For example, our "day" is from 16:00:01 to 16:00:00. anything after 16:00:00 is classed as being in the next day.

I want to extract totals from my database based on these "days" rather than the standard 00:00:00 - 23:59:59. is this possible, i really cant think of a way of doing this!!

any help appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-22 : 04:06:34

subtract 16 hours from the date field

KH
[spoiler]Time is always against us[/spoiler]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-22 : 04:20:47

Try this:

set dateformat ymd
Declare @TestData table
(OrdDate datetime,
OrdQty Int)

Insert into @TestData
Select '2010-01-01 13:30' as OrdDate, 20 as OrdQty union
Select '2010-01-01 16:30' as OrdDate, 25 as OrdQty union
Select '2010-01-02 13:30' as OrdDate, 30 as OrdQty union
Select '2010-01-03 18:30' as OrdDate, 45 as OrdQty union
Select '2010-01-05 13:30' as OrdDate, 20 as OrdQty

Select OrdDate,sum(ordqty) from
(
Select case When datepart(hh,OrdDate) >15 then DATEADD(dd, DATEDIFF(dd,0,OrdDate), 1)
Else DATEADD(dd, DATEDIFF(dd,0,OrdDate), 0) End As OrdDate, OrdQty
from @TestData) As SubTab
group by OrdDate

Regards,
Bohra.

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 04:54:20

quote:
Originally posted by iainiow

Hi all,

Ive got my head in a spin on this one and wonder if anyone might be able to help!!

I have a table of 250,000 rows, the date field of this table stores date/time values. I want to prepare some totals based on time cut offs.

For example, our "day" is from 16:00:01 to 16:00:00. anything after 16:00:00 is classed as being in the next day.

I want to extract totals from my database based on these "days" rather than the standard 00:00:00 - 23:59:59. is this possible, i really cant think of a way of doing this!!

any help appreciated!

see this for similar logic

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources