By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH Calendar_CTE AS (
SELECT CAST('20230101' AS DATE) AS [Date]
UNION ALL
SELECT DATEADD(DAY, 1, [Date]) FROM Calendar_CTE
WHERE DATEADD(DAY, 1, [Date]) < '20240101'
)
SELECT *
INTO Calendar_Table
FROM Calendar_CTE
ORDER BY [Date]
OPTION (maxrecursion 0)
365 rows affected
DECLARE @tsql VARCHAR(MAX) = ''
DECLARE @pivot_columns VARCHAR(MAX) = ''
DECLARE @start_date DATE = '20230901'
DECLARE @end_date DATE = '20230905'
DECLARE @c_date DATE
DECLARE dates_cursor CURSOR FOR
SELECT [Date]
FROM Calendar_Table
WHERE
[Date] >= @start_date
AND
[Date] <= @end_date
OPEN dates_cursor
FETCH NEXT FROM dates_cursor INTO @c_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @pivot_columns += '[' + CONVERT(VARCHAR(10),@c_date, 23) + '],'
FETCH NEXT FROM dates_cursor INTO @c_date
END
CLOSE dates_cursor
DEALLOCATE dates_cursor
SET @pivot_columns = LEFT(@pivot_columns, LEN(@pivot_columns) - 1)
SET @tsql += 'SELECT
PersonalNr,' + @pivot_columns
SET @tsql += '
FROM
(
SELECT
T1.PersonalNr,
CT.[Date],
COUNT(*) AS ct
| PersonalNr | 2023-09-01 | 2023-09-02 | 2023-09-03 | 2023-09-04 | 2023-09-05 |
|---|---|---|---|---|---|
| 1 | 1 | 0 | 0 | 0 | 0 |
| 2 | 1 | 1 | 0 | 0 | 0 |
| 3 | 0 | 0 | 0 | 1 | 0 |