Group by Time interval.

I have a table(work_order) with time as varchar(5).

The values in table looks like this

work_order_id rtim

1 08:15
2 08:45
3 10:13
4 14:56

and so on...

I want to count how many work orders for every half an hour.

The result should look like this

Hours Count
8 10
8:30 15
9 34
9:30 03

and so on...

really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.

Any Help is Appreciated.

Thankyou.
Jaidev ParuchuriBetter to store your times as a DATETIME column:

CREATE TABLE Work_Order (work_order_id INTEGER PRIMARY KEY, rtim DATETIME
NOT NULL)

INSERT INTO Work_Order VALUES (1, '2003-11-11T08:15:00')
INSERT INTO Work_Order VALUES (2, '2003-11-11T08:45:00')
INSERT INTO Work_Order VALUES (3, '2003-11-11T10:13:00')
INSERT INTO Work_Order VALUES (4, '2003-11-11T14:56:00')

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',rtim)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

If you have to keep the Rtim column as CHAR:

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',
CONVERT(DATETIME,rtim,108)
)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

--
David Portas
----
Please reply only to the newsgroup
--|||"Jaidev Paruchuri" <jaidev@.criticalresourcetech.com> wrote in message
news:f885ab3.0311110746.7084cc95@.posting.google.co m...
> I have a table(work_order) with time as varchar(5).
> The values in table looks like this
> work_order_id rtim
> 1 08:15
> 2 08:45
> 3 10:13
> 4 14:56
> and so on...
> I want to count how many work orders for every half an hour.
> The result should look like this
> Hours Count
> 8 10
> 8:30 15
> 9 34
> 9:30 03
> and so on...
> really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.
> Any Help is Appreciated.
> Thankyou.
> Jaidev Paruchuri

CREATE TABLE Work_Orders
(
work_order_id INT NOT NULL PRIMARY KEY,
rtim CHAR(5) NOT NULL
)

SELECT Hrs.h + Sep.s + Mins.begin_min AS start_time,
COUNT(rtim) AS order_count
FROM (SELECT '00' AS h UNION ALL SELECT '01' AS h UNION ALL
SELECT '02' AS h UNION ALL SELECT '03' AS h UNION ALL
SELECT '04' AS h UNION ALL SELECT '05' AS h UNION ALL
SELECT '06' AS h UNION ALL SELECT '07' AS h UNION ALL
SELECT '08' AS h UNION ALL SELECT '09' AS h UNION ALL
SELECT '10' AS h UNION ALL SELECT '11' AS h UNION ALL
SELECT '12' AS h UNION ALL SELECT '13' AS h UNION ALL
SELECT '14' AS h UNION ALL SELECT '15' AS h UNION ALL
SELECT '16' AS h UNION ALL SELECT '17' AS h UNION ALL
SELECT '18' AS h UNION ALL SELECT '19' AS h UNION ALL
SELECT '20' AS h UNION ALL SELECT '21' AS h UNION ALL
SELECT '22' AS h UNION ALL SELECT '23' AS h) AS Hrs
CROSS JOIN
(SELECT ':' AS s) AS Sep
CROSS JOIN
(SELECT '00' AS begin_min, '29' AS end_min
UNION ALL
SELECT '30' AS begin_min, '59' AS end_min) AS Mins
LEFT OUTER JOIN
Work_Orders AS WO
ON rtim BETWEEN Hrs.h + Sep.s + Mins.begin_min AND
Hrs.h + Sep.s + Mins.end_min
GROUP BY Hrs.h + Sep.s + Mins.begin_min

Regards,
jag|||John

This query is beyond Excellence !!

This is exactly what i need.

Thank you for valuable your time!

regards,
--Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
David,

Your Query worked fine.
I didnt look at it earlier.

Thankyou very much .
Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!