How do I calculate amount of time in each week between two dates – SQL Server

[EDITED TO SIMPLIFY]

I have 500+ records. All of which have a reference number, a start date, an end date and a total machining time.

Ref    StartDate    EndDate    MachineTimeHours
123    24/01/2020   30/01/2020   28
321    25/02/2020   27/02/2020   18

Starting at the start date, I need to calculate how many machining hours fall into 1 week and how many fall into the next. Our working days are Monday to Thursday 8 Hours & Friday 4 Hours.

Ref 321 has a start of 25/2 which is a Tuesday and a finish date of 27/2 which is a Thursday in the same week. This will calculate as all 18 hours being in the same week. Ref 123 has a start of 24/01. This is a Friday in Week 4 of 2020. Based on my rules, that would be 4 hours in week 4 and 24 Hours in week 5.

I have a table called ‘DatesList’ which has all days on it (as well as week number and working hours).

I need my table to list each record for each week and I’ll do the grouping on a separate report.

In effect I’d like

Ref    StartDate    EndDate    MachineTimeHours   Week   Hours
123    24/01/2020   30/01/2020   28                4       4
123    24/01/2020   30/01/2020   28                5       24 
321    25/02/2020   27/02/2020   18                9       18

Answer

You can start with creating some reference tables.

For the example those are just temporary tables.

Reference data:

--
-- Reference tables
-- 
CREATE TABLE #ref_calendar
(
   CalDate DATE PRIMARY KEY,
   DayOfWeek SMALLINT NOT NULL,
   WeekNr SMALLINT NOT NULL,
   IsHoliday BIT NOT NULL DEFAULT 0
);

DECLARE @year int = 2020;
SET DATEFIRST 1; -- 1: monday start

;WITH RCTE_DATES AS
(
  SELECT 
  DATEFROMPARTS(@year, 1, 1) AS caldate

  UNION ALL

  SELECT dateadd(day, 1, caldate)
  FROM RCTE_DATES
  WHERE caldate <= DATEFROMPARTS(@year, 12, 31)
)
INSERT INTO #ref_calendar (CalDate, DayOfWeek, WeekNr)
SELECT 
caldate, 
DATEPART(weekday, caldate) AS DayOfWeek,
DATEPART(week, caldate) AS WeekNr
FROM rcte_dates c
WHERE NOT EXISTS
(
  SELECT 1
  FROM #ref_calendar ref
  WHERE ref.CalDate = c.caldate
)
OPTION (MAXRECURSION 366);

CREATE TABLE #ref_workhours
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  DayOfWeek SMALLINT NOT NULL,
  WorkHours DECIMAL(4,2) NOT NULL,
  ActiveFrom DATE NOT NULL DEFAULT GetDate(),
  ActiveTill DATE
);

INSERT INTO #ref_workhours 
(DayOfWeek, WorkHours) VALUES
 (1, 8.0), (2, 8.0), (3, 8.0), (4, 8.0), (5, 4.0),
 (6, 0), (7, 0);

Some sample data:

--
-- Sample data
--
CREATE TABLE YourDateRangeTable
(  
  Id INT IDENTITY(1,1) PRIMARY KEY,
  JobNumber INT NOT NULL,
  PartNumber VARCHAR(8) NOT NULL,
  Machine CHAR(3) NOT NULL,
  StartDate DATE NOT NULL, 
  EndDate DATE NOT NULL
);

INSERT INTO YourDateRangeTable 
(JobNumber, PartNumber, Machine, StartDate, EndDate) values 
(12345, 'XYZ321', 'DL8', '2020-01-24', '2020-01-30');

Then you can run a query that uses the reference tables.

SELECT JobNumber, PartNumber, Machine
, YEAR(cal.CalDate) AS [Year]
, cal.WeekNr AS [Week]
, SUM(w.WorkHours) AS [Hours]
FROM YourDateRangeTable t
JOIN #ref_calendar cal
  ON cal.CalDate >= t.StartDate 
 AND cal.CalDate < t.EndDate
JOIN #ref_workhours w
  ON w.DayOfWeek = cal.DayOfWeek
GROUP BY JobNumber, PartNumber, Machine
, YEAR(cal.CalDate), cal.WeekNr;

Returns:

JobNumber   PartNumber  Machine     Year    Week    Hours
12345       XYZ321      DL8         2020        4   4.00
12345       XYZ321      DL8         2020        5   24.00

A test on db<>fiddle here