"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