3 Replies Latest reply on May 17, 2012 8:22 PM by Jonathan Drummey

    Padding and Running Sum

    Alex Kerin

      Data: I have shift starts and shift ends for employees. I want to apportion hours to a day (this will later be matched to a volume on the daily level)

      Issues: Shifts run over midnight. Therefore I have to apportion part of a shift to a volume for that day, and part of the shift to the volume for the next day

       

      I've tried tackling this several ways - custom SQL with running sum (thanks Richard Leeke) - fails because I'm not capturing a duration anywhere and don't have a datetime to use for the total for the day (if I use shift start then I may miss the last day of the data set as no shifts start on this day - no durations, and vice versa)

       

      I've tried padding by every hour and day thanks (@Jonathan Drummey), but duplication when the padding data and a shift start is at the same time causes a problem.

       

      Any ideas. Added bonus - I will want to do this by hour as well eventually so that I could understand the labor hours used between 12pm and 1PM on the 2nd Jan.

       

      My attempts attached....

        • 1. Re: Padding and Running Sum
          Alex Kerin

          Some progress - keep the shift end and start as well as the Combined DateTime (this allows us to look at all dates where any hours were worked). SQL:

          SELECT [Sheet1$].[ID] AS [ID],

            1 as [Delta],

            [Sheet1$].[Shift Start] AS [DateTime],

          [Sheet1$].[Shift Start] AS [Shift Start],

          [Sheet1$].[Shift End] AS [Shift End]

          FROM [Sheet1$]

          union all

          SELECT [Sheet1$].[ID] AS [ID],

            -1 as [Delta],

            [Sheet1$].[Shift End] AS [DateTime],

          [Sheet1$].[Shift Start] AS [Shift Start],

          [Sheet1$].[Shift End] AS [Shift End]

          FROM [Sheet1$]

           

          And then the duration in hours for that day as a calc:

           

          If [Delta]=1 then

            (if int([Shift End])-int([Shift Start])>0 then int([Shift Start])+1 - float([Shift Start])

             else [Shift End]-[Shift Start] end)*24

          elseif [Delta]=-1 then

             (if int([Shift End])-int([Shift Start])>0 then  float([Shift End])-int([Shift End])

             else 0 end)*24

          end

          1 of 1 people found this helpful
          • 2. Re: Padding and Running Sum
            Alex Kerin

            I also (think) I have solved the padding I needed to create to look at this by hour.

             

            The issue occurs if there is an hour during the day where a shift does not start or finish. Now I have no mark in that hour to divide a volume by. Presuming a shift runs over this hour, there are of course labor hours associated with that hour. I needed to pad the data out for all hours of the day. I did this with custom SQL:

             

            SELECT [Sheet1$].[ID] AS [ID],

              1 as [Delta],

              [Sheet1$].[Shift Start] AS [DateTime]

            FROM [Sheet1$]

            union all

            SELECT [Sheet1$].[ID] AS [ID],

              -1 as [Delta],

              [Sheet1$].[Shift End] AS [DateTime]

            FROM [Sheet1$]

            union all

            SELECT "HHMM" AS [ID],

            0 AS [Delta],

            CDate(Format([Sheet1$].[Shift Start],"mm/dd/yyyy") + " " + [HHMM$].[HH:MM]) As [DateTime]

            FROM [HHMM$], [Sheet1$]

            union all

            SELECT "HHMM" AS [ID],

            0 AS [Delta],

            CDate(Format([Sheet1$].[Shift End],"mm/dd/yyyy") + " " + [HHMM$].[HH:MM]) As [DateTime]

            FROM [HHMM$], [Sheet1$]

             

            HHMM is a text list of 00:00 to 23:00. This is similar to the padding created by Jonathan Drummey a while back, but here I avoid the need for a separate list of days (and where statements that seemed to cause performance issues), but it does mean in my case I need another union to pull in padding for shifts that end the day after.

             

            And yes, I am marking this as correct....

            1 of 1 people found this helpful
            • 3. Re: Padding and Running Sum
              Jonathan Drummey

              Nice work!

               

              Jonathan