SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Return Records By Hour
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanj
Starting Member

22 Posts

Posted - 04/05/2014 :  15:49:09  Show Profile  Reply with Quote
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

USA
93 Posts

Posted - 04/07/2014 :  17:02:11  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/08/2014 :  08:38:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.67 seconds. Powered By: Snitz Forums 2000