"Jaidev Paruchuri" <ja****@criticalresourcetech.com> wrote in message
news:f8*************************@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