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

SQL Server Development (2000)

Group time

Author

Topic

hgrades
Starting Member

2 Posts

Posted - 2005-08-17 : 15:23:00

I was wondering if anyone knew of a way to group a result by 5 minute increments? What I need is a query to bring back a count of user sessions open every 5 minutes.

Hour     minute    users
10    5    434
10    10    564
10    15    233

thanks!

Here is what I have but it makes multiple columns:

select count(distinct A.order_id) as NumOrders, SUM(sale_price) as total, DATEPART(hh,order_date) as thehour, CONVERT(VARCHAR,A.order_date,12) as thedate,

(CASE WHEN DATEPART(minute,order_date) BETWEEN 0 AND 5 THEN 5 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 6 AND 10 THEN 10 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 11 AND 15 THEN 15 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 16 AND 20 THEN 20 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 21 AND 25 THEN 25 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 26 AND 30 THEN 30 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 31 AND 35 THEN 35 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 36 AND 40 THEN 40 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 41 AND 45 THEN 45 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 46 AND 50 THEN 50 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 51 AND 55 THEN 55 END) as halfhour,
(CASE WHEN DATEPART(minute,order_date) BETWEEN 56 AND 60 THEN 60 END) as halfhour

from uc_orders A
join uc_order_items B on A.order_id = B.order_id
join uc_store_items C on B.store_items_id = C.store_items_id
join uc_products D on C.product_id = D.product_id
where order_date > '2005-08-05 00:00:00.000' AND order_date < '2005-08-06 00:00:00.000'
group by CONVERT(VARCHAR,A.order_date,12),DATEPART(hh,order_date),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 0 AND 5 THEN 5 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 6 AND 10 THEN 10 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 11 AND 15 THEN 15 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 16 AND 20 THEN 20 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 21 AND 25 THEN 25 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 26 AND 30 THEN 30 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 31 AND 35 THEN 35 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 36 AND 40 THEN 40 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 41 AND 45 THEN 45 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 46 AND 50 THEN 50 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 51 AND 55 THEN 55 END),
(CASE WHEN DATEPART(minute,order_date) BETWEEN 56 AND 60 THEN 60 END)
order by thehour, halfhour asc

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-17 : 16:13:31

Here is a way to do it if the datetimes are just for today. You can change the getdate() to any other day you need:

SELECT FLOOR(DATEDIFF(mi, CONVERT(datetime, CONVERT(char(8), getdate(), 112)), Orderdate)/5), SUM(sale_price)
FROM YourTable
GROUP BY FLOOR(DATEDIFF(mi, CONVERT(datetime, CONVERT(char(8), getdate(), 112)), OrderDate)/5)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-18 : 01:18:27

This should do it.


select
	count(distinct A.order_id) as NumOrders,
	SUM(sale_price) as total,
	-- Round the time down to the next lower 5 minutes
	dateadd(minute,(datediff(minute,0,order_date)/5)*5,0)
	as Five_Minute_Time
from
	uc_orders A 
	join 
	uc_order_items B
	on A.order_id = B.order_id 
	join
	uc_store_items C
	on B.store_items_id = C.store_items_id
	join uc_products D
	on C.product_id = D.product_id
where
	order_date >= '2005-08-05 00:00:00.000' AND
	order_date < '2005-08-06 00:00:00.000'
group by
	dateadd(minute,(datediff(minute,0,order_date)/5)*5,0)
order by
	dateadd(minute,(datediff(minute,0,order_date)/5)*5,0)

CODO ERGO SUM

hgrades
Starting Member

2 Posts

Posted - 2005-08-18 : 14:03:27

That will do it! Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources