TimeSeries query based on Statuses (per minute) - Midnight overlap

Transact-SQL
,
1

Below is the input table:
--Create table
CREATE TABLE tblConf(ID INT IDENTITY(1,1), ConfName NVARCHAR(10), StartDateTime DATETIME, ConfStat NVARCHAR(10), Duration INT);
--Create index
CREATE CLUSTERED INDEX idx_Conf_SQ ON tblConf (ID);

--Insert values
INSERT INTO dbo.tblConf
VALUES
('A', '6/11/18 1:00 PM', 'Booked', 60),
('A', '6/11/18 2:00 PM', 'Booked', 60),
('A', '6/11/18 3:00 PM', 'Booked', 120),
('A', '6/11/18 6:20 PM', 'Occupied', 24),
('A', '6/11/18 9:20 PM', 'Occupied', 8),
('A', '6/11/18 11:58 PM', 'Occupied', 3),
('A', '6/12/18 7:22 AM', 'Occupied', 293),
('A', '6/12/18 9:30 AM', 'Booked', 30),
('A', '6/12/18 10:00 AM', 'Booked', 60),
('A', '6/12/18 11:30 AM', 'Booked', 30),
('A', '6/12/18 12:15 PM', 'Booked', 60),
('A', '6/12/18 12:17 PM', 'Occupied', 264),
('A', '6/12/18 1:15 PM', 'Booked', 105),
('A', '6/12/18 3:00 PM', 'Booked', 60),
('A', '6/12/18 4:00 PM', 'Booked', 60),
('A', '6/12/18 6:39 PM', 'Occupied', 28),
('A', '6/12/18 9:03 PM', 'Occupied', 6),
('B', '6/4/18 9:00 AM', 'Booked', 120),
('B', '6/4/18 3:10 PM', 'Occupied', 11),
('B', '6/4/18 9:22 PM', 'Occupied', 53),
('B', '6/4/18 11:12 PM', 'Occupied', 28),
('B', '6/4/18 11:55 PM', 'Occupied', 6),
('B', '6/5/18 12:39 AM', 'Occupied', 2407),
('B', '6/5/18 9:00 AM', 'Booked', 120),
('B', '6/5/18 11:00 AM', 'Booked', 60),
('B', '6/5/18 12:00 PM', 'Booked', 300),
('B', '6/6/18 9:00 AM', 'Booked', 120),
('B', '6/6/18 11:00 AM', 'Booked', 240),
('B', '6/6/18 3:00 PM', 'Booked', 30),
('B', '6/6/18 3:30 PM', 'Booked', 90),
('B', '6/7/18 9:00 AM', 'Booked', 120),
('B', '6/7/18 11:00 AM', 'Booked', 60),
('B', '6/7/18 12:00 PM', 'Booked', 120),
('B', '6/7/18 2:00 PM', 'Booked', 60),
('B', '6/7/18 2:15 PM', 'Occupied', 8),
('B', '6/7/18 3:45 PM', 'Booked', 75);

Need following output using query:

ConfName StartDateTime Summary/Status Status EndDateTime
A 6/11/18 12:00 AM Summary NOT Booked and NOT Occupied 6/11/18 8:18 AM
A 6/11/18 12:00 AM NOT Booked NOT Booked 6/11/18 8:18 AM
A 6/11/18 12:00 AM NOT Occupied NOT Occupied 6/11/18 8:18 AM
A 6/11/18 8:18 AM NOT Booked NOT Booked 6/11/18 9:00 AM
A 6/11/18 8:18 AM Occupied Occupied 6/11/18 11:13 AM
A 6/11/18 8:18 AM Summary NOT Booked and Occupied 6/11/18 9:00 AM
A 6/11/18 9:00 AM Summary Booked and Occupied 6/11/18 11:13 AM
A 6/11/18 9:00 AM Booked Booked 6/11/18 12:00 PM
A 6/11/18 11:13 AM NOT Occupied NOT Occupied 6/11/18 12:00 PM
A 6/11/18 11:13 AM Summary Booked and NOT Occupied 6/11/18 12:00 PM
A 6/11/18 12:00 PM Summary NOT Booked and NOT Occupied 6/11/18 12:32 PM
A 6/11/18 12:00 PM NOT Occupied NOT Occupied 6/11/18 12:32 PM
A 6/11/18 12:00 PM NOT Booked NOT Booked 6/11/18 12:32 PM
A 6/11/18 12:32 PM Summary NOT Booked and Occupied 6/11/18 1:00 PM
A 6/11/18 12:32 PM Occupied Occupied 6/11/18 4:54 PM
A 6/11/18 12:32 PM NOT Booked NOT Booked 6/11/18 1:00 PM
A 6/11/18 1:00 PM Booked Booked 6/11/18 2:00 PM
A 6/11/18 1:00 PM Summary Booked and Occupied 6/11/18 4:54 PM
A 6/11/18 2:00 PM Booked Booked 6/11/18 3:00 PM
A 6/11/18 3:00 PM Booked Booked 6/11/18 5:00 PM
A 6/11/18 4:54 PM NOT Occupied NOT Occupied 6/11/18 5:00 PM
A 6/11/18 4:54 PM Summary Booked and NOT Occupied 6/11/18 5:00 PM
A 6/11/18 5:00 PM NOT Booked NOT Booked 6/11/18 6:20 PM
A 6/11/18 5:00 PM NOT Occupied NOT Occupied 6/11/18 6:20 PM
A 6/11/18 5:00 PM Summary NOT Booked and NOT Occupied 6/11/18 6:20 PM
A 6/11/18 6:20 PM Occupied Occupied 6/11/18 6:44 PM
A 6/11/18 6:20 PM NOT Booked NOT Booked 6/11/18 6:44 PM
A 6/11/18 6:20 PM Summary NOT Booked and Occupied 6/11/18 6:44 PM
A 6/11/18 6:44 PM NOT Booked NOT Booked 6/11/18 9:20 PM
A 6/11/18 6:44 PM NOT Occupied NOT Occupied 6/11/18 9:20 PM
A 6/11/18 6:44 PM Summary NOT Booked and NOT Occupied 6/11/18 9:20 PM
A 6/11/18 9:20 PM Summary NOT Booked and Occupied 6/11/18 9:28 PM
A 6/11/18 9:20 PM Occupied Occupied 6/11/18 9:28 PM
A 6/11/18 9:20 PM NOT Booked NOT Booked 6/11/18 9:28 PM
A 6/11/18 9:28 PM NOT Occupied NOT Occupied 6/11/18 11:58 PM
A 6/11/18 9:28 PM NOT Booked NOT Booked 6/11/18 11:58 PM
A 6/11/18 9:28 PM Summary NOT Booked and NOT Occupied 6/11/18 11:58 PM
A 6/11/18 11:58 PM Occupied Occupied 6/12/18 12:00 AM
A 6/11/18 11:58 PM NOT Booked NOT Booked 6/12/18 12:00 AM
A 6/11/18 11:58 PM Summary NOT Booked and Occupied 6/12/18 12:00 AM
A 6/12/18 12:00 AM NOT Booked NOT Booked 6/12/18 12:01 AM
A 6/12/18 12:00 AM Occupied Occupied 6/12/18 12:01 AM
A 6/12/18 12:00 AM Summary NOT Booked and Occupied 6/12/18 12:01 AM
A 6/12/18 12:01 AM NOT Booked NOT Booked 6/12/18 7:22 AM
A 6/12/18 12:01 AM NOT Occupied NOT Occupied 6/12/18 7:22 AM
A 6/12/18 12:01 AM Summary NOT Booked and NOT Occupied 6/12/18 7:22 AM
A 6/12/18 7:22 AM Occupied Occupied 6/12/18 12:15 PM
A 6/12/18 7:22 AM NOT Booked NOT Booked 6/12/18 9:30 AM
A 6/12/18 7:22 AM Summary NOT Booked and Occupied 6/12/18 9:30 AM
A 6/12/18 9:30 AM Booked Booked 6/12/18 10:00 AM
A 6/12/18 9:30 AM Summary Booked and Occupied 6/12/18 11:00 AM
A 6/12/18 10:00 AM Booked Booked 6/12/18 11:00 AM
A 6/12/18 11:00 AM Summary NOT Booked and Occupied 6/12/18 11:30 AM
A 6/12/18 11:00 AM NOT Booked NOT Booked 6/12/18 11:30 AM
A 6/12/18 11:30 AM Booked Booked 6/12/18 12:00 PM
A 6/12/18 11:30 AM Summary Booked and Occupied 6/12/18 12:00 PM
A 6/12/18 12:00 PM Summary NOT Booked and Occupied 6/12/18 12:15 PM
A 6/12/18 12:00 PM NOT Booked NOT Booked 6/12/18 12:15 PM
A 6/12/18 12:15 PM Booked Booked 6/12/18 1:15 PM
A 6/12/18 12:15 PM Summary Booked and NOT Occupied 6/12/18 12:17 PM
A 6/12/18 12:15 PM NOT Occupied NOT Occupied 6/12/18 12:17 PM
A 6/12/18 12:17 PM Occupied Occupied 6/12/18 4:41 PM
A 6/12/18 12:17 PM Summary Booked and Occupied 6/12/18 4:41 PM
A 6/12/18 1:15 PM Booked Booked 6/12/18 3:00 PM
A 6/12/18 3:00 PM Booked Booked 6/12/18 4:00 PM
A 6/12/18 4:00 PM Booked Booked 6/12/18 5:00 PM
A 6/12/18 4:41 PM Summary Booked and NOT Occupied 6/12/18 5:00 PM
A 6/12/18 4:41 PM NOT Occupied NOT Occupied 6/12/18 5:00 PM
A 6/12/18 5:00 PM Summary NOT Booked and NOT Occupied 6/12/18 6:39 PM
A 6/12/18 5:00 PM NOT Booked NOT Booked 6/12/18 6:39 PM
A 6/12/18 5:00 PM NOT Occupied NOT Occupied 6/12/18 6:39 PM
A 6/12/18 6:39 PM Occupied Occupied 6/12/18 7:07 PM
A 6/12/18 6:39 PM NOT Booked NOT Booked 6/12/18 7:07 PM
A 6/12/18 6:39 PM Summary NOT Booked and Occupied 6/12/18 7:07 PM
A 6/12/18 7:07 PM NOT Occupied NOT Occupied 6/12/18 9:03 PM
A 6/12/18 7:07 PM NOT Booked NOT Booked 6/12/18 9:03 PM
A 6/12/18 7:07 PM Summary NOT Booked and NOT Occupied 6/12/18 9:03 PM
A 6/12/18 9:03 PM Occupied Occupied 6/12/18 9:09 PM
A 6/12/18 9:03 PM NOT Booked NOT Booked 6/12/18 9:09 PM
A 6/12/18 9:03 PM Summary NOT Booked and Occupied 6/12/18 9:09 PM
A 6/12/18 9:09 PM NOT Booked NOT Booked 6/13/18 12:00 AM
A 6/12/18 9:09 PM NOT Occupied NOT Occupied 6/13/18 12:00 AM
A 6/12/18 9:09 PM Summary NOT Booked and NOT Occupied 6/13/18 12:00 AM
A 6/13/18 12:00 AM Summary NOT Booked and NOT Occupied 6/13/18 7:12 AM
A 6/13/18 12:00 AM NOT Booked NOT Booked 6/13/18 7:12 AM
A 6/13/18 12:00 AM NOT Occupied NOT Occupied 6/13/18 7:12 AM
B 6/4/18 12:00 AM Summary NOT Booked and NOT Occupied 6/4/18 9:00 AM
B 6/4/18 12:00 AM NOT Booked NOT Booked 6/4/18 9:00 AM
B 6/4/18 12:00 AM NOT Occupied NOT Occupied 6/4/18 9:00 AM
B 6/4/18 9:00 AM Booked Booked 6/4/18 11:00 AM
B 6/4/18 9:00 AM Summary Booked and NOT Occupied 6/4/18 11:00 AM
B 6/4/18 9:00 AM NOT Occupied NOT Occupied 6/4/18 11:00 AM
B 6/4/18 11:00 AM Summary NOT Booked and NOT Occupied 6/4/18 3:10 PM
B 6/4/18 11:00 AM NOT Occupied NOT Occupied 6/4/18 3:10 PM
B 6/4/18 11:00 AM NOT Booked NOT Booked 6/4/18 3:10 PM
B 6/4/18 3:10 PM Occupied Occupied 6/4/18 3:21 PM
B 6/4/18 3:10 PM NOT Booked NOT Booked 6/4/18 3:21 PM
B 6/4/18 3:10 PM Summary NOT Booked and Occupied 6/4/18 3:21 PM
B 6/4/18 3:21 PM NOT Booked NOT Booked 6/4/18 9:22 PM
B 6/4/18 3:21 PM NOT Occupied NOT Occupied 6/4/18 9:22 PM
B 6/4/18 3:21 PM Summary NOT Booked and NOT Occupied 6/4/18 9:22 PM
B 6/4/18 9:22 PM Summary NOT Booked and Occupied 6/4/18 10:15 PM
B 6/4/18 9:22 PM Occupied Occupied 6/4/18 10:15 PM
B 6/4/18 9:22 PM NOT Booked NOT Booked 6/4/18 10:15 PM
B 6/4/18 10:15 PM NOT Booked NOT Booked 6/4/18 11:12 PM
B 6/4/18 10:15 PM NOT Occupied NOT Occupied 6/4/18 11:12 PM
B 6/4/18 10:15 PM Summary NOT Booked and NOT Occupied 6/4/18 11:12 PM
B 6/4/18 11:12 PM Summary NOT Booked and Occupied 6/4/18 11:40 PM
B 6/4/18 11:12 PM NOT Booked NOT Booked 6/4/18 11:40 PM
B 6/4/18 11:12 PM Occupied Occupied 6/4/18 11:40 PM
B 6/4/18 11:40 PM NOT Occupied NOT Occupied 6/4/18 11:55 PM
B 6/4/18 11:40 PM NOT Booked NOT Booked 6/4/18 11:55 PM
B 6/4/18 11:40 PM Summary NOT Booked and NOT Occupied 6/4/18 11:55 PM
B 6/4/18 11:55 PM NOT Booked NOT Booked 6/5/18 12:00 AM
B 6/4/18 11:55 PM Occupied Occupied 6/5/18 12:00 AM
B 6/4/18 11:55 PM Summary NOT Booked and Occupied 6/5/18 12:00 AM
B 6/5/18 12:00 AM Summary NOT Booked and Occupied 6/5/18 12:01 AM
B 6/5/18 12:00 AM NOT Booked NOT Booked 6/5/18 12:01 AM
B 6/5/18 12:00 AM Occupied Occupied 6/5/18 12:01 AM
B 6/5/18 12:01 AM NOT Booked NOT Booked 6/5/18 12:39 AM
B 6/5/18 12:01 AM NOT Occupied NOT Occupied 6/5/18 12:39 AM
B 6/5/18 12:01 AM Summary NOT Booked and NOT Occupied 6/5/18 12:39 AM
B 6/5/18 12:39 AM NOT Booked NOT Booked 6/5/18 9:00 AM
B 6/5/18 12:39 AM Occupied Occupied 6/6/18 12:00 AM
B 6/5/18 12:39 AM Summary NOT Booked and Occupied 6/5/18 9:00 AM
B 6/5/18 9:00 AM Summary Booked and Occupied 6/5/18 5:00 PM
B 6/5/18 9:00 AM Booked Booked 6/5/18 11:00 AM
B 6/5/18 11:00 AM Booked Booked 6/5/18 12:00 PM
B 6/5/18 12:00 PM Booked Booked 6/5/18 5:00 PM
B 6/5/18 5:00 PM Summary NOT Booked and Occupied 6/6/18 12:00 AM
B 6/5/18 5:00 PM NOT Booked NOT Booked 6/6/18 12:00 AM
B 6/6/18 12:00 AM Summary NOT Booked and Occupied 6/6/18 9:00 AM
B 6/6/18 12:00 AM Occupied Occupied 6/6/18 4:46 PM
B 6/6/18 12:00 AM NOT Booked NOT Booked 6/6/18 9:00 AM
B 6/6/18 9:00 AM Booked Booked 6/6/18 11:00 AM
B 6/6/18 9:00 AM Summary Booked and Occupied 6/6/18 4:46 PM
B 6/6/18 11:00 AM Booked Booked 6/6/18 3:00 PM
B 6/6/18 3:00 PM Booked Booked 6/6/18 3:30 PM
B 6/6/18 3:30 PM Booked Booked 6/6/18 5:00 PM
B 6/6/18 4:46 PM Summary Booked and NOT Occupied 6/6/18 5:00 PM
B 6/6/18 4:46 PM NOT Occupied NOT Occupied 6/6/18 5:00 PM
B 6/6/18 5:00 PM Summary NOT Booked and NOT Occupied 6/7/18 12:00 AM
B 6/6/18 5:00 PM NOT Booked NOT Booked 6/7/18 12:00 AM
B 6/6/18 5:00 PM NOT Occupied NOT Occupied 6/7/18 12:00 AM
B 6/7/18 12:00 AM Summary NOT Booked and NOT Occupied 6/7/18 9:00 AM
B 6/7/18 12:00 AM NOT Occupied NOT Occupied 6/7/18 9:00 AM
B 6/7/18 12:00 AM NOT Booked NOT Booked 6/7/18 9:00 AM
B 6/7/18 9:00 AM Booked Booked 6/7/18 11:00 AM
B 6/7/18 9:00 AM Summary Booked and NOT Occupied 6/7/18 2:15 PM
B 6/7/18 9:00 AM NOT Occupied NOT Occupied 6/7/18 2:15 PM
B 6/7/18 11:00 AM Booked Booked 6/7/18 12:00 PM
B 6/7/18 12:00 PM Booked Booked 6/7/18 2:00 PM
B 6/7/18 2:00 PM Booked Booked 6/7/18 3:00 PM
B 6/7/18 2:15 PM Summary Booked and Occupied 6/7/18 2:23 PM
B 6/7/18 2:15 PM Occupied Occupied 6/7/18 2:23 PM
B 6/7/18 2:23 PM Summary Booked and NOT Occupied 6/7/18 3:00 PM
B 6/7/18 2:23 PM NOT Occupied NOT Occupied 6/7/18 3:00 PM
B 6/7/18 3:00 PM NOT Occupied NOT Occupied 6/7/18 3:45 PM
B 6/7/18 3:00 PM NOT Booked NOT Booked 6/7/18 3:45 PM
B 6/7/18 3:00 PM Summary NOT Booked and NOT Occupied 6/7/18 3:45 PM
B 6/7/18 3:45 PM NOT Occupied NOT Occupied 6/7/18 5:00 PM
B 6/7/18 3:45 PM Booked Booked 6/7/18 5:00 PM
B 6/7/18 3:45 PM Summary Booked and NOT Occupied 6/7/18 5:00 PM
B 6/7/18 5:00 PM NOT Occupied NOT Occupied 6/8/18 12:00 AM
B 6/7/18 5:00 PM NOT Booked NOT Booked 6/8/18 12:00 AM
B 6/7/18 5:00 PM Summary NOT Booked and NOT Occupied 6/8/18 12:00 AM
B 6/8/18 12:00 AM Summary NOT Booked and NOT Occupied 6/8/18 9:00 AM
B 6/8/18 12:00 AM NOT Occupied NOT Occupied 6/8/18 9:00 AM
B 6/8/18 12:00 AM NOT Booked NOT Booked 6/8/18 9:00 AM
2

