Grouping hours

[imosri]

Hi,

I need to build a query to display sales by hour range.

In short, the table has two significant columns for the calculation:

Sales_Total and date, date column is datetime type and always has full time/date information. ie 09/30/2004 02:06:50 a.m.

There are several other columns, but they will be used for filtering.

The result I need is:
Time Total
----------- --------
9:00 - 9:59 11111.00
10:00-10:59 22222.00
11:00-11:59 0.00 (I need to display 0 if no sales were made).
...
20:00-21:00 33333.00

Total 66666.00

Thank you all in advance.

Regards

 

[hmckillop]

you can group by hour by using something like this

select 
	sum(sales) as Sales_total, 
	datepart(hh, logontime) as Hour
from logonsession (nolock) 
group by 
	datepart(hh, logontime)
order by datepart(hh, logontime)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[kavin]

select sum(total) as TotalSale,convert(varchar(20),datepart(hh,logontime)) + ' - ' + convert(varchar(2),(datepart(hh,logontime)+1)) as Hour from #TestTable group by convert(varchar(20),datepart(hh,logontime)) + ' - ' + convert(varchar(2),(datepart(hh,logontime)+1))

 

[kavin]

select sum(total) as TotalSale,convert(varchar(20),datepart(hh,logontime)) + ' - ' + convert(varchar(2),(datepart(hh,logontime)+1)) as Hour from #TestTable group by convert(varchar(20),datepart(hh,logontime)) + ' - ' + convert(varchar(2),(datepart(hh,logontime)+1))


Regards
Pavan Kumar

 

[vongrunt]

OK, this was obvious part... what about this?

11:00-11:59 0.00 (I need to display 0 if no sales were made).

Personally I fill such gaps client-side...

 

[hmckillop]

I would do a right join on a table (in this case a temp table) containing values 0-23 for each hour and then use isnull to replace the NULL values with 0

declare @temp table (myid int)
insert into @temp values(6)
insert into @temp values(7)
insert into @temp values(8)
insert into @temp values(9)
insert into @temp values(10)
insert into @temp values(11)
insert into @temp values(12)
insert into @temp values(13)
insert into @temp values(14)
insert into @temp values(15)
insert into @temp values(16)

select isnull(sum(sales),0) as TotalSalesPerHour, myid
from aa_sales
right join @temp on myid = datepart(hh, saletime) 
group by myid

"I'm living so far beyond my income that we may almost be said to be living apart

 

[imosri]

All,

I'll take some time testing your suggestions.

I'll post my results shortly

Regards