How to Use a Date Table as SQL Server Calendar Table in order to List Missing Date
Here in sql article, I'd like to display how we can code t-sql scripts for listing missing dates in a sql table in a given date range with samples.
The developed solution for this sql problem is using a sql date table or a sql calendar table probably every sql developers or sql administrators had used at least once.
This sql article will not give you t-sql scripts for creating a date table but will use a dates table using t-sql codes described at How to Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code.
The DatesTable function whose source code is given at the related article is going to be used in your sql codes for displaying missing date values in a database table.
Let's see how can a sql developer can use the DatesTable tabular user defined function to create a date table on the fly.
SELECT [date] FROM dbo.DateTable('20090101','20091231')
Let's work on a sample case on AdventureWorks sample SQL database with SalesOrderHeader table.
Let's try to find the gaps between dates where no Order is set by listing the dates where not OrderDate record is inserted at.
SELECT dt.[date]
FROM dbo.DateTable('20040101','20041231') dt
LEFT JOIN AdventureWorks.Sales.SalesOrderHeader so
ON dt.[date] = so.OrderDate
WHERE so.OrderDate IS NULL
Now let's make the sample for each Sales Person, I mean we can now extand the query to list the gaps in dates for each sales person.
This is for single sales person.
-- Listing sales days of the related sales person
SELECT DISTINCT SalesPersonID, OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesPersonID = 279
AND OrderDate BETWEEN '20040101' and '20041231'
-- Listing gaps between sales days of the related sales person
SELECT sp.SalesPersonID, dt.[date]
FROM dbo.DateTable('20040101','20041231') dt
CROSS JOIN AdventureWorks.Sales.SalesPerson sp
LEFT JOIN AdventureWorks.Sales.SalesOrderHeader so
ON dt.[date] = so.OrderDate and sp.SalesPersonID = so.SalesPersonID
WHERE so.OrderDate IS NULL
AND sp.SalesPersonID = 279
This is for all of the sales person.
-- Listing sales days of a sales person
SELECT DISTINCT SalesPersonID, OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20040101' and '20041231'
ORDER BY SalesPersonID, OrderDate
-- Listing gaps between sales days of each sales person
SELECT sp.SalesPersonID, dt.[date]
FROM dbo.DateTable('20040101','20041231') dt
CROSS JOIN AdventureWorks.Sales.SalesPerson sp
LEFT JOIN AdventureWorks.Sales.SalesOrderHeader so
ON dt.[date] = so.OrderDate and sp.SalesPersonID = so.SalesPersonID
WHERE so.OrderDate IS NULL
ORDER BY sp.SalesPersonID, dt.[date]