Unusual Group by Day Requirement

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 2000 Forums

Transact-SQL (2000)

Unusual Group by Day Requirement

Author

Topic

st99015207
Starting Member

2 Posts

Posted - 2009-09-08 : 13:34:26

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-08 : 14:10:39

Shouldn't this be 12/31/2008 per your specs? '01/01/2009 02:00:00' , and what happened to '03/01/2009 01:12:00 30.00'?

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 14:35:06

I agree with jim's first observation. Here is one way which basically relies on a seperate table with from/to dates to JOIN on. There are various ways to accomplish that but this way uses a recursive cte.


set dateformat 'dmy'
declare @t table (dt datetime, v money)
insert @t
select '01/01/2009 15:00:00', 23.40 union all
select '01/01/2009 21:00:00', 40.50 union all
select '01/01/2009 02:00:00', 60.00 union all
select '02/01/2009 05:10:00', 30.45 union all
select '03/01/2009 01:12:00', 30.00 union all
select '05/01/2009 15:00:00', 24.60

declare @last datetime; select @last = max(dt) from @t

;with dts (grp, dt_fm, dt_to)
as
(
       select 1
              ,dateadd(day, datediff(day, 1, min(dt)), '1900-01-01 05:00:00.000')
              ,dateadd(day, datediff(day, 0, min(dt)), '1900-01-01 05:00:00.000')
       from   @t
       union all
       select dts.grp + 1
              ,dateadd(day, 1, dts.dt_fm)
              ,dateadd(day, 1, dts.dt_to)
       from   dts
       where  dateadd(day, 1, dts.dt_fm) < @last
)
select min(dt_fm), sum(v)
from   @t t
join   dts d
       on d.dt_fm < t.dt
       and d.dt_to >= t.dt
group by d.grp
having sum(v) > 0

OUTPUT:
----------------------- ---------------------
2008-12-31 05:00:00.000 60.00
2009-01-01 05:00:00.000 63.90
2009-01-02 05:00:00.000 60.45
2009-01-05 05:00:00.000 24.60

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-08 : 15:01:40

set dateformat 'dmy'
declare @MyTable table (MyDate datetime, Amt money)
insert @MyTable
select '01/01/2009 15:00:00', 23.40 union all
select '01/01/2009 21:00:00', 40.50 union all
select '01/01/2009 02:00:00', 60.00 union all
select '02/01/2009 05:10:00', 30.45 union all
select '03/01/2009 01:12:00', 30.00 union all
select '05/01/2009 15:00:00', 24.60

select
	BusinessDate =
		dateadd(dd,datediff(dd,0,dateadd(hh,-5,a.MyDate)),0),
	Amt = sum(Amt)
from
	@MyTable a
group by
	dateadd(dd,datediff(dd,0,dateadd(hh,-5,a.MyDate)),0)
order by
	dateadd(dd,datediff(dd,0,dateadd(hh,-5,a.MyDate)),0)

Results:

BusinessDate                                           Amt
------------------------------------------------------ --------
2008-12-31 00:00:00.000                                60.0000
2009-01-01 00:00:00.000                                63.9000
2009-01-02 00:00:00.000                                60.4500
2009-01-05 00:00:00.000                                24.6000

CODO ERGO SUM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-08 : 15:28:03

DECLARE @Table TABLE (Date datetime, Val numeric(5,2))

INSERT INTO @Table

select '01/01/2009 15:00:00', 23.40 union all
select '01/01/2009 21:00:00', 40.50 union all
select '01/01/2009 02:00:00', 60.00 union all
select '02/01/2009 05:10:00', 30.45 union all
select '03/01/2009 01:12:00', 30.00 union all
select '05/01/2009 15:00:00', 24.60

SELECT

CASE
WHEN DATEDIFF(n,'1900/01/01', convert(datetime,'1900/01/01 ' +convert(varchar(8),date,108))) <=300
THEN DATEADD(day,DATEDIFF(day,0,DATEADD(day,-1,date)) ,0)
ELSE DATEADD(day,DATEDIFF(day,0,DATE ),0)
END
, sum(val)
from
@table
group by

CASE
WHEN DATEDIFF(n,'1900/01/01', convert(datetime,'1900/01/01 ' +convert(varchar(8),date,108))) <=300
THEN DATEADD(day,DATEDIFF(day,0,DATEADD(day,-1,date)) ,0)
ELSE DATEADD(day,DATEDIFF(day,0,DATE ),0)
END

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-08 : 16:28:33

[code]declare @t table (dt datetime, v money)
insert @t
select '01/01/2009 15:00:00', 23.40 union all
select '01/01/2009 21:00:00', 40.50 union all
select '01/01/2009 02:00:00', 60.00 union all
select '02/01/2009 05:10:00', 30.45 union all
select '03/01/2009 01:12:00', 30.00 union all
select '05/01/2009 15:00:00', 24.60

SELECT DATEADD(DAY, DATEDIFF(HOUR, '05:00', dt) / 24, 0) AS theDay,
SUM(v) AS theSum
FROM @t
GROUP BY DATEADD(DAY, DATEDIFF(HOUR, '05:00', dt) / 24, 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources