Home Calculate Workday from Work Hours Spread Across 2 Calendar Days - SQL Server
Reply: 3

Calculate Workday from Work Hours Spread Across 2 Calendar Days - SQL Server

Mayank Gupta
1#
Mayank Gupta Published in 2018-02-13 13:04:28Z

Please refer to the data image below:

Table Structure:

CREATE TABLE [dbo].[tblData](
    [agentName] [nvarchar](255) NULL,
    [DateTime] [datetime] NULL,
    [loggedMins] [float] NULL,
    [activeMinutes] [float] NULL,
    [holdMinutes] [float] NULL,
    [inactiveMinutes] [float] NULL
) ON [PRIMARY]

Sample Data:

Insert Into TblData Values ('Doe, John', '01/21/2018 23:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 00:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 00:30:00', 30, 0.018, 0, 29.982) 
Insert Into TblData Values ('Doe, John', '01/22/2018 01:00:00', 30, 0, 0, 29.982) 
Insert Into TblData Values ('Doe, John', '01/22/2018 01:30:00', 30, 0, 0, 29.016) 
Insert Into TblData Values ('Doe, John', '01/22/2018 02:00:00', 30, 0, 0, 0) 
Insert Into TblData Values ('Doe, John', '01/22/2018 02:30:00', 30, 0, 0, 18.132) 
Insert Into TblData Values ('Doe, John', '01/22/2018 03:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 03:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 04:00:00', 27.55, 0, 0, 27.55) 
Insert Into TblData Values ('Doe, John', '01/22/2018 18:00:00', 19.1166666666667, 1.86578666666667, 0, 16.9660416666667) 
Insert Into TblData Values ('Doe, John', '01/22/2018 18:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 19:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 19:30:00', 30, 0, 0, 26.532) 
Insert Into TblData Values ('Doe, John', '01/22/2018 20:00:00', 30, 0, 0, 14.568) 
Insert Into TblData Values ('Doe, John', '01/22/2018 20:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 21:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 21:30:00', 30, 0, 0, 11.232) 
Insert Into TblData Values ('Doe, John', '01/22/2018 22:00:00', 30, 0, 0, 13.266) 
Insert Into TblData Values ('Doe, John', '01/22/2018 22:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 23:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/22/2018 23:30:00', 30, 0, 0, 16.551) 
Insert Into TblData Values ('Doe, John', '01/23/2018 00:00:00', 30, 4.416, 0, 25.584) 
Insert Into TblData Values ('Doe, John', '01/23/2018 00:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/23/2018 01:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/23/2018 01:30:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/23/2018 18:00:00', 30, 0, 0, 30) 
Insert Into TblData Values ('Doe, John', '01/23/2018 18:30:00', 30, 0, 0, 30) 

My problem is that I need to calculate work date on the basis of 'DateTime' column. John Doe works from 6PM to 4 AM. Work date needs to be added to new column which should be equal to 6PM date. Below is my logic:

  1. Compare DateTime of current column with previous column along with agentName.
  2. If difference is half hour and colleague is same, update final column with as of date of previous row.

I am having a hard time in translating into a code :( HELP!

Matthew Baker
2#
Matthew Baker Reply to 2018-02-13 15:23:30Z

Second answer:

SELECT  *
,       (SELECT MIN(DateTime)
         FROM   tblData S
         WHERE  S.DateTime <= M.DateTime
                AND S.DateTime > DATEADD(HOUR, -12, M.DateTime)
        ) ShiftStart
FROM    tblData M

Probably your best alternative then - this works on the idea that a shift will be no more that 12 hours, but you can tweak that. Also not the most efficient approach.

Matthew Baker
3#
Matthew Baker Reply to 2018-02-13 14:41:14Z

Have a look at this. I've cast the datetime to time only and put a pivot point of midday, if time is after midday then its today. if its before midday then its yesterday. I choose midday in case of early starts/late finishes etc.

SELECT  *
,       CASE WHEN CAST(DateTime AS TIME) > '12:00:00' THEN CAST(DateTime AS DATE)
             ELSE DATEADD(DAY, -1, CAST(DateTime AS DATE))
        END AS WorkDate
FROM    @tblData

That will give you a column you can group by. Maybe wrap it in a cte to help.

Mayank Gupta
4#
Mayank Gupta Reply to 2018-02-13 15:44:14Z

Answer from Matther Baker works perfectly!! I simple ranked rows by agentName and time; and then added agentName to the code so that the time for every agent gets counted separately:

--CREATING A TEMP TABLE
Select 
    row_number() over(order by agentName, [DateTime] asc) as [Rnk], * 
Into ##TmpData
from TblData
Order by agentName, [DateTime] asc

--ACTUAL LOGIC

SELECT  *,
       (SELECT MIN(DateTime) FROM   tblData S
        WHERE  S.DateTime <= M.DateTime
        AND S.DateTime > DATEADD(HOUR, -15, M.DateTime) and S.agentName = M.agentName
       ) ShiftStart
FROM    ##TmpData M


--DROPPING TEMP TABLE
Drop Table ##TmpData

I saw that no one ever did a shift for more than 13 hours and hence added buffer and changed time interval to 15.

Thank you so much guys!!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.324123 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO