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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -