Odtnhj

I have a table that calculates what hours a person has worked. We have a night team that logon anytime after 4PM and logoff before 8AM the following morning. The table looks like the below.

 Workdate WorkHour
2018-11-13 20 -- this was the hour they logged on
2018-11-13 21
2018-11-13 22
2018-11-13 23
2018-11-14 0
2018-11-14 1
2018-11-14 2
2018-11-14 3
2018-11-14 4
2018-11-14 5 -- this was the hour they logged off

For the purpose of reporting, we only want to associate these hours worked to the date they first logged on, in this example the 13th of November 2018. My ideal output would look like the below.

Workdate WorkHour ReportingDate
2018-11-13 20 2018-11-13 
2018-11-13 21 2018-11-13 
2018-11-13 22 2018-11-13 
2018-11-13 23 2018-11-13 
2018-11-14 0 2018-11-13 
2018-11-14 1 2018-11-13 
2018-11-14 2 2018-11-13 
2018-11-14 3 2018-11-13 
2018-11-14 4 2018-11-13 
2018-11-14 5 2018-11-13 

Any ideas on how I can do this? Appreciate any help

Jess

You can think of it as a gaps and island problem where contiguous hours represent an island. You need to find all islands and find minimum date for each island:

DECLARE @T TABLE (userid INT, workdate DATE, workhour INT);
INSERT INTO @t VALUES
(1, '2018-11-13', 20),
(1, '2018-11-13', 21),
(1, '2018-11-13', 22),
(1, '2018-11-13', 23),
(1, '2018-11-14', 0),
(1, '2018-11-14', 1),
(1, '2018-11-14', 2),
(1, '2018-11-14', 3),
(1, '2018-11-14', 4),
(1, '2018-11-14', 5),
(1, '2018-11-20', 6);

WITH cte1 AS (
 SELECT userid, workdate, workhour
 , DATEADD(HOUR, workhour, CAST(workdate AS DATETIME)) AS workdatetime
 FROM @t
), cte2 AS (
 SELECT userid, workdate, workhour
 , CASE WHEN DATEDIFF(HOUR, LAG(workdatetime) OVER (PARTITION BY userid ORDER BY workdate, workhour), workdatetime) = 1 THEN 0 ELSE 1 END AS chg
 FROM cte1
), cte3 AS (
 SELECT userid, workdate, workhour
 , SUM(chg) OVER (PARTITION BY userid ORDER BY workdate, workhour) AS grp
 FROM cte2
)
SELECT userid, workdate, workhour, MIN(workdate) OVER (PARTITION BY userid, grp) AS ReportingDate
FROM cte3
ORDER BY userid, workdate, workhour

I'll update when I've got a working example, but try:

SELECT
MIN(WorkDate) OVER (PARTITION BY UserId ORDER BY WorkHour) [ReportingDate]
FROM <YourTable>
WHERE WorkDate >= CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)

This is a variation of "gaps-and-islands". You can identify the adjacent hours by subtracting an enumerated sequence. After that, you just need to take the max over the group.

select t.*,
 min(workdate) over (partition by datediff(hour, - seqnum, workdatehour) as imputed_workdate
from (select t.*,
 dateadd(hour, workhour, workdate) as workdatehour,
 row_number() over (order by workdate, workhour) as seqnum
 from t
 ) t