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 2012 Forums
 Transact-SQL (2012)
 Group by Week
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 08/30/2013 :  04:04:43  Show Profile  Reply with Quote
Hi All

I have a table [Rooms] and would like to see the number of times they get booked per week.

select Count(*) from rooms where date = Current Month group by week

Output needs to be:

Week 1: 5
Week 2: 12
Week 3: 7
Week 4: 10

Any help is appreciated.

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/30/2013 :  05:16:41  Show Profile  Reply with Quote
select (((day(DATE)-1) / 7) + 1) AS WeekNum, Count(*) from rooms where date = Current Month group by (((day(DATE)-1) / 7) + 1)


--
Chandu
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 08/30/2013 :  05:40:09  Show Profile  Reply with Quote
Thanks Chandu!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/30/2013 :  05:41:40  Show Profile  Reply with Quote
quote:
Originally posted by SQLNoob81

Thanks Chandu!


Welcome

--
Chandu
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 08/30/2013 :  06:13:51  Show Profile  Reply with Quote
ok, I got it wrong. It needs to add the 1st week vale to second week, second to third etc. as below:


Original:
Week 1: 5
Week 2: 12
Week 3: 7
Week 4: 10

Required:

Week 1: 5
Week 2: 12 + 5
Week 3: 7 + 12 + 5
Week 4: 10 + 7 + 12 + 5
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 08/30/2013 :  09:00:33  Show Profile  Reply with Quote
Instead of count(*) use sum(count(*)) over(order by (((day(DATE)-1) / 7) + 1))
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/03/2013 :  05:46:48  Show Profile  Reply with Quote

;with cte as (
   select (((day(DATE)-1) / 7) + 1) AS WeekNum, Count(*) cnt 
   from rooms 
   where date = Current Month 
   group by (((day(DATE)-1) / 7) + 1)
)
SELECT c1.WeekNum, c1.cnt+COALESCE(c2.cnt, 0)
FROM cte c1 
LEFT JOIN cte c2 ON c1.WeekNum = c2.WeekNum+1


--
Chandu
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  12:46:24  Show Profile  Reply with Quote
Slight variation:
DECLARE @Temp table (Room VARCHAR(15), BookDate DATE)
INSERT @Temp VALUES

('A', GETDATE() - 7),
('B', GETDATE() - 5),
('C', GETDATE() - 3),
('D', GETDATE()),
('E', GETDATE() + 1),
('F', GETDATE() + 2),
('G', GETDATE() + 5),
('H', GETDATE() + 8),
('I', GETDATE() + 11),
('J', GETDATE() + 12),
('K', GETDATE() + 15),
('L', GETDATE() + 16)


;
WITH Cte AS
(
	SELECT (((DAY(BookDate) - 1) / 7) + 1) AS WeekNum, COUNT(*) BookCount 
	FROM @Temp
	GROUP BY (((DAY(BookDate) - 1) / 7) + 1)
)
SELECT
	A.WeekNum,
	SUM(B.BookCount)
FROM 
	Cte AS A
INNER JOIN
	Cte AS B
	ON A.WeekNum >= B.WeekNum
GROUP BY A.WeekNum
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  12:56:51  Show Profile  Reply with Quote
And here is a simplified version:
	SELECT 
		(((DAY(BookDate) - 1) / 7) + 1) AS WeekNum,
		SUM(COUNT(*)) OVER (ORDER BY (((DAY(BookDate) - 1) / 7) + 1)) AS RunningTotal
	FROM 
		@Temp
	GROUP BY 
		(((DAY(BookDate) - 1) / 7) + 1)
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