CODEWITHSUNDEEP
sql-servert-sql
Mayank Gupta
Mayank Gupta

Reputation: 23

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

Please refer to the data image below:

Table Structure

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!

Upvotes: 1

Views: 219

Answers (3)

Mayank Gupta
Mayank Gupta

Reputation: 23

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!!

Upvotes: 1

Matthew Baker
Matthew Baker

Reputation: 2729

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.

Upvotes: 0

Matthew Baker
Matthew Baker

Reputation: 2729

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.

Upvotes: 0

Related Questions