SQL Server Displaying Missing Dates

sql server displaying missing dates

I would recommend you use a table valued function to get you all days in between 2 selected dates as a table (Try it out in this fiddle):

CREATE FUNCTION dbo.GetAllDaysInBetween(@FirstDay DATETIME, @LastDay DATETIME)
RETURNS @retDays TABLE 
(
    DayInBetween DATETIME
)
AS 
BEGIN
    DECLARE @currentDay DATETIME
    SELECT @currentDay = @FirstDay

    WHILE @currentDay <= @LastDay
    BEGIN

        INSERT @retDays (DayInBetween)
            SELECT @currentDay

        SELECT @currentDay = DATEADD(DAY, 1, @currentDay)
    END 

    RETURN
END

(I include a simple table setup for easy copypaste-tests)

CREATE TABLE SiteVisit (ID INT PRIMARY KEY IDENTITY(1,1), visitDate DATETIME, visitSite NVARCHAR(512))

INSERT INTO SiteVisit (visitDate, visitSite)
    SELECT '2014-03-11', 'site1'
    UNION
    SELECT '2014-03-12', 'site1'
    UNION
    SELECT '2014-03-15', 'site1'
    UNION
    SELECT '2014-03-18', 'site1'
    UNION
    SELECT '2014-03-18', 'site2'

now you can simply check what days no visit occured when you know the "boundary days" such as this:

SELECT
        DayInBetween AS missingDate,
        'site1' AS visitSite
    FROM dbo.GetAllDaysInBetween('2014-03-11', '2014-03-18') AS AllDaysInBetween
    WHERE NOT EXISTS 
        (SELECT ID FROM SiteVisit WHERE visitDate = AllDaysInBetween.DayInBetween AND visitSite = 'site1')

Or if you like to know all days where any site was not visited you could use this query:

SELECT
        DayInBetween AS missingDate,
        Sites.visitSite
    FROM dbo.GetAllDaysInBetween('2014-03-11', '2014-03-18') AS AllDaysInBetween
    CROSS JOIN (SELECT DISTINCT visitSite FROM SiteVisit) AS Sites
    WHERE NOT EXISTS
        (SELECT ID FROM SiteVisit WHERE visitDate = AllDaysInBetween.DayInBetween AND visitSite = Sites.visitSite)
    ORDER BY visitSite

Just on a side note: it seems you have some duplication in your table (not normalized) siteName should really go into a separate table and only be referenced from SiteVisit

How to fill in missing dates

Here is a query that would work. Start by cross joining all combinations of dates and users (add filters as needed), then left join the users table and calculate quota using the last_value() function (note that if you are using Snowflake, you must specify "rows between unbounded preceding and current row" as documented here):

with all_dates_users as (
--all combinations of dates and users
select date, user
from dates
cross join (select distinct user_email as user from users)
),
joined as (
--left join users table to the previous
select DU.date, DU.user, U.sent_at, U.user_email, U.score, U.quota
from all_dates_users DU
left join users U on U.sent_at = DU.date and U.user_email = DU.user
)
--calculate quota as previous quota using last_value() function
select date, user, nvl(score, 0) as score, last_value(quota) ignore nulls over (partition by user order by date desc rows between unbounded preceding and current row) as quota
from joined
order by date desc;

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

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

Find missing dates using SQL

This will get you all missing days for one year (SQL Fiddle).

all_dates generates a list of all dates of 2012 (adjust as required), and the LEFT JOIN checking for IS NULL eliminates those dates that exist in your source-table.

WITH all_dates AS (
  SELECT TO_DATE('01-jan-2012') + ROWNUM - 1 AS d
  FROM dual
  CONNECT BY ROWNUM <= ADD_MONTHS(TO_DATE('01-jan-2012'), 12 ) - TO_DATE('01-jan-2012')
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.d = all_dates.d )
WHERE t.d IS NULL
ORDER BY all_dates.d
;

Make sure to use a bind variable instead of hard-coding the date three times.

SQL Finding Missing Dates Between Ranges

Try this:

WITH
  a AS (
    SELECT
      SourceSystem, FromDate, ToDate,
      LEAD(FromDate) OVER(
        PARTITION BY SourceSystem
        ORDER BY RunDate
      ) AS NextDate
    FROM #temptable
  )
SELECT
  SourceSystem,
  DATEADD(DAY, 1, ToDate) AS GapBeg,
  DATEADD(DAY, -1, NextDate) AS GapFin
FROM a
WHERE
  NextDate IS NOT NULL AND
  DATEADD(DAY, -2, NextDate) >= ToDate;

Result:

+--------------+------------+------------+
| SourceSystem |   GapBeg   |   GapFin   |
+--------------+------------+------------+
| AAP          | 2021-06-03 | 2021-06-03 |
| ILG          | 2021-06-02 | 2021-06-04 |
+--------------+------------+------------+

db-fiddle

Display data for all date ranges including missing dates

Did you check, if datefrom and dateto is in correct range?

Minimum value of DateTime field is 1753-01-01 and maximum value is 9999-12-31.

Look at your source table to check initial values.

Fill Missing Dates In a Date-Sequenced in SQL using Tally Table

You could do this using a Tally Table.

Basically, you use the Tally Table to generate sequence of dates from @startDate to @endDate and CROSS JOIN it to DISTINCT Item to generate all Date-Item combination. Then, the result will be LEFT-JOINed to tblSales to achieve the desired output.

SQL Fiddle

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt

---

Here is an alternative. Instead of the cascading CTEs, use sys.columns to generate the Tally Table.:

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt

---

Result

|    Item |         dt |  Sales |
|---------|------------|--------|
| tenant1 | 2014-01-01 |    100 |
| tenant1 | 2014-01-02 |    100 |
| tenant1 | 2014-01-03 |    100 |
| tenant1 | 2014-01-04 |   NULL |
| tenant1 | 2014-01-05 |    100 |
| tenant2 | 2014-01-01 |    100 |
| tenant2 | 2014-01-02 |   NULL |
| tenant2 | 2014-01-03 |   NULL |
| tenant2 | 2014-01-04 |    100 |
| tenant2 | 2014-01-05 |   NULL |
| tenant3 | 2014-01-01 |    100 |
| tenant3 | 2014-01-02 |   NULL |
| tenant3 | 2014-01-03 |    100 |
| tenant3 | 2014-01-04 |   NULL |
| tenant3 | 2014-01-05 |    100 |

---

Related Topics