You can use for example recursive CTE to generate desired dates. Consider the following example
CREATE TABLE Person (
PersonId int
);
CREATE TABLE Attendance (
PersonId int,
InTime datetime,
OutTime datetime
);
INSERT INTO Person (PersonId) VALUES
(1),
(2),
(3);
INSERT INTO Attendance (PersonId, InTime, OutTime) VALUES
(1, CONVERT(datetime, '2019-01-04 09:00:00.000', 120), CONVERT(datetime, '2019-01-04 12:00:00.000', 120)),
(1, CONVERT(datetime, '2019-01-04 13:00:00.000', 120), CONVERT(datetime, '2019-01-04 17:00:00.000', 120)),
(1, CONVERT(datetime, '2019-01-06 10:00:00.000', 120), CONVERT(datetime, '2019-01-06 17:00:00.000', 120)),
(3, CONVERT(datetime, '2019-01-06 10:00:00.000', 120), CONVERT(datetime, '2019-01-06 11:00:00.000', 120));
;WITH Dates (ReportingDate) AS (
SELECT CONVERT(date, '2019-01-01 00:00:00.000', 120) AS ReportingDate
UNION ALL
SELECT DATEADD(day, 1, d.ReportingDate)
FROM Dates d
WHERE d.ReportingDate < CONVERT(date, '2019-01-10 00:00:00.000', 120)
),
AttendanceHours AS (
SELECT a.PersonId,
CAST(a.InTime as Date) AS AttendanceDate,
MIN(a.InTime) AS MinIn,
MAX(a.OutTime) AS MaxOut
FROM Attendance a
GROUP BY a.PersonId,
CAST(InTime as Date)
)
SELECT p.PersonId,
d.ReportingDate ,
ah.MinIn,
ah.MaxOut
FROM Person p
CROSS APPLY Dates d
LEFT JOIN AttendanceHours ah ON ah.PersonId = p.PersonId AND ah.AttendanceDate = d.ReportingDate
ORDER BY p.PersonId,
d.ReportingDate
OPTION (MAXRECURSION 1000);
The result is
PersonId ReportingDate MinIn MaxOut
-------- ------------- ----- ------
1 2019-01-01 NULL NULL
1 2019-01-02 NULL NULL
1 2019-01-03 NULL NULL
1 2019-01-04 2019-01-04 09:00:00.000 2019-01-04 17:00:00.000
1 2019-01-05 NULL NULL
1 2019-01-06 2019-01-06 10:00:00.000 2019-01-06 17:00:00.000
1 2019-01-07 NULL NULL
1 2019-01-08 NULL NULL
1 2019-01-09 NULL NULL
1 2019-01-10 NULL NULL
2 2019-01-01 NULL NULL
2 2019-01-02 NULL NULL
2 2019-01-03 NULL NULL
2 2019-01-04 NULL NULL
2 2019-01-05 NULL NULL
2 2019-01-06 NULL NULL
2 2019-01-07 NULL NULL
2 2019-01-08 NULL NULL
2 2019-01-09 NULL NULL
2 2019-01-10 NULL NULL
3 2019-01-01 NULL NULL
3 2019-01-02 NULL NULL
3 2019-01-03 NULL NULL
3 2019-01-04 NULL NULL
3 2019-01-05 NULL NULL
3 2019-01-06 2019-01-06 10:00:00.000 2019-01-06 11:00:00.000
3 2019-01-07 NULL NULL
3 2019-01-08 NULL NULL
3 2019-01-09 NULL NULL
3 2019-01-10 NULL NULL