Grouping Dates and Statuses - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Dates and Statuses

Author

Topic

ckimus
Starting Member

4 Posts

Posted - 2015-01-30 : 20:17:20

Hello, I wonder if anyone here can help me. I have a table that currently looks something like below.

CLID VisID VisitStrt VisitEnd. Bldg RoomStart RoomStatus
-----------------------------------------------------
1011 10261 1/01/2015 1/07/2015 1555 1/01/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/02/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/03/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/04/2015 Away
1011 10261 1/01/2015 1/07/2015 1555 1/05/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/06/2015 Reserved
1011 10261 1/01/2015 1/07/2015 1555 1/07/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/15/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/16/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/17/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/18/2015 Away
1011 10265 1/15/2015 1/20/2015 1777 1/19/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/20/2015 Occupied

I have been trying to group this so that each Status has one RoomStart and one RoomEnd, getting rid rows in between. The result would look like this:

CLID VisID VisitStrt VisitEnd. Bldg RoomStart RoomEnd RoomStatus
-----------------------------------------------------------------
1011 10261 1/01/2015 1/07/2015 1555 1/01/2015 1/03/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/04/2015 1/04/2015 Away
1011 10261 1/01/2015 1/07/2015 1555 1/05/2015 1/05/2015 Occupied
1011 10261 1/01/2015 1/07/2015 1555 1/06/2015 1/06/2015 Reserved
1011 10261 1/01/2015 1/07/2015 1555 1/07/2015 1/07/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/15/2015 1/17/2015 Occupied
1011 10265 1/15/2015 1/20/2015 1777 1/18/2015 1/18/2015 Away
1011 10265 1/15/2015 1/20/2015 1777 1/19/2015 1/20/2015 Occupied

Any ideas? I've found a solution using partion and a running total but this table is huge and the query takes far too long to run.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-31 : 07:45:07

Do you realize that your desired solution has overlapping dates? e.g. bldg/room 1555/1011 is both away and occupied on jan 5, and both occupied and reserved on the 6th. Is that what you want? Why? (doesn't make sense to me)

ckimus
Starting Member

4 Posts

Posted - 2015-01-31 : 12:10:41

Ahh, well spotted. I typed this out by hand and didn't realize. I have edited it so they don't overlap.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-31 : 12:23:58

Try interval packing with the difference in order method:


-- *** Test Data ***
CREATE TABLE #t
(
	CLID int NOT NULL
	,VisID int NOT NULL
	,RoomStart date NOT NULL
	,RoomStatus varchar(20) NOT NULL
);
INSERT INTO #t
VALUES (1011, 10261, '20150101', 'Occupied') 
	,(1011, 10261, '20150102', 'Occupied')
	,(1011, 10261, '20150103', 'Occupied')
	,(1011, 10261, '20150104', 'Away')
	,(1011, 10261, '20150105', 'Occupied')
	,(1011, 10261, '20150106', 'Reserved')
	,(1011, 10261, '20150107', 'Occupied')
	,(1011, 10265, '20150115', 'Occupied')
	,(1011, 10265, '20150116', 'Occupied')
	,(1011, 10265, '20150117', 'Occupied')
	,(1011, 10265, '20150118', 'Away')
	,(1011, 10265, '20150119', 'Occupied')
	,(1011, 10265, '20150120', 'Occupied');
-- *** End Test Data ***

WITH Grps
AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY CLID, VisID ORDER BY RoomStart)
			- ROW_NUMBER() OVER (PARTITION BY CLID, VisID, RoomStatus ORDER BY RoomStart) AS Grp

	FROM #t
)
,Ranges
AS
(
	SELECT CLID, VisID, RoomStatus, Grp
		,MIN(RoomStart) AS RoomStart
		,MAX(RoomStart) AS RoomEnd
	FROM Grps
	GROUP BY CLID, VisID, RoomStatus, Grp
)
SELECT CLID, VisID, RoomStart, RoomEnd, RoomStatus
FROM Ranges
ORDER BY RoomStart;

ckimus
Starting Member

4 Posts

Posted - 2015-01-31 : 13:09:33

Thanks for the reply, that was a great place to start. However, I found that if there was a new visit a few days after the last one with the same status, it got grouped with that previous visit. If the next visit looked like this:

(1011, 10265, '20150123', '20150127', '20150123', 'Occupied')
(1011, 10265, '20150123', '20150127', '20150124', 'Occupied')
(1011, 10265, '20150123', '20150127', '20150124', 'Occupied')
(1011, 10265, '20150123', '20150127', '20150126', 'Occupied')
(1011, 10265, '20150123', '20150127', '20150127', 'Occupied');

I added the VisitStart and VisitEnd to the query and it seems to work perfectly now. Is there any reason why it shouldn't?

WITH Grps
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY CLID, VisID ORDER BY RoomStart)
- ROW_NUMBER() OVER (PARTITION BY CLID, VisID, RoomStatus ORDER BY RoomStart) AS Grp

FROM t
)
,Ranges
AS
(
SELECT CLID, VisitStart, VisitEnd, VisID, RoomStatus, Grp
,MIN(RoomStart) AS RoomStart
,MAX(RoomStart) AS RoomEnd
FROM Grps
GROUP BY CLID, VisitStart, VisitEnd, VisID, RoomStatus, Grp
)
SELECT CLID, VisID, VisitStart, VisitEnd,RoomStart, RoomEnd, RoomStatus
FROM Ranges
ORDER BY RoomStart;

ckimus
Starting Member

4 Posts

Posted - 2015-01-31 : 18:05:11

Thanks Ifor, your solution did it. I just included the VisitStart and VisitEnd to the group and it Works perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources