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