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


Comments

Post a Comment

Popular posts from this blog

Request failed with HTTP status 503 Server Error

Image
Problem: When 100's of users ae trying to access Reporting services report then, SSRS throws error as below : "Request failed with HTTP status 503 Server Error" and how to resolve this error Solution: From experience, this issue can be caused by multiple reasons Problem might be related to: 1 - TCP/IP Port Exhaustion: When a client initiates a TCP/IP socket connection to a server, the client typically connects to a specific port on the server and requests that the server respond to the client over a short-lived TCP or UDP port. The default range of short-lived ports used by client applications is 1025 through 5000. Under certain conditions, it is possible that the available ports in the default range are exhausted. 2 - If you are using any custom assembly method in any of your report (say for example PrivateFontCollection class or anything else), if yes then you might want to review that 3 - Change MaxActiveReqForOneUser from its default value o...
Read more

Removing and Manipulating Time portion from DateTime in SQLServer

Image
One of the common transformation we would have to do as part of ETL would be manipulating Time portion. Please see some of below code, which would help to resolve some of the time related transformation Case1 - Getting only Date portion from DateTime /* Removing Time portion from DateTime */   Select -- Standard date with time   getdate () As DateWithTime , -- Returns Date Portion Only   dateadd ( d , 0 , datediff ( d , 0 , getdate ())) As DateOnly Result as below: Case2 -  /* Removing Seconds from DataTime */ /* Removing Seconds from DataTime */ declare @t datetime set @T = GETDATE () SELECT @T as Original , DATEadd ( ss ,- datepart ( ss , @t ), @t ) as wihtout_sec -- if no milisecs , DATEADD ( mi , DateDiff ( mi , 0 , @t ), 0 ) as OneMore_Variation Case3 - We want to Keep Hours but remove Minutes and Seconds from DateTime /* Keep Hou...
Read more

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())  ...
Read more