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
Go to Top of Page

SQLNoob81
Starting Member

38 Posts

Posted - 2013-08-30 : 05:40:09
Thanks Chandu!
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 05:41:40
quote:
Originally posted by SQLNoob81

Thanks Chandu!


Welcome

--
Chandu
Go to Top of Page

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
Go to Top of Page

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))
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -