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

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

user3217
1#
user3217 Published in May 21, 2018, 6:47 pm

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!

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO