Return Records By 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)

Return Records By Hour

Author

Topic

sanj
Starting Member

22 Posts

Posted - 2014-04-05 : 15:49:09

Hi,

I am looking at returning records in an hourly count format (from 07 to 18) for today, I have viewed a few other posts (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180943) but have been unable to get these working,

my table is in the following format:


ID, Starttime

so for the following sample data:


ID	Starttime	EndTime
1	05/04/2014 12:32	06/04/2014 12:32
2	05/04/2014 12:32	06/04/2014 12:32
3	03/04/2014 14:32	04/04/2014 14:32
7	05/04/2014 12:32	06/04/2014 12:32
8	02/04/2014 11:32	03/04/2014 11:32
9	01/04/2014 12:32	02/04/2014 12:32
10	02/04/2014 07:32	03/04/2014 07:32
11	05/04/2014 08:32	06/04/2014 08:32
12	05/04/2014 12:32	06/04/2014 12:32
13	03/04/2014 14:32	04/04/2014 14:32
14	05/04/2014 16:32	06/04/2014 16:32
15	05/04/2014 12:32	06/04/2014 12:32
16	05/04/2014 12:32	06/04/2014 12:32
17	05/04/2014 12:32	06/04/2014 12:32

I would like to return the following, for a date I send to the stored proc, in this example today:


Hour    CountOfUsers
07	0
08	1
09	0
10	0
11	0
12	7
13	0
14	0
15	0
16	0
17	0
18	0
19	0

Any help is appreciated.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-07 : 17:02:11

are you looking for something like this?


DECLARE @Input TABLE
(ID INT,
Starttime DATETIME,
EndTime DATETIME
)
INSERT INTO @Input VALUES(1,'05/04/2014 12:32','06/04/2014 12:32')
,(2,'05/04/2014 12:32','06/04/2014 12:32')
,(3,'03/04/2014 14:32','04/04/2014 14:32')
,(7,'05/04/2014 12:32','06/04/2014 12:32')
,(8,'02/04/2014 11:32','03/04/2014 11:32')
,(9,'01/04/2014 12:32','02/04/2014 12:32')
,(10,'02/04/2014 07:32','03/04/2014 07:32')
,(11,'05/04/2014 08:32','06/04/2014 08:32')
,(12,'05/04/2014 12:32','06/04/2014 12:32')
,(13,'03/04/2014 14:32','04/04/2014 14:32')
,(14,'05/04/2014 16:32','06/04/2014 16:32')
,(15,'05/04/2014 12:32','06/04/2014 12:32')
,(16,'05/04/2014 12:32','06/04/2014 12:32')
,(17,'05/04/2014 12:32','06/04/2014 12:32')

;WITH CTE AS
(select LEFT(CONVERT(VARCHAR(10), Starttime, 114) ,2) AS [Hours]
from @Input
)
, CTE_1 AS
(SELECT 7 AS [Hours]
UNION ALL
SELECT [Hours]+1 FROM CTE_1 WHERE [Hours]+1 <= 18
)
SELECT b.[Hours], COUNT(a.[Hours]) AS Cnt
FROM CTE a
RIGHT OUTER JOIN CTE_1 b ON a.[Hours] = b.[Hours]
GROUP BY b.[Hours]

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-04-08 : 08:38:12

[code]DECLARE @Sample TABLE
(
ID INT,
Starttime DATETIME,
EndTime DATETIME
);

INSERT @Sample
VALUES ( 1, '20140405 12:32', '20140406 12:32'),
( 2, '20140405 12:32', '20140406 12:32'),
( 3, '20140403 14:32', '20140404 14:32'),
( 7, '20140405 12:32', '20140406 12:32'),
( 8, '20140402 11:32', '20140403 11:32'),
( 9, '20140401 12:32', '20140402 12:32'),
(10, '20140402 07:32', '20140403 07:32'),
(11, '20140405 08:32', '20140406 08:32'),
(12, '20140405 12:32', '20140406 12:32'),
(13, '20140403 14:32', '20140404 14:32'),
(14, '20140405 16:32', '20140406 16:32'),
(15, '20140405 12:32', '20140406 12:32'),
(16, '20140405 12:32', '20140406 12:32'),
(17, '20140405 12:32', '20140406 12:32');

DECLARE @theDay DATETIME = '20140405';

WITH cteSource(theTime, TimePart)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, TimePart, @theDay), TimePart) AS theTime,
TimePart
FROM (
VALUES ('07:00'),
('08:00'),
('09:00'),
('10:00'),
('11:00'),
('12:00'),
('13:00'),
('14:00'),
('15:00'),
('16:00'),
('17:00'),
('18:00'),
('19:00')
) AS d(TimePart)
)
SELECT c.TimePart,
COUNT(s.ID)
FROM cteSource AS c
LEFT JOIN @Sample AS s ON CONVERT(CHAR(13), s.Starttime, 120) = CONVERT(CHAR(13), c.theTime, 120)
GROUP BY c.TimePart
ORDER BY c.TimePart;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources