Getting Week-Wise count of records

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 487
DECLARE @Temp table (Room VARCHAR(15), BookDate DATE)

--Populating Sample Data
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)

--query to get the week-wise Count
;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;

--MSSQL 2012
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)

Comments or Responses

Login to post response