Calculates Rostered rate types based on work segment

/***********************************************************************************
Calculates rostered rate types based on work segment
This does not calculate rates. It's purely for breaking down the roster record.



SELECT * FROM [dbo].[GetWorkSegments] ('B0271037793', 'grp-act-cen')

SELECT r.*
FROM nbc.Roster r
WHERE DATEPART(hour, Start) < 8 AND DATEPART(hour, [End]) > 10

***********************************************************************************/

CREATE FUNCTION [dbo].[GetWorkSegments]
(
    @WorkSegmentId VARCHAR(100)
, @RosteredService VARCHAR(50)
)
RETURNS @Totals TABLE
(
RosteredServiceID VARCHAR(100)
  , RosteredService VARCHAR(50)
  , ShiftId INT
  , RateDayType VARCHAR(20)
  , WeekdayId INT
  , ShortDay CHAR(3)
  , [Weekday] VARCHAR(12)
  , ShiftDateKey INT
  , ShiftStart DATETIME2
  , ShiftEnd DATETIME2
  , MinutesInShift MONEY -- so we can multiply it by a rate
  , MinutesInSegment MONEY -- so we can multiply it by a rate
)
AS
BEGIN
DECLARE @StartDate DATE = (SELECT MIN([start]) FROM nbc.Roster WHERE RosteredServiceId = @WorkSegmentId AND RosteredService = @RosteredService);
DECLARE @EndDate DATE = (SELECT MIN([End]) FROM nbc.Roster WHERE RosteredServiceId = @WorkSegmentId AND RosteredService = @RosteredService);
--DECLARE @EndDate DATE = DATEADD(DAY,1,GETDATE());

DECLARE @Dates TABLE
    (
         ThisDate DATETIME2 PRIMARY KEY
        ,ThisWeekDay VARCHAR(25)
        ,UNIQUE(ThisWeekDay,ThisDate)
    );

WITH Dates_CTE AS
(
    SELECT @StartDate AS ThisDate
            , DATENAME(DW,@StartDate) AS ThisWeekDay
    UNION ALL
    SELECT NextDate, DATENAME(DW,NextDate)
    FROM Dates_CTE
CROSS APPLY (VALUES(DATEADD(DAY,1,ThisDate))) NextDates(NextDate)
    WHERE NextDate <= @EndDate
)
INSERT INTO @Dates
SELECT ThisDate,ThisWeekDay FROM Dates_CTE OPTION (MAXRECURSION 0);


DECLARE @ShiftStartDates TABLE
(
StartDate DATETIME2
  , ShiftId INT
   ,WeekdayId INT
  , DayCode CHAR(3)
  , UNIQUE(StartDate)
);

INSERT INTO @ShiftStartDates
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE,'00:00',S.startTime),D.ThisDate) AS startDate
      , S.ID AS ShiftId
      , W.ID AS WeekdayId
  , W.code as DayCode
FROM Shifts S
JOIN Shifts_Weekdays SW ON S.ID = SW.shiftID
JOIN Weekdays W ON SW.weekdayId = W.Id
JOIN @Dates D ON W.weekday = D.ThisWeekDay


DECLARE @ShiftDates TABLE
(
StartDate DATETIME2
  , EndDate DATETIME2
  , ShiftId INT
  , WeekdayId INT
  , UNIQUE(StartDate)
);

INSERT INTO @ShiftDates
SELECT StartDate
      , (SELECT TOP 1 StartDate FROM @ShiftStartDates WHERE StartDate > S.StartDate) AS EndDate
      , ShiftId
      , WeekdayId
FROM @ShiftStartDates S;

--Get results
INSERT INTO @Totals
SELECT RosteredServiceId
  , RosteredService
      , ShiftId
      , RateDayType
      , WeekdayId
      , ShortDay
      , [Weekday]
      , ShiftDateKey
      , ShiftStart
      , ShiftEnd
      , MinutesInShift
      , MinutesInSegment
