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 2008 Forums
 Transact-SQL (2008)
 Multiple count results in the same query...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

betty80
Starting Member

2 Posts

Posted - 09/25/2012 :  04:55:27  Show Profile  Reply with Quote
Hi

I have a table similar to this...

Year | Month | Day | OrderTime
2012 | Jan | 1 | 17:09
2012 | Jan | 1 | 17:11
2012 | Jan | 1 | 18:30
2012 | Jan | 1 | 18:36
2012 | Jan | 2 | 17:00
2012 | Jan | 2 | 17:22
2012 | Jan | 2 | 18:43
2012 | Jan | 3 | 17:09

Is it possible to get something like this...

Year | Month | Day | OrderTime |Count
2012 | Jan | 1 | 17:00-18:00 | 2
2012 | Jan | 1 | 18:00-19:00 | 2
2012 | Jan | 2 | 17:00-18:00 | 2
2012 | Jan | 2 | 18:00-19:00 | 1
2012 | Jan | 3 | 17:00-18:00 | 0
2012 | Jan | 3 | 18:00-19:00 | 1

Would this be achievable in one query or would I have to do it in a few..?

Thanks..!


sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  06:52:28  Show Profile  Reply with Quote
Sure it is possible - something like this (I have left the start and end time as two-separate columns, easy enough to combine into a single string column if you need to)
SELECT
	[Year],
	[Month],
	[Day],
	DATEPART (hour, OrderTime) AS OrderTimeStart,
	DATEPART (hour, DATEADD(hour,1,OrderTime) ) AS OrderTimeEnd,
	COUNT(*) AS [Count]
FROM
	YourTable
GROUP BY
	[Year],
	[Month],
	[Day],
	DATEPART (hour, OrderTime),
	DATEPART (hour, DATEADD(hour,1,OrderTime))
ORDER BY
	[Year],
	[Month],
	[Day],
	DATEPART (hour, OrderTime);
Go to Top of Page

betty80
Starting Member

2 Posts

Posted - 09/25/2012 :  07:06:19  Show Profile  Reply with Quote
Perfect, thanks so much for your help with that..!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  07:36:04  Show Profile  Reply with Quote
You are welcome :) [Just want to warn you that I didn't test the code other than to see if it will parse]
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.08 seconds. Powered By: Snitz Forums 2000