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())

Answer 1

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