hi
hope this helps
create sample data
-- Create sample table
CREATE TABLE ServiceHours (
ID INT,
Code VARCHAR(10),
ServiceDate DATE,
Hours DECIMAL(5,2)
);
-- Insert sample data
INSERT INTO ServiceHours (ID, Code, ServiceDate, Hours)
VALUES
(123, 'H5', '2025-01-15', 1.00),
(123, 'H5', '2025-02-10', 0.25),
(123, 'H5', '2025-02-13', 1.00),
(123, 'H5', '2025-02-14', 0.50),
(123, 'H5', '2025-02-17', 1.00),
(123, 'H5', '2025-02-19', 0.25),
(123, 'H5', '2025-02-21', 1.00),
(123, 'H5', '2025-02-24', 1.00),
(123, 'H5', '2025-02-25', 1.00),
(123, 'H5', '2025-02-26', 1.00),
(123, 'H5', '2025-02-27', 0.25),
(123, 'H5', '2025-02-28', 1.00);
t-sql query
-- Build calendar inline using a tally (numbers) table
;WITH Calendar AS (
SELECT DATEADD(DAY, n, MinDate) AS ServiceDate
FROM (
SELECT MIN(ServiceDate) AS MinDate, MAX(ServiceDate) AS MaxDate
FROM ServiceHours
) r
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, r.MinDate, r.MaxDate) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_objects
) n
)
SELECT
c.ServiceDate,
d.ID,
d.Code,
COALESCE(t.Hours, 0) AS Hours
FROM Calendar c
CROSS JOIN (SELECT DISTINCT ID, Code FROM ServiceHours) d
LEFT JOIN ServiceHours t
ON t.ServiceDate = c.ServiceDate
AND t.ID = d.ID
AND t.Code = d.Code
ORDER BY d.ID, c.ServiceDate;
t-sql result sample
