CODEWITHSUNDEEP
sql-server
curious1
curious1

Reputation: 14717

Generate counts of records for each day in a week

I use SQL Server 2014 for my project. I have the following code to produce the number of registrations of each day:

SELECT   
    DATEADD(DAY, DATEDIFF(DAY, 0, createTime), 0) AS createdOn, 
    COUNT(*) AS Count
FROM
    Registration
GROUP BY 
    DATEADD(DAY, DATEDIFF(DAY, 0, createTime), 0)
ORDER BY 
    createdOn

Now I would like to get the numbers for each day in a week (so there will be max 7 rows in output). How can I do it?

Here is the solution I have based on George's comment. Thank you, George!

SELECT   
    DATEPART(weekday, createTime) AS createdOn, 
    COUNT(*) AS Count
FROM
    Registration
GROUP BY 
    DATEPART(weekday, createTime)
ORDER BY 
    createdOn

Upvotes: 0

Views: 53

Answers (1)

Ross Bush
Ross Bush

Reputation: 15155

One way to return all days within a range returned with your data joined on matching days is to use a "calendar" table and LEFT JOIN your data by date.

DECLARE @StartDate DATETIME = '01/01/2015' 
DECLARE @EndDate DATETIME = '12/01/2016' 

//By Day In Year    
;WITH Calender as 
( 
    SELECT CalendarDate = @StartDate
    UNION ALL 
    SELECT CalendarDate = DATEADD(DAY, 1, CalendarDate)
    FROM Calender WHERE DATEADD (DAY, 1, CalendarDate) <= @EndDate 
)

SELECT 
    C.CalendarDate,
    COUNT(*) AS Count
FROM 
    Calender C
    LEFT JOIN Regsitration R ON R.createdOn = C.CalendarDate
GROUP BY
    C.CalendarDate
OPTION (MAXRECURSION 0) 

//By Week In Year

;WITH Calender as 
( 
    SELECT CalendarDate = @StartDate, WeekNumber=DATEPART(WEEK, @StartDate)
    UNION ALL 
    SELECT CalendarDate = DATEADD(WEEK, 1, CalendarDate), WeekNumber=DATEPART(WEEK, @StartDate)
    FROM Calender WHERE DATEADD (WEEK, 1, CalendarDate) <= @EndDate 
)

SELECT 
    C.WeekNumber,
    COUNT(*) AS Count
FROM 
    Calender C
    LEFT JOIN Regsitration R ON DATEPART(WEEK,R.createdOn) = C.WeekNumber
GROUP BY
    C.WeekNumber
OPTION (MAXRECURSION 0)

Upvotes: 1

Related Questions