SQL: Daily report for with user counts

Question

I am trying to get the 30 days report of users, that will return date and total count of users as count created on that date and i did it with this query

 Select count(*)  As [Count] ,  
        (SELECT CONVERT(date, AddDate)) As [Date] 
 from Users 
 WHERE AddDate  >= (SELECT DateAdd(month, -1, Convert(date, GetDate()))) 
 Group By CONVERT(date, AddDate)

it give me only those dates on which any user is created, but i want to show all 30 days either if it has count 0.

Same Case i want to do with monthly report. i am getting months in which users are created , now i want to change it to get last 12 months from this month and their total users count. For this i am using this query

 Select count(*)  As [Count] ,
        (Select DATEPART( month , DateAdd( month , DATEPART(mm,AddDate) , -1 ) )) as Month
 from Users 
 WHERE AddDate  >= (SELECT DateAdd(YEAR, -1, Convert(date, GetDate())))
 Group By DATEPART(mm,AddDate)

You need a `Calendar table` and `Left Join` – Pரதீப் Oct 04 '16 at 09:52 — Pரதீப், Oct 04 '16 at 09:52

JohnHC · Answer 1 · 2016-10-04T10:22:50.787

5

Using a Calendar CTE:

With NumberSequence ( Number ) as
(
    Select 1 as Number
    union all
    Select Number + 1
    from NumberSequence
    where Number <= 30
)
, CalendarCTE as
(
    select cast(dateadd(dd, -30 + Number,getdate()) as Date) as CalDate
    from Numbersequence
)

select CalDate, count(U1.*) as CountUsers
from CalendarCTE
left join Users U1
on CalDate = convert(date, U1.AddDate)
group by CalDate

edited Oct 04 '16 at 10:22

answered Oct 04 '16 at 10:00

JohnHC

10,935
1
24
40

cast the column 'CalDate' to Date,other wise this will return the time part also. – Unnikrishnan R Oct 04 '16 at 10:21

score 2 · Answer 2 · edited May 23 '17 at 10:34

2

As I mentioned in comment, You need a Calendar table and Left Join

SELECT Count(u.adddate) AS [Count], 
       c.dates  AS [Date] 
FROM   calendar_table C 
       LEFT JOIN users U 
              ON c.dates = CONVERT(DATE, adddate) 
WHERE  c.dates >= Dateadd(month, -1, CONVERT(DATE, Getdate())) 
GROUP  BY c.dates

To generate/create a calendar table or dates check out the below questions

How to generate a range of dates in SQL Server

Generate Dates between date ranges

How to create a Calender table for 100 years in Sql

edited May 23 '17 at 10:34

Community

1
1

answered Oct 04 '16 at 09:57

Pரதீப்

91,748
19
131
172

Calendar table is overkill. Why not a 30 day calendar CTE? – JohnHC Oct 04 '16 at 09:57
@JohnHC - The idea is sort of calendar table not exactly calendar table – Pரதீப் Oct 04 '16 at 09:59

score 2 · Answer 3 · answered Oct 04 '16 at 10:02

2

Try this script :

WITH CTEDates
AS
(
  SELECT CAST(GetDate() as date) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTEDates
  WHERE DAY([date]) <= 30
)
Select count(*)  As [Count] ,CONVERT(date, AddDate) As [Date] 
from CTEDates 
LEFT JOIN Users ON CTEDates.date=CONVERT(date, AddDate)
WHERE AddDate  >= DateAdd(month, -1, GetDate())
Group By CONVERT(date, AddDate)

answered Oct 04 '16 at 10:02

Jaydip Jadhav

12,179
6
24
40

The CTE won't work. It's the increment that needs to be limited, not the day of the date. Otherwise, Oct 31st won't appear... – JohnHC Oct 04 '16 at 10:06
Well, good to know about this but this will give Oct 31s,I have tested it before posting the Ans :) – Jaydip Jadhav Oct 04 '16 at 10:08

score 2 · Answer 4 · answered Oct 04 '16 at 10:17

2

DECLARE @StartDate Datetime
DECLARE @EndDate Datetime

CREATE TABLE #tMyCalanderDate (dtDate Datetime Primary key)

SELECT @StartDate = '01-Sep-2016'
SELECT @EndDate  =  '30-Sep-2016'

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #tMyCalanderDate (dtDate)
    SELECT @StartDate 

    SELECT @StartDate = DATEADD(day,1,@StartDate)

END

SELECT count(A.UserID)  As [Count] , B.dtDate As [Date] 
FROM Users AS A
RIGHT JOIN #tMyCalanderDate AS B ON CONVERT(date, A.AddDate) = CONVERT(date, B.dtDate)  
WHERE CONVERT(date, A.AddDate) BETWEEN @StartDate AND @EndDate
Group By CONVERT(date, B.dtDate)

answered Oct 04 '16 at 10:17

Sohel Islam

21
2

Azure Synapse Analytics (data warehouse) does not support recursion in a CTE so variations on this method to create the temp table can be used instead of the other suggestions here. – sartoris May 24 '21 at 20:12
However, here's a link to a faster method: https://stackoverflow.com/a/7825036/1803823 – sartoris May 24 '21 at 21:34

score 1 · Answer 5 · answered Oct 04 '16 at 10:13

You can use a CTE to get a thirty day calendar. Then left join your Users table to it.

DECLARE @CurrentTime DATETIME = GETDATE()
;WITH CTE AS
    (
      SELECT CONVERT(DATE, @CurrentTime) AS [Date]

      UNION ALL

      SELECT  DATEADD(dd, -1, Date)
      FROM    CTE
      WHERE   DATEADD(dd, 29, Date) > @CurrentTime
    )
SELECT COUNT(U.AddDate) AS [Count]
, CTE.[Date] AS [Date]
FROM CTE
LEFT JOIN users U
ON CTE.Date = CONVERT(Date, AddDate)
GROUP BY CTE.Date

You can use a similar CTE to get the twelve month calendar and use the same joins to get the count.

HTH.

SQL: Daily report for with user counts

5 Answers5

Linked