SQL QUERY with custom columns from SUM

I would like to build a SQL query to get the sum of hours(difference between end and start) by week for each person. In my main table, I have 3 columns :

The result I expect :

Thanks for your help

Code done in SQL Server, consists of 3 steps

1. Create a CTE query to calculate the time difference and the week number
2. Create a CTE query to sum hours by ID and week number
3. Create a PIVOT Table to Convert Weeks to Columns Note: Date corrected data were used to fit the example

Solution

WITH hours_calculated as (
    SELECT [ID]
      ,[startDT]
      ,[endDT]
      , DATEDIFF(hh, [startDT], [endDT]) as Hours_Difference
      , CAST((DAY([startDT])-1)/7   AS INT) +1 AS WeekNumber
    FROM [development].[dbo].[hours_by_week]
), weeks_calculated as (

select ID, WeekNumber,SUM(Hours_Difference) as total_hours from hours_calculated group by ID, WeekNumber

)
select ID , [1] as W01,[2] as W02,[3] as W03,[4] as W04,[5] as W05
from weeks_calculated AS SourceTable
pivot
(
    SUM(total_hours)
    FOR WeekNumber IN ([1],[2],[3],[4],[5] )
)  AS PivotTable;

Output

ID  W01   W02   W03     W04     W05
1   7     NULL  NULL    NULL    NULL
2   NULL     9  NULL    NULL    NULL
3   2        4  3       NULL    NULL

Schema

CREATE TABLE [dbo].[hours_by_week](
    [ID] [tinyint] NOT NULL,
    [startDT] [datetime2](7) NOT NULL,
    [endDT] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO

References

Group by week of a particular month in sql server

How to get total number of hours between two dates in sql server?