Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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  
 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.04 seconds. Powered By: Snitz Forums 2000