tsql - Creating an empty row in SQL if col values aren't in both tables between date

Question

I don't really even know how to ask this question.

I'm trying to select data from different tables between selected dates. If there is no data for that date then some dummy data should be used in the row.

DECLARE @FromD AS VARCHAR(20) = '2020-12-20'
DECLARE @ToD AS VARCHAR(20) = '2020-12-30'
SELECT 
    Employees.Name,
    ('') AS CalcDate,
    ('') AS [Day],
    ('') AS Status,
    ('') AS AbsentCode,
    ('') AS Shifts,
    ('') AS Calc0,
    ('') AS TotalHours,
    ('') AS WorkedShift,
    @FromD AS FromDate,
    @ToD AS ToDate,
    @OrderBy AS OrderBy
FROM 
    Database.dbo.EmployeeData AS Employees,
    Database.dbo.CompanyCodes AS CompanyCodes,
    Database.dbo.ClockDate AS ClockDate
WHERE
    Employees.EmpNum NOT IN 
    (
        SELECT Database.dbo.EmployeeData.EmpNum
        FROM Database.dbo.EmployeeData
        INNER JOIN Database.dbo.ClockDate 
        ON Database.dbo.EmployeeData.EmpNum = Database.dbo.ClockDate.EmpNum
        WHERE Database.dbo.ClockDate.CalcDate BETWEEN @FromD AND @ToD
    )
    AND Employees.EmpNum = 'BH200'

Here I'm trying to check if there is a ClockDate for each day between FromDate and ToDate, if there is not then I want to fill the row with the empty values. But for some reason it creates a table with a couple hundred rows.

Answer 1

CompanyCodes is not related, used -- assuming @FromD, @toD and CalcDate are [date] type, not varchar, this might be what you need:

DECLARE @FromD date = '20201220' --YYYYMMDD international date format
DECLARE @ToD date = '20201230'
select *
    FROM dbo.EmployeeData AS e
    where not exists(select * 
                     from dbo.ClockDate d 
                     where d.EmpNum = e.EmpNum and d.CalcDate BETWEEN @FromD AND @ToD)