Welcome to Qi-U Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
283 views
in Technique[技术] by (71.8m points)

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

Please log in or register to answer this question.

1 Answer

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Qi-U Community for programmer and developer-Open, Learning and Share
...