Group By Query

[Leighton21]

Hi all,

I have the following table (including data)

Date Product PrevProd Weight
12/06/2008 4:15:00 AM A A 100
12/06/2008 4:30:00 AM B A 100
12/06/2008 5:00:00 AM B B 100
12/06/2008 5:30:00 AM B B 100
12/06/2008 5:45:00 AM B B 100

I am trying to query the data so that it is grouped hourly. But as an added twist the data needs to account for change in the product. This sounds simple however that data needs to come out like the following

Date Product Weight
12/06/2008 4:30:00 AM A 200
12/06/2008 5:00:00 AM B 100
12/06/2008 6:00:00 AM B 200

So you can see that it needs to be grouped by previous product. It then needs to be grouped by the hour but if there is a change of product the Date needs to be the date time of when the product changed. Therefore anything from
05:00:00:001 to 06:00:00:000 is summed and put against the 06:00 time slot. Anything from 04:00:00:001 to the 04:30 (the change in product) is summed against the 04:30 time slot. Everthing after the 04:30 (product change is summed and placed against the 05:00 time slot.


Essentially i have grouped by the rounded hour to get and then by previous product

Date Product Weight
12/06/2008 5:00:00 AM A 200
12/06/2008 5:00:00 AM B 400
12/06/2008 6:00:00 AM B 200


But I cant figure out how to select the 4:30 as the time instead of the 5:00:00 AM


Cheers

 

[RyanEK]

Hi,

Just to clarify, should the last row in the desired result be 5:45AM? I don't understand why its 6AM.

Date Product Weight
12/06/2008 4:30:00 AM A 200
12/06/2008 5:00:00 AM B 100
12/06/2008 *5:45:00 AM B 200

Ryan

 

[Leighton21]

Cheers Ryan,

Thats the twist of this query first it needed to be grouped by hour so after originally

Date Product PrevProd Weight
12/06/2008 4:15:00 AM A A 100
12/06/2008 4:30:00 AM B A 100
12/06/2008 5:00:00 AM B B 100
12/06/2008 5:30:00 AM B B 100
12/06/2008 5:45:00 AM B B 100

became

Date Product PrevProd Weight
12/06/2008 5:00:00 AM B B 300
12/06/2008 6:00:00 AM B B 200

i.e. rather than grouping by Hour(Date) each time is summed and grouped upwards so anything in the 6th hour i.e. 05:00:001 to 06:00:01 goes to 6am.

Then the requirement changed as as well as being grouped by hour if there is a change of product the query must keep grouping by hour but also by the product change. When the product changes the Date column must present the time of change.

So to answer your question 5:45:00 AM belongs to the 6th hour and since there has been no product changed it is summarised by grouping into the 6th hour which is 6:00 am

cheers

 

[RyanEK]

Hi,

Crikey that's a tricky one... but I gave it a go. I had to do it in 2 parts.

First the test data

create table #tmp (d datetime, p1 char(1), p2 char(1), weight int)
insert into #tmp (d,p1,p2,weight) 
values ('12/06/2008 4:15:00 AM','A','A',100)
insert into #tmp (d,p1,p2,weight) 
values ('12/06/2008 4:30:00 AM','B','A',100)
insert into #tmp (d,p1,p2,weight) 
values ('12/06/2008 5:00:00 AM','B','B',100)
insert into #tmp (d,p1,p2,weight) 
values ('12/06/2008 5:30:00 AM','B','B',100)
insert into #tmp (d,p1,p2,weight) 
values ('12/06/2008 5:45:00 AM','B','B',100)

First, create a temp table that has the hour that a record should be in (hour) and the actual hour it should be in (changed).

select *, 
dateadd(hh,1,cast(convert(varchar(13),dateadd(s,-1,d),120) + ':00' as datetime)) as hour,
case when (p1 <> p2) then d else dateadd(hh,1,cast(convert(varchar(13),dateadd(s,-1,d),120) + ':00' as datetime)) end as Changed
into #tmp2
from #tmp

Then another query to return the desired output.

select d2, p2, sum(weight)
from
(
select *, 
(select top 1 changed from #tmp2 t2 where t1.hour = t2.hour and t1.p2 = t2.p2 order by d desc) as d2
from #tmp2 t1
) as tbl
group by p2, d2

This problem is tricky without having to resort to cursors.
Let me know if it works on a bigger dataset.

Ryan

 

[Leighton21]

Cheers Ryan

Works Great