SQL Server - How to show dates contained in a range that don't appear in any row

Question

I have to do a report (SSRS) and I need to obtain dates contained in a range that don't appear in the table (Historical)

For example:

Params:

BeginingDate: 2016-10-10
EndDate:      2016-10-15

Table Historical:

DateTime - Name
2016-10-11 Yas
2016-10-13 Jesbel

I need a query to return:

    Date
2016-10-10
2016-10-12
2016-10-14
2016-10-15

All this query is for a report so I can't use external codes.

Thanks.

Create a numbers/dates table or use a CTE and select with a where-not-in, E.g. [SQL Server: How to select all days in a date range even if no data exists for some days](http://stackoverflow.com/questions/5899829/sql-server-how-to-select-all-days-in-a-date-range-even-if-no-data-exists-for-so) — Alex K., Nov 29 '16 at 12:34
The link provided by Alex will work. Mark this as duplicate? — Joe C, Nov 29 '16 at 12:43

score 2 · Accepted Answer · edited May 23 '17 at 12:01

I am so newbie with SSRS, so I don't tottaly sure if the next approach is working with u or not.

The next query is working well on SSMS:

The Query:-

    CREATE TABLE #Temp
    ([DateTime] date)


INSERT INTO #Temp
    ([DateTime])
VALUES
    ('2016-10-11'),
    ('2016-10-13')



DECLARE @MinDate DATE = '20161010',
        @MaxDate DATE = '20161015';

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

        Except
        select [DateTime] from #temp

        drop table  #Temp

The Result:-

The reference for getting all dates between two dates is the next, then I customized what u need above.:

Get all dates between two dates in SQL Server

score 0 · Answer 2 · answered Nov 29 '16 at 12:28

You need to generate dates between the start and end ranges. Then you need to find missed dates.

CREATE TABLE Historical
        ([DateTime] datetime, [Name] varchar(6))
    ;

    INSERT INTO Historical
        ([DateTime], [Name])
    VALUES
        ('2016-10-11 00:00:00', 'Yas'),
        ('2016-10-13 00:00:00', 'Jesbel')
    ;

By using below query you can find missed dates

 ;WITH CTE AS
        (
        SELECT CONVERT(DATE,'2016-10-10') AS DATE1
        UNION ALL
        SELECT DATEADD(DD,1,DATE1) FROM CTE WHERE DATE1<'2016-10-15'
        )
        SELECT DATE1 MISSING_ONE FROM CTE
        except 
        select [DateTime] from Historical
        option(maxrecursion 0)

SQL Server - How to show dates contained in a range that don't appear in any row

2 Answers2