CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Phani
Phani

Reputation: 103

Number of Shifts betweens two times

Can you please help me with the following.

I am trying to calculate how may shifts a patient was in the hospital. The shifts timings start from 7:00 AM to 6:59 PM and 7:00 PM to 6.59 AM.

If a patient was admitted to a location after the start of the shift, we ignore that shift in the total calculation.

Here is the sample data and how the end result should look like :

DECLARE @T AS TABLE
(
    ID          INT,
    LOCATION VARCHAR(10),
    Date_entered DATETIME,
    date_left datetime
);

DECLARE @endresult AS TABLE
(
    ID          INT,
    LOCATION VARCHAR(10),
    Date_entered DATETIME,
    date_left datetime,
    Total_shifts int
)


insert into @T VALUES
(1,'AB','01/01/2019 07:10','01/01/2019 20:30'), 
(2,'CD','01/01/2019 20:30','01/04/2019 02:30'), 
(3,'EF','01/04/2019 02:30','01/07/2019 19:30'), 
(4,'GH','01/07/2019 19:30','01/08/2019 13:30')


insert into @endresult VALUES
(1,'AB','01/01/2019 07:10','01/01/2019 20:30',1), 
(2,'CD','01/01/2019 20:30','01/04/2019 02:30',4), 
(3,'EF','01/04/2019 02:30','01/07/2019 19:30',8), 
(4,'GH','01/07/2019 19:30','01/08/2019 13:30',1) 

SELECT * FROM @t

select * from @endresult

I tried using a recursive CTE, but the query is taking too much time to complete. Any simple way to calculate the timings?

Upvotes: 0

Views: 73

Answers (2)

GMB
GMB

Reputation: 222582

Here is a query that returns the correct results for your sample data :

select 
    t.*, 
    DATEDIFF(DAY, date_entered, date_left) * 2
        - CASE WHEN DATEPART(HOUR, date_entered) < 7 THEN 0 WHEN DATEPART(HOUR, date_entered) < 19 THEN 1 ELSE 2 END
        + CASE WHEN DATEPART(HOUR, date_left)    < 7 THEN 0 WHEN DATEPART(HOUR, date_left)    < 19 THEN 1 ELSE 2 END
    AS Total_shifts
from @t t;

The logic is to first count how many days occured between entrance and exit, then multiply it by two to get a raw number of shifts ; then adjust the raw count by checking the times when the patient entered and exited.

This demo on DB Fiddle with your sample data returns :

ID | LOCATION | Date_entered        | date_left           | Total_shifts
-: | :------- | :------------------ | :------------------ | -----------:
 1 | AB       | 01/01/2019 07:10:00 | 01/01/2019 20:30:00 |            1
 2 | CD       | 01/01/2019 20:30:00 | 04/01/2019 02:30:00 |            4
 3 | EF       | 04/01/2019 02:30:00 | 07/01/2019 19:30:00 |            8
 4 | GH       | 07/01/2019 19:30:00 | 08/01/2019 13:30:00 |            1

Upvotes: 2

Nick
Nick

Reputation: 147206

Here's a query that will give you the results you want. It uses 3 CTEs, the first simply selects the minimum Date_entered and maximum date_left values, the second aligns the minimum Date_entered value to a shift start time (7AM or 7PM), and the third recursive CTE generates a list of all the shift start times between the minimum Date_entered and maximum date_left values. Finally we JOIN that CTE to the admissions table and count the number of shift start times between Date_entered and date_left:

WITH cte AS 
    (SELECT MIN(Date_entered) AS min_date, MAX(date_left) AS max_date
     FROM T),
    cte1 AS
   (SELECT CASE WHEN DATEPART(hour, min_date) < 7 THEN DATEADD(hour, 19, DATEADD(day, -1, CONVERT(DATE, min_date)))
                ELSE DATEADD(hour, 7, CONVERT(DATETIME, CONVERT(DATE, min_date)))
                END AS min_shift
    FROM cte),
    shifts AS 
   (SELECT min_shift AS shift_start
    FROM cte1
    UNION ALL 
    SELECT DATEADD(hour, 12, shift_start)
    FROM shifts
    WHERE shift_start < (SELECT max_date FROM cte))
SELECT T.ID, T.LOCATION, T.Date_Entered, T.date_left, COUNT(s.shift_start) AS Total_shifts
FROM T
JOIN shifts s ON s.shift_start BETWEEN T.Date_Entered AND T.date_left
GROUP BY T.ID, T.LOCATION, T.Date_Entered, T.date_left

Output:

ID  LOCATION    Date_Entered            date_left               Total_shifts
1   AB          01/01/2019 07:10:00     01/01/2019 20:30:00     1
2   CD          01/01/2019 20:30:00     04/01/2019 06:59:00     4
3   EF          04/01/2019 07:00:00     07/01/2019 19:30:00     8
4   GH          07/01/2019 19:30:00     08/01/2019 13:30:00     1

Demo on dbfiddle

Upvotes: 1

Related Questions