CODEWITHSUNDEEP
sqlsql-servert-sqlouter-joingaps-and-islands
Muhammad Waheed
Muhammad Waheed

Reputation: 1088

How to get missing dates with 0 value in SQL Server?

I am using below query in SQL Server to find distinct number of logins made in last 7 days(excluding today's date): 

SELECT TOP (7) CONVERT(date, LoginTime) AS ActivityDate, COUNT(DISTINCT LoginID) AS UserCount
FROM Login
WHERE CONVERT(date, LoginTime) < CONVERT(date, GETDATE())
GROUP BY CONVERT(date, LoginTime)  
ORDER BY ActivityDate DESC;

It generates following output:

ActivityDate | UserCount
----------------------
2019-02-21   | 2
2019-02-20   | 3
2019-02-19   | 2
2019-02-15   | 2
2019-02-14   | 1
2019-02-13   | 2
2019-02-12   | 3

My expectation is to have all last 7 days in a sequence (not like as current output where date 2019-02-16, 2019-02-17 and 2019-02-18 are missing after 2019-02-19). I need that, if a date is missing it must be displayed with 0 count.

My expected output is as below:

ActivityDate | UserCount
----------------------
2019-02-21   | 2
2019-02-20   | 3
2019-02-19   | 2
2019-02-18   | 0
2019-02-17   | 0
2019-02-16   | 0
2019-02-15   | 2

Upvotes: 1

Views: 4157

Answers (4)

elizabk
elizabk

Reputation: 480

The best way to generate dates that do not have rows in your table in to join on a calendar table.

Here's an extremely simple calendar table of one year, based on this answer:

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO [Calendar]
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

(You can modify this query to add many more dates in the future, so that it won't need to be maintained for a while.)

Now you can join on the calendar table in your query like this:

select top (7) c.CalendarDate as ActivityDate,count(distinct LoginID) as UserCount
from Calendar c
left join Login l
    ON c.CalendarDate = CONVERT(date, l.LoginTime)
    and CONVERT(date,LoginTime )< convert(date,getdate())
group by c.CalendarDate 
order by c.CalendarDate desc;

It's worth the space it takes up, it will come in handy in many other cases as well.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

It is just 7 days so just type those dates:

SELECT ActivityDate, COUNT(DISTINCT LoginID) AS UserCount
FROM (VALUES
    (CAST(CURRENT_TIMESTAMP - 1 AS DATE)), -- build the list of dates
    (CAST(CURRENT_TIMESTAMP - 2 AS DATE)),
    (CAST(CURRENT_TIMESTAMP - 3 AS DATE)),
    (CAST(CURRENT_TIMESTAMP - 4 AS DATE)),
    (CAST(CURRENT_TIMESTAMP - 5 AS DATE)),
    (CAST(CURRENT_TIMESTAMP - 6 AS DATE)),
    (CAST(CURRENT_TIMESTAMP - 7 AS DATE))
) datelist(ActivityDate)
LEFT JOIN Login ON CAST(LoginTime AS DATE) = ActivityDate
GROUP BY ActivityDate
ORDER BY ActivityDate DESC

Upvotes: 3

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this. Here you need to get first minimum date and maximum date. After that you need to generate all dates between that two days. And last you need to join both table.

declare @MinDate date
declare @MaxDate date

select * into #temp from(
select top (7) CONVERT(date,LoginTime) as ActivityDate,count(distinct LoginID) as UserCount
        from Login
        where CONVERT(date,LoginTime )< convert(date,getdate())
        group by CONVERT(date,LoginTime )  
        order by ActivityDate desc; 
)a        

Set @MinDate = (select min (ActivityDate) from #temp)
Set @MaxDate = (select max (ActivityDate) from #temp)  

Select a.Date, isnull(b.UserCount,0) as UserCount from(
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;
)a left join #temp b on a.Date = b.ActivityDate

You can find the live demo Here. I have inserted your query output into a temp table but the logic is same.

Upvotes: 3

EzLo
EzLo

Reputation: 14189

To see a particular value, the value must come from a row. So to see dates that don't exist on your login table, you must generated them as rows somewhere.

You can use a simple recursive CTE to generate 1 row per day between a particular interval, then use a LEFT JOIN to join logins that match on that particular day. The ones that don't match will still be displayed, since we are using LEFT JOIN.

DECLARE @GeneratingDateFrom DATE = DATEADD(DAY, -7, GETDATE())
DECLARE @GeneratingDateTo DATE = GETDATE()

;WITH GeneratedDates AS
(
    SELECT
        GeneratedDate = @GeneratingDateFrom

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
    FROM
        GeneratedDates AS G
    WHERE
        DATEADD(DAY, 1, G.GeneratedDate) < @GeneratingDateTo
)
SELECT
    G.GeneratedDate,
    count(distinct L.LoginID) as UserCount
FROM 
    GeneratedDates AS G
    LEFT JOIN [Login] AS L ON G.GeneratedDate = CONVERT(date, L.LoginTime)
GROUP BY
    G.GeneratedDate
ORDER BY 
    G.GeneratedDate desc

Upvotes: 4

Related Questions