Strange group by requirement!

Question

Post reply

Strange group by requirement!

nick-604971

SSC Enthusiast

Points: 188
More actions
September 8, 2009 at 11:37 am

#97991

Hi all,
We have an application that uses a standard 24 hours period for a reporting 'day', but the day doesnt run from midnight to midnight, it actually runs from 5AM to 5AM.
Its easy to return aggregate totals grouped by the day using datepart for a standard mignight to midnight day, but how would I acheive the same grouping using my strange 5AM-> 5AM period??
The table in question has a column for the date and a seperate column for the time.
Example data from table:
Date Time Value
---------------------------------------
01/01/2009 15:00:00 23.40
01/01/2009 21:00:00 40.50
01/01/2009 02:00:00 60.00
02/01/2009 05:10:00 30.45
03/01/2009 01:12:00 30.00
05/01/2009 15:00:00 24.60
Desired Output:
Date Total
----------------------
01/01/2009 123.90
02/01/2009 60.45
05/01/2009 24.60
I hope this makes sense?!
I already have the where clause for returning rows that fall between two dates/times....Therefore its simply the grouping logic I am struggling with...
Many thanks in adavance.
Nick

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

This shows how to convert the actual datetime to a business date.

select

a.MyDate,

BusinessDate = dateadd(dd,datediff(dd,0,dateadd(hh,-5,a.MyDate)),0)

from

( --Test Data

select MyDate = getdate() union all

select MyDate = '20090908 04:59:59.997' union all

select MyDate = '20090908 05:00:00.000' union all

select MyDate = '20090909 04:59:59.997' union all

select MyDate = '20090909 05:00:00.000'

) a

order by

a.MyDate

Results:

MyDate BusinessDate

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

2009-09-08 04:59:59.997 2009-09-07 00:00:00.000

2009-09-08 05:00:00.000 2009-09-08 00:00:00.000

2009-09-08 13:58:16.967 2009-09-08 00:00:00.000

2009-09-09 04:59:59.997 2009-09-08 00:00:00.000

2009-09-09 05:00:00.000 2009-09-09 00:00:00.000

(5 row(s) affected)

GSquared SSC Guru Points: 260824 More actions · Answer 2

I was going to suggest subtracting five hours from the time to get the appropriate date, but I see that Michael beat me to it.

The thing to keep in mind on this kind of solution is, which day does 5 AM itself belong to? Is a 5 AM event considered the beginning of the new day, or the end of the prior?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

nick-604971 SSC Enthusiast Points: 188 More actions · Answer 3

GSquared (9/8/2009)
I was going to suggest subtracting five hours from the time to get the appropriate date, but I see that Michael beat me to it.
The thing to keep in mind on this kind of solution is, which day does 5 AM itself belong to? Is a 5 AM event considered the beginning of the new day, or the end of the prior?

Thanks.

05:00:00 is the start of the new working day, in the same way that 00:00:00 is the start of a normal day.

I understand the concept of shifting the time by five hours, but this is made complicated by the date field....(?)

nick-604971 SSC Enthusiast Points: 188 More actions · Answer 4

Thanks for the example, but I think you have missed the point that my date isnt one standard datetime field, its actually made up

of two columns, date and time. They are both varchar columns that store just their actual component. eg.

Date

----

01/01/2009

Time

-----

05:00:00

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

nick (9/8/2009)
Thanks for the example, but I think you have missed the point that my date isnt one standard datetime field, its actually made up
of two columns, date and time. They are both varchar columns that store just their actual component. eg.
Date
----
01/01/2009
Time
-----
05:00:00

Just combine them together, convert to datetime, and so on...

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 6

nick (9/8/2009)
Thanks for the example, but I think you have missed the point that my date isnt one standard datetime field, its actually made up
of two columns, date and time.

Add a computed column to the table or create a passthrough view of the table with a computed column to recombine these two items. I know you probably don't have any control over the design, but it was a mistake to store these in separate columns especially since they've probably been stored as VARCHAR instead of the proper DATETIME datatypes.

Since you need to do this a lot, a two column daily date range table may be worthwhile building to take advantage of indexes and the like for aggregates.

--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)

one_too_many SSC Enthusiast Points: 179 More actions · Answer 7

We do this with one of our apps and use a the minutes from zero i.e. midnight. You can set up a function or a lookup table to off set the mins from zero to change your 05:00 to equal 0.

KRs

M