It's not at all clear what rules you're trying to implement and your sample data doesn't seem to align with your expected output - for example why does 6/11/18 8:18 AM appear in the output despite not appearing anywhere in the data.

What are the rules for why rows should appear? Why are there summary rows for some times and not others?

3

Hi Andy,

  1. Why does 6/11/18 8:18 AM appears in the output ==> I've missed the rows from the earlier part of the day
    INSERT INTO dbo.tblConf
    VALUES
    ('A', '06/11/18 08:18 AM', 'Occupied',175),
    ('A', '06/11/18 09:00 AM', 'Booked', 180),
    ('A', '06/11/18 12:32 PM', 'Occupied', 262)

  2. Summary rows are showing sometimes and not for others ==> Summary rows comes for the combination of the following 4 Statuses:

'Booked and Occupied'
'Booked and NOT Occupied'
'NOT Booked and Occupied'
'NOT Booked and NOT Occupied'

Rest of the Statuses:
'Booked'
'Occupied'
'NOT Booked'
'NOT Occupied'

I hope I've clarified all your queries.

4

I think this would get you close:

With CTE_Changepoints As
(
	Select
		ConfName,
		ChangeTime,
		Sum(Booked) As Booked,
		Sum(Occupied) As Occupied
	From
	(
		Select 
			ConfName,
			StartDateTime As ChangeTime,
			Case ConfStat When 'Booked' Then +1 End As Booked,
			Case ConfStat When 'Occupied' Then +1 End As Occupied
		From tblConf
		Union all 
		Select 
			ConfName,
			dateadd(minute, Duration, StartDateTime) As ChangeTime,
			Case ConfStat When 'Booked' Then -1 End As Booked,
			Case ConfStat When 'Occupied' Then -1 End As Occupied
		From tblConf
	) CHANGE
	Group By
		ConfName,
		ChangeTime
),
CTE_AddLabels As
(
Select 1 As Ordinal, ConfName, ChangeTime, Cast(Null As VarChar(Max)) As Summary, Case When Booked = 1 Then 'Booked' Else 'Not Booked' End As Status From CTE_Changepoints
Union All Select 2, ConfName, ChangeTime, Null As Summary, Case When Occupied = 1 Then 'Occupied' Else 'Not Occupied' End As Status From CTE_Changepoints
Union All Select 3, ConfName, ChangeTime, 'Summary' As Summary, 
	Case When Booked = 1 Then 'Booked' Else 'Not Booked' End 
	+ ' and ' + Case When Occupied = 1 Then 'Occupied' Else 'Not Occupied' End As Status From CTE_Changepoints 
Where Booked Is Not Null Or Occupied Is Not Null
)
Select
	ConfName,
	ChangeTime As StartDateTime,
	IsNull(Summary, Status) As SummaryStatus,
	Status,
	Lead(ChangeTime, 3) Over (Partition By ConfName Order By ChangeTime) As EndDateTime
