Transactions by the hour - 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 2005 Forums

Transact-SQL (2005)

Transactions by the hour

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 04:38:52

Hi

I have a number of transactions made through out the day and was hoping to count the number of transactions made each hour of the day.
Does anyone know how this could be done?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 04:56:46

DO you have a date field to indicate the datetime of transaction? Then i think you can take count of transaction grouped by DATEPART(h,datetimecolumn)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 05:10:04

Hi

There are two fields, one to indicate the date, and one for the time. Hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 05:16:35

Can you post the full table structure?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 05:28:20

OrderNo - Nvarchar
OrderDate - Datetime (No time values given eg 07/06/2007 00:00:00)
OrderTime - Nvarchar (Having a problem converting to Datetime as the format given is "12:30 AM")
Qty - Nvarchar

Hope this helps!
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 06:10:50

[code]DECLARE @Tran TABLE
(OrderNo int IDENTITY(1,1),
Date datetime,
[Time] nvarchar(10)
)
INSERT INTO @Tran (Date,[Time]) VALUES('2 Jan 2007','9:30 am')
INSERT INTO @Tran (Date,[Time]) VALUES('2 Jan 2007','11:38 am')
INSERT INTO @Tran (Date,[Time]) VALUES('2 Jan 2007','11:45 am')
INSERT INTO @Tran (Date,[Time]) VALUES('2 Jan 2007','12:30 pm')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','9:30 am')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','11:30 am')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','11:45 am')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','12:30 pm')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','1:30 pm')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','1:38 pm')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','2:15 pm')
INSERT INTO @Tran (Date,[Time]) VALUES('3 Jan 2007','3:30 am')

--sample data
SELECT * FROM @Tran

--output
SELECT Date,DATEPART(hh,CAST(Date AS datetime)+CAST([Time] AS datetime)),COUNT(OrderNo)
FROM @Tran
GROUP BY Date,DATEPART(hh,CAST(Date AS datetime)+CAST([Time] AS datetime))
ORDER BY Date,DATEPART(hh,CAST(Date AS datetime)+CAST([Time] AS datetime))

/* output
---------

.....

ID Date Time
----------- ----------------------- ----------
1 2007-01-02 00:00:00.000 9:30 am
2 2007-01-02 00:00:00.000 11:38 am
3 2007-01-02 00:00:00.000 11:45 am
4 2007-01-02 00:00:00.000 12:30 pm
5 2007-01-03 00:00:00.000 9:30 am
6 2007-01-03 00:00:00.000 11:30 am
7 2007-01-03 00:00:00.000 11:45 am
8 2007-01-03 00:00:00.000 12:30 pm
9 2007-01-03 00:00:00.000 1:30 pm
10 2007-01-03 00:00:00.000 1:38 pm
11 2007-01-03 00:00:00.000 2:15 pm
12 2007-01-03 00:00:00.000 3:30 am

(12 row(s) affected)

Date
----------------------- ----------- -----------
2007-01-02 00:00:00.000 9 1
2007-01-02 00:00:00.000 11 2
2007-01-02 00:00:00.000 12 1
2007-01-03 00:00:00.000 3 1
2007-01-03 00:00:00.000 9 1
2007-01-03 00:00:00.000 11 2
2007-01-03 00:00:00.000 12 1
2007-01-03 00:00:00.000 13 2
2007-01-03 00:00:00.000 14 1

(9 row(s) affected)

*/

[/code]

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-03 : 07:15:33

There are two fields, one to indicate the date, and one for the time. Hope this helps.
if you change the design you should...this is a waste of storage and processing power....it's far better/simpler to use the build in facilities of the Database engine. (Under the current scenario, you'll have to validate all time data to ensure it's strucuturally valid)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 07:55:23

Hi

I have managed to convert the time to the correct format so it is now being shown as '30/12/1899 07:36:00', how would I just show the time?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 08:50:32

I've shown in my sample code how to combine two fields and group on them.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 09:30:19

Is there a way of showing it between certain hours such as between 1pm-2pm and 9am-10am?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 09:40:10

Can you specify your desired o/p model?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 10:14:13

Ideally i would like it to look close to:

01/07/2007 1am-2am 8
01/07/2007 2am-3am 1
01/07/2007 3am-4am 0
...
...
02/07/2007 2pm-3pm 4

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-01-03 : 11:21:40

Finally got it, used the following code:

SELECT
CONVERT(varchar(max),OrderDate,1) AS 'Day'
,CASE WHEN DATEPART(hour,OrderTime) = 0 THEN 'Midnight-1'
WHEN DATEPART(hour,OrderTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,OrderTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,OrderTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,OrderTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,OrderTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,OrderTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,OrderTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,OrderTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,OrderTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,OrderTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,OrderTime) = 11 THEN '11am-Noon'
WHEN DATEPART(hour,OrderTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,OrderTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,OrderTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,OrderTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,OrderTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,OrderTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,OrderTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,OrderTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,OrderTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,OrderTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,OrderTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,OrderTime) = 23 THEN '11pm-Midnight'
END AS 'TimePeriod'

,COUNT(*) AS TranCount

FROM tblOrders

GROUP BY CONVERT(varchar(max),OrderDate,1),DATEPART(hh,OrderTime)
ORDER BY CONVERT(varchar(max),OrderDate,1) ASC, DATEPART(hh,OrderTime) ASC

Thanks for all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources