Determine missing dates count and business days in T-SQL

Question

I'm a trying to figure out how to write a query that appends multiple tables and counts the missing dates. Some locations have 5 business days/week (tableX) and some 6 business days (tableY). I'd like to determine business days for the location, extract the current count and figure out how many days some of them are behind with filling in data. I've tried a few examples from SO using UNION ALL, DATEDIFF and MAX of the current that but that's not giving me the expected result. The missing rows are there just the illustrate the blanks. The simplest query, the better. Thank you.

tableX

+-----------+----------+---------+-------+--------------+--------------+
|   Date    | Location | DayName | Count | BusinessDays | Missing Days |
+-----------+----------+---------+-------+--------------+--------------+
| 4/1/2020  | X        | Wed     |     1 |              |              |
| 4/2/2020  | X        | Thu     |     1 |              |              |
| 4/3/2020  | X        | Fri     |     1 |              |              |
| 4/4/2020  | X        | Sat     |       |              |              |
| 4/5/2020  | X        | Sun     |       |              |              |
| missing   |          | (Mon)   |       |              |              |
| missing   |          | (Tue)   |       |              |              |
| 4/8/2020  | X        | Wed     |     1 |              |              |
| 4/9/2020  | X        | Thu     |     1 |              |              |
| 4/10/2020 | X        | Fri     |     1 |              |              |
| 4/11/2020 | X        | Sat     |       |              |              |
| 4/12/2020 | X        | Sun     |       |              |              |
| 4/13/2020 | X        | Mon     |     1 |              |              |
| 4/14/2020 | X        | Tue     |     1 |              |              |
| 4/15/2020 | X        | Wed     |     1 |              |              |
|           |          | Total   |     9 |           11 |            2 |
+-----------+----------+---------+-------+--------------+--------------+

tableY

+-----------+----------+---------+-------+--------------+--------------+
|   Date    | Location | DayName | Count | BusinessDays | Missing Days |
+-----------+----------+---------+-------+--------------+--------------+
| 4/1/2020  | Y        | Wed     |     1 |              |              |
| 4/2/2020  | Y        | Thu     |     1 |              |              |
| 4/3/2020  | Y        | Fri     |     1 |              |              |
| 4/4/2020  | Y        | Sat     |     1 |              |              |
| 4/5/2020  | Y        | Sun     |       |              |              |
| missing   |          | (Mon)   |       |              |              |
| missing   |          | (Tue)   |       |              |              |
| missing   |          | (Wed)   |       |              |              |
| 4/9/2020  | Y        | Thu     |     1 |              |              |
| 4/10/2020 | Y        | Fri     |     1 |              |              |
| 4/11/2020 | Y        | Sat     |     1 |              |              |
| 4/12/2020 | Y        | Sun     |       |              |              |
| 4/13/2020 | Y        | Mon     |     1 |              |              |
| 4/14/2020 | Y        | Tue     |     1 |              |              |
| 4/15/2020 | Y        | Wed     |     1 |              |              |
|           |          | Total   |    10 |           13 |            3 |
+-----------+----------+---------+-------+--------------+--------------+

Desired result

+-------+------+----------+--------------+
| Month | Year | Location | Missing Days |
+-------+------+----------+--------------+
|     4 | 2020 | X        |            2 |
|     4 | 2020 | Y        |            3 |
+-------+------+----------+--------------+

simple failed example

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
SET @EndDate = GETDATE()

SELECT [Location],
       [Date],
        (DATEDIFF(dd, @StartDate, @EndDate) + 1)
          -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
          -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
          -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS Businessdays
  FROM [Daily].[dbo].[tableX] WHERE YEAR(Date)=YEAR(getdate()) AND MONTH(Date)=MONTH(getdate())
  UNION ALL
SELECT [Location],
       [Date],
       (DATEDIFF(dd, @StartDate, @EndDate) + 1)
          -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
          -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) AS Businessdays
  FROM [Daily].[dbo].[tableY] WHERE YEAR(Date)=YEAR(getdate()) AND MONTH(Date)=MONTH(getdate())

Aaron Hughes · Answer 1 · 2020-04-17T14:25:12.940

This answer will depend on the use of a numbers table to generate a range of dates. This could be a statically generated numbers table or a CTE, however, due to recursion limits, the CTE has limits to the number of rows you can generate.

Check these links for info on how to build a numbers table:

Static Numbers Table

CTE Numbers Sequence

--SETUP FOR DEMO
DECLARE @tableX TABLE 
(
    Date DATETIME
    ,[Location] VARCHAR(1)
    ,DayOfWeekNumber INT
    ,DayofWeekName VARCHAR(10)
)

DECLARE @tableY TABLE 
(
    Date DATETIME
    ,[Location] VARCHAR(1)
    ,DayOfWeekNumber INT
    ,DayofWeekName VARCHAR(10)
)

INSERT INTO @tableX 
VALUES
(43920,'X',DATEPART(DW,43920),DATENAME(DW,43920)),
(43921,'X',DATEPART(DW,43921),DATENAME(DW,43921)),
(43922,'X',DATEPART(DW,43922),DATENAME(DW,43922)),
(43923,'X',DATEPART(DW,43923),DATENAME(DW,43923)),
(43924,'X',DATEPART(DW,43924),DATENAME(DW,43924)),
--(43925,'X',DATEPART(DW,43925),DATENAME(DW,43925)),
--(43926,'X',DATEPART(DW,43926),DATENAME(DW,43926)),
(43927,'X',DATEPART(DW,43927),DATENAME(DW,43927)),
(43928,'X',DATEPART(DW,43928),DATENAME(DW,43928)),
(43929,'X',DATEPART(DW,43929),DATENAME(DW,43929)),
(43930,'X',DATEPART(DW,43930),DATENAME(DW,43930)),
(43931,'X',DATEPART(DW,43931),DATENAME(DW,43931)),
(43932,'X',DATEPART(DW,43932),DATENAME(DW,43932)),
(43933,'X',DATEPART(DW,43933),DATENAME(DW,43933)),
(43934,'X',DATEPART(DW,43934),DATENAME(DW,43934))



INSERT INTO @tableY
VALUES
(43920,'Y',DATEPART(DW,43920),DATENAME(DW,43920)),
(43921,'Y',DATEPART(DW,43921),DATENAME(DW,43921)),
(43922,'Y',DATEPART(DW,43922),DATENAME(DW,43922)),
(43923,'Y',DATEPART(DW,43923),DATENAME(DW,43923)),
(43924,'Y',DATEPART(DW,43924),DATENAME(DW,43924)),
--(43925,'Y',DATEPART(DW,43925),DATENAME(DW,43925)),
--(43926,'Y',DATEPART(DW,43926),DATENAME(DW,43926)),
--(43927,'Y',DATEPART(DW,43927),DATENAME(DW,43927)),
(43928,'Y',DATEPART(DW,43928),DATENAME(DW,43928)),
(43929,'Y',DATEPART(DW,43929),DATENAME(DW,43929)),
(43930,'Y',DATEPART(DW,43930),DATENAME(DW,43930)),
(43931,'Y',DATEPART(DW,43931),DATENAME(DW,43931)),
(43932,'Y',DATEPART(DW,43932),DATENAME(DW,43932)),
(43933,'Y',DATEPART(DW,43933),DATENAME(DW,43933)),
(43934,'Y',DATEPART(DW,43934),DATENAME(DW,43934))

--END SETUP FOR DEMO

--DECLARE YOUR RANGE
DECLARE @rangeStart DATETIME
DECLARE @rangeEnd DATETIME

DECLARE @dataSet TABLE
(
    Date DATETIME
    ,[Location] VARCHAR(1)
    ,DayOfWeekNumber INT
    ,DayofWeekName VARCHAR(10)
)

--SIMPLE UNION ALL OF THE LOCATION TABLES IN QUESTION
--IF LOCATION CODE IS NOT ALREADY PART OF THE TABLE, YOU CAN DEFINE IT AS AN ADDITIONAL COLUMN IN YOUR STATEMENT
INSERT INTO @dataSet
select * from @tableX 
UNION ALL
SELECT * from @tableY

SELECT @rangeStart = MIN(DATE) FROM @dataSet
SELECT @rangeEnd = MAX(Date) FROM @dataSet




--DEFINE YOUR BUSINESS DAYS FOR EACH LOCATION
DECLARE @locationsBusinessDays TABLE
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    ,LocationCode NVARCHAR(10)
    ,BusinessDay INT
)

--INSERT A RECORD FOR EACH LOCATION AND BUSINESS DAY FOR SAID LOCATION.
--THIS COULD BE A STATIC FACT TABLE IS YOU PREFER.
INSERT INTO @locationsBusinessDays
(
    LocationCode
    ,BusinessDay
)
VALUES
('X', 2)
,('X', 3)
,('X', 4)
,('X', 5)
,('X', 6)
,('Y', 2)
,('Y', 3)
,('Y', 4)
,('Y', 5)
,('Y', 6)
,('Y', 7)

--USE THE NUMBERS TABLE TO GENERATE A LIST OF DATES, ONE FOR EACH LOCATION, WITHIN THE DEFINED RANGE
;with reportRange
as
(
    select 
        CAST(Num as DATETIME) as DateStamp
        ,loc.LocationCode
        ,DATEPART(DW, n.num) As DayOfWeekNumber
        ,DATENAME(DW, n.Num) AS DayOfWeekName
    FROM Numbers n  
        CROSS APPLY (SELECT DISTINCT LocationCode From @locationsBusinessDays) loc
    WHERE n.Num >= @rangeStart ANd n.Num <= @rangeEnd
),
/*BRING ALL THE DATA BACK, 
  JOINING AGAINST YOUR BUSINESS DAYS FACT TABLE
  AND YOUR MERGED DATASET
  ANYTHING WITH A MISSING DATE BECAUSE OF THE LEFT JOIN ON THE MERGED DATASET 
  AS WELL AS BEING CONSIDERED A VALID BUSINESS DAY GET MARKED AS MISSING
*/
calcMissing
AS
(
    SELECT r.*
        ,CASE WHEN b.Date IS NULL AND lbd.BusinessDay IS NOT NULL THEN 1 ELSE 0 END AS Missing
    FROM reportRange r
        left join @dataSet b on r.DateStamp = b.Date and r.LocationCode = b.Location
        left join @locationsBusinessDays lbd on lbd.LocationCode = r.LocationCode AND r.DayOfWeekNumber = lbd.BusinessDay
)

--GROUP AS NECESSARY AND SUM THE MISSING DAYS
SELECT 
    MONTH(DateStamp) AS [Month]
    ,YEAR(DateStamp) AS [Year]
    ,LocationCode 
    ,SUM(Missing) as MissingDays
FROM calcMissing
GROUP BY 
    MONTH(DateStamp)
    ,YEAR(DateStamp)
    ,LocationCode

Thank you for your time and effort. Your example exceeds my comprehension. Is there a way to simplify this? I am not looking to declare a rangestart and a rangeend, I'd like to pick it up directly from the date values in my tables (20+ similar tables). somethin like this: please see my edit — Cosmin, Apr 16 '20 at 22:13
@iCosmin in your edit it looks like you are setting your range for `@startDate` and `@endDate` as month to date, but your comment suggests you want to pull the range from the dates in your location tables? which are you after? I can help adjust the query for either, but this is probably as simple as I can think to make things. I don't think looking for missing dates in a range is necessarily trivial. Throw in the fact that you need to consider different definitions of business days and you've got yourself a fairly complex query. — Aaron Hughes, Apr 16 '20 at 22:46
I am after the second scenario, like I mentioned in the comment. I just don't know how to write it well in the example. What confuses me a bit is the first half, with the creations of the tables because I already have them. Thank you again for all your contribution. — Cosmin, Apr 17 '20 at 14:06
I've edited my answer to reflect how you want to gather the range data. Also, the first part of my answer is denoted with comments as setting up for demonstration purposes. You can effectively ignore that section and replace with your real tables.Just add all of your tables to the `@dataset` table variable. — Aaron Hughes, Apr 17 '20 at 14:27
I don't understand how am I supposed to use the Numbers Table to generate the list of dates. I think "reportRange" is missing because it is only referenced once. Can you explain the "with reportRange" and "from Numbers n" part? Thanks. — Cosmin, Apr 17 '20 at 15:56
Numbers can be converted freely to dates (ie `CAST(Num as DATETIME) as DateStamp`). Given that the numbers table represents a sequence, or range, of numbers, you can use the numbers table to define a range of dates to check against for missing dates in that range. the `with ReportRange` portion is a CTE that generates a record for each date within the determined range, for each location. `Numbers n` is just a table with a single integer column named 'num'. In that column is a sequence of numbers. For example my numbers table has a record for 1 - 50000. You can have more or less as needed. — Aaron Hughes, Apr 17 '20 at 16:17
I've tried it, managed to run it without error but the results are not as expected. The businessdays table doesn't make sense. Every location has a number of business days in a month (eg. April 13 days so far). DayName is there just for demonstration purposes, I don't have it or need it in my table. You answer is just too complex for what I need. Could you please devise a much simpler approach that excludes Sat, Sun in the query itself so I can mark it as the answer? — Cosmin, Apr 17 '20 at 16:45
Your question states that each location defined its own business days per week. The businessdays table reflects that scheme. Each record in the table represents a location and a day of the week that said location defines as a business day. SO (X, 2) would mean location X considers Monday a business day. See [DatePart](https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15) for more. The results of the provided example match what you defined as your desired output. I'm sorry, I don't know of a way to simplify given the need to define custom work weeks. — Aaron Hughes, Apr 20 '20 at 15:01

Determine missing dates count and business days in T-SQL

1 Answers1