From CTE_AddLabels
Where ChangeTime = Any(Select StartDateTime From tblConf)
order by ConfName, ChangeTime, Ordinal

Although I'm still not entirely sure why you don't have summary rows for some of the times you're showing

1 Like
5

Hi Andy,

I ran the query but this is still not quite close to what I want. There are few results missing.

As mentioned, the Summary rows only show up for the following Statuses:
'Booked and Occupied'
'Booked and NOT Occupied'
'NOT Booked and Occupied'
'NOT Booked and NOT Occupied'

Overall, based on the tblConf, the results that is expected for each status from StartDateTime and EndDateTime (within a 24 hour span) with no other status overlapping for the following statuses:

'Booked and Occupied'
'Booked and NOT Occupied'
'NOT Booked and Occupied'
'NOT Booked and NOT Occupied'
'Booked'
'Occupied'
'NOT Booked'
'NOT Occupied'

And if any of the current 'Booked' or 'Occupied' status duration surpasses the current day then it should break the pending Duration for that statuses till next available hour/s or day/s

6

This answer is a bit close, but not close enough to cover all the scenarios.

Thank you Andy for your time and effort!

7

hi satisd9x

please define

'Booked and Occupied'
'Booked and NOT Occupied'
'NOT Booked and Occupied'
'NOT Booked and NOT Occupied'
'Booked'
'Occupied'
'NOT Booked'
'NOT Occupied'

thanks

so that i can work on it :slight_smile:

8

Apologies for the delay in replying harishgg1..

The names itself defines that a room is either:
'Booked and Occupied' OR
'Booked and NOT Occupied' OR
'NOT Booked and Occupied' OR
'NOT Booked and NOT Occupied' OR
'Booked' OR
'Occupied' OR
'NOT Booked' OR
'NOT Occupied'