Reputation: 23
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:
I am having a hard time in translating into a code :( HELP!
Upvotes: 1
Views: 219
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
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
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