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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…