Group by Week - SQL Server Forums

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Group by Week

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2013-08-30 : 04:04:43

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 05:16:41

select (((day(DATE)-1) / 7) + 1) AS WeekNum, Count(*) from rooms where date = Current Month group by (((day(DATE)-1) / 7) + 1)

--
Chandu

SQLNoob81
Starting Member

38 Posts

Posted - 2013-08-30 : 05:40:09

Thanks Chandu!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 05:41:40

quote:
Originally posted by SQLNoob81

Thanks Chandu!

Welcome

--
Chandu

SQLNoob81
Starting Member

38 Posts

Posted - 2013-08-30 : 06:13:51

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-30 : 09:00:33

Instead of count(*) use sum(count(*)) over(order by (((day(DATE)-1) / 7) + 1))

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-03 : 05:46:48

[code]
;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[/code]

--
Chandu

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 12:46:24

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 12:56:51

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources