473,549 Members | 2,871 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Group by Time interval.

I have a table(work_orde r) 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
Jul 20 '05 #1
4 26186
Better 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,'2000010 1',rtim)/30.0)*30
,'20000101'),10 8)
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,'2000010 1',
CONVERT(DATETIM E,rtim,108)
)/30.0)*30
,'20000101'),10 8)
FROM Work_Order) AS W(mi)
GROUP BY mi

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
"Jaidev Paruchuri" <ja****@critica lresourcetech.c om> wrote in message
news:f8******** *************** **@posting.goog le.com...
I have a table(work_orde r) 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
Jul 20 '05 #3
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!
Jul 20 '05 #4


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!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5079
by: Rob | last post by:
Hi, I have a question, when you use a random number generator Random() in a loop to generate say 50000 random numbers; is it any difference if you use different time interval between each loop instead of a fixed amount of time, for example using an exponential time interval instead of a fixed time interval in a loop which generates 50000...
4
2955
by: Andrew Poulos | last post by:
How do I convert a length of time, measured in seconds, into a "point in time" type time interval or what's represented as: time (second,10,2) The format is: PS]] where: y: The number of years (integer, >= 0, not restricted) m: The number of months (integer, >=0, not restricted) d: The number of days (integer, >=0, not restricted)
2
1871
by: VB User | last post by:
I need to which time interval a given time is within. What is the function I can use?
1
3510
by: greg chu | last post by:
Hi, not sure who has done this. I want to set up a time interval that could be pass midnight. so people can enter 8AM to 8AM (pass midnight to 2nd day) 8AM to 2 AM (pass midnight to 2nd day) 8AM to 2 PM The have code to check if the time is in interval.
0
1045
by: achio84 | last post by:
Hi all, I'm a newbie in .Net programming. I'm developing a web application that has a listbox containing more than 1000++ items. This listbox is dynamic as the content will change on a time interval (e.g every 5 seconds). I tried to compare the list items and make a change if there is a need using for loop and if condition. I even tried using...
6
5554
by: newsteve1 | last post by:
hi, this should be simple but its stumping me, I am trying to make a slideshow that pulls random images at a random time interval (between 1 and 4 seconds). The images part works fine, and I can get the time function to create a random number, but that number doesnt change throughout the duration of the slideshow. I would like the duration...
1
3121
by: mndprasad | last post by:
Hi all I need a help here..am doing an application in jsp..i got struck in a position where i have not done that before There are multiple users in my application..for each user the time allotement is 9am to 4pm...after 4 it should automatically logout and the admin can increase the time interval say upto 5..so that he can work til 5pm......
0
1348
by: mariasoosai | last post by:
I have to send the jobs status that are started running from the previous day 9.00 a.m to today 9 a.m from the table sysjobhistory in sql server msdb database.But in that table , date and time are seperately present. also they are of datatype 'int'. pls anyone tell the query extract the jobs that are running between the specified time interval
1
2250
by: madankarmukta | last post by:
HI, I created the process which is hook to launch just before the Desktop appears and after the login credentials are entered by the machine owner.In Xp it is giving the expected result i.e. my process is running properly .. can say surely because the UI Interface of the executable is appearing before desktop appears. But in Windows server...
4
2269
by: yuvang | last post by:
i have a mdb located in server and n number of users are accessing and adding data to that mdb (source table). now i want to save the mdb tables in particular time interval frequently.
0
7518
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7715
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7469
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7808
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6040
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3498
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3480
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1057
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
757
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.