CODEWITHSUNDEEP
datediff
user23507398
user23507398

Reputation: 1

Date diff on a single column based on a "status" in SQL Server

I have a list of users by email and their status.
ID| Datetime | Status Email | 2/19/2024 6:30 | |john.com | 2/19/2024 07:00| Available| |john.com | 2/19/2024 07:30 Available | 2/19/2024 09:30| Unavailable 2/19/2024 10:30 | Available.

The desired output for each user is: enter image description here Using SQL SERVER

The problem I have is calculating Total Unavailable status with datediff because as shown above the user went unavailable at 9:30 and available at 10:30 for a difference of 60 minutes. A user can go Unavailable to Available many times so it like capturing those data pairs.

I have tried subquery, case statements, I tried flagging rows to be grouped. The essence of the issue is that the dates are all in one column and the status is the only thing that changes. I need to be able to calculate the date difference from rows that are marked unavailable to available for many users in a single table. I need a total summary as shown in my expected results. Thoughts?

Temp tables do not seem to help because I need the unavailable to available pair to be on a single row, several times as needed for each user. I am not and expert so any direction would be great.

email starttime endtime total_available hours total unavailable
john doe 02/01/07:00 02/01/2024 5:00 10 hours ??????
jane Cell 4

I can already calculate everything but total unavailable. because not able to capture the unavailable / available pairs in for each user in the date. Will this take some advanced scripting of some type?

Upvotes: 0

Views: 33

Answers (0)

Related Questions