FROM
(
SELECT r.RosteredServiceId
  , r.RosteredService
  , SD.ShiftId
  , CASE
WHEN wd.code = 'SAT' THEN 'SAT'
WHEN wd.code = 'SUN' THEN 'SUN'
WHEN h.Holiday IS NOT NULL THEN 'HOLIDAY'
ELSE sh.shift
END AS RateDayType
  , SD.WeekdayId
  , wd.code ShortDay
  , wd.weekday
  , CASE
                WHEN r.[Start] >= SD.StartDate AND r.[End] >= SD.EndDate THEN FORMAT(r.[Start], 'yyyyMMdd') 
                WHEN r.[Start] >= SD.StartDate AND r.[End] <= SD.EndDate THEN FORMAT(r.[Start], 'yyyyMMdd') 
                WHEN r.[Start] <= SD.StartDate AND r.[End] >= SD.EndDate THEN FORMAT(SD.startDate, 'yyyyMMdd') 
                WHEN r.[Start] <= SD.StartDate AND r.[End] <= SD.EndDate THEN FORMAT(SD.startDate, 'yyyyMMdd') 
            END AS ShiftDateKey
  , CASE
                WHEN r.[Start] >= SD.StartDate AND r.[End] >= SD.EndDate THEN r.[Start]
                WHEN r.[Start] >= SD.StartDate AND r.[End] <= SD.EndDate THEN r.[Start]
                WHEN r.[Start] <= SD.StartDate AND r.[End] >= SD.EndDate THEN SD.startDate
                WHEN r.[Start] <= SD.StartDate AND r.[End] <= SD.EndDate THEN SD.startDate
            END AS ShiftStart
  , CASE
                WHEN r.[Start] >= SD.StartDate AND r.[End] >= SD.EndDate THEN SD.endDate
                WHEN r.[Start] >= SD.StartDate AND r.[End] <= SD.EndDate THEN r.[End]
                WHEN r.[Start] <= SD.StartDate AND r.[End] >= SD.EndDate THEN SD.endDate
                WHEN r.[Start] <= SD.StartDate AND r.[End] <= SD.EndDate THEN r.[End]
            END AS ShiftEnd
  , CASE
                WHEN r.[Start] >= SD.StartDate AND r.[End] >= SD.EndDate THEN DATEDIFF(MINUTE,r.[Start],SD.endDate)
                WHEN r.[Start] >= SD.StartDate AND r.[End] <= SD.EndDate THEN DATEDIFF(MINUTE,r.[Start],r.[End])
                WHEN r.[Start] <= SD.StartDate AND r.[End] >= SD.EndDate THEN DATEDIFF(MINUTE,SD.startDate,SD.endDate)
                WHEN r.[Start] <= SD.StartDate AND r.[End] <= SD.EndDate THEN DATEDIFF(MINUTE,SD.startDate,r.[End])
            END AS MinutesInShift
  , DATEDIFF(MINUTE,r.[Start],r.[End]) AS MinutesInSegment
FROM [nbc].[Roster] r
INNER JOIN @ShiftDates SD ON r.[End] > SD.StartDate AND r.[Start] < SD.EndDate
INNER JOIN [nbc].[ServiceCentre] sc ON sc.ServiceCentre = r.ServiceCentre
INNER JOIN [dbo].[Weekdays] wd ON wd.ID = SD.WeekdayId
INNER JOIN [dbo].[Shifts] sh ON sh.ID = sd.ShiftId
LEFT JOIN [nbc].[RegionalHoliday] h ON FORMAT(SD.startDate, 'yyyyMMdd') = h.[DimDate_DateKey]
   AND h.[ServiceCentre_ScId] = sc.ScId
WHERE r.RosteredServiceId = @WorkSegmentId AND r.RosteredService = @RosteredService
    ) PayIntervals

RETURN;
END

GO