CODEWITHSUNDEEP
c#sql-serverlinq
Anup
Anup

Reputation: 9738

SQL Query finding Dates which are not Present in a Table

I have one table which have Employee Ids & Shift dates.

I am building a Report where I want to generate Unassigned Shifts means the Dates which are not present in the Table.

Example :- User Selects Date Range - 1 Jan - 31 Jan - All Employees. So Dates which are not Present in the Table will be generated in the Report.

I can do this in Linq easily by retrieving all the records then iterating each employee & finding dates which are not present & getting it in Datatable, And this is what i have tried till now.

Can i do it by firing a Single SQL Query?

Upvotes: 0

Views: 994

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You need a calendar table to do this.

I have used a Recursive CTE to generate the dates. But I will suggest to create a calendar table in your database and use it in queries like this 

DECLARE @startdate DATE = '2018-01-01',
        @enddate   DATE = '2018-01-31';

WITH calendar
     AS (SELECT @startdate AS Dates
         UNION ALL
         SELECT Dateadd(dd, 1, Dates)
         FROM   calendar
         WHERE  Dates < @enddate)
SELECT *
FROM   calendar c
       CROSS JOIN (SELECT DISTINCT EmployeeId
                   FROM   Employees) E --replace with your employee table name
WHERE  NOT EXISTS (SELECT 1
                   FROM   yourtable t
                   WHERE  c.Dates = t.dates
                          AND E.EmployeeId = t.EmployeeId)
OPTION (maxrecursion 0)

Upvotes: 1

Related Questions