Grouping Statuses and Dates

Transact-SQL
1

Hello,

I'm wondering if anyone might be able to help me. I am trying to group a table similar to the one below. I have seen similar problems and previously had this solved when the table had a VisitNumber column, but this has been taken away.

My Sample Data is below:

CREATE TABLE #tb
(
    ClientID int NOT NULL
    ,BuildingID int NOT NULL
    ,RoomStart date NOT NULL
    ,RoomEnd date 
    ,RoomStatus varchar(20)
);

    INSERT INTO #tb
    VALUES 
     (1001,    11,    '20120101',    '20120201',    'xfer')
    ,(1001,    12,    '20120201',    '20120301',    'xfer')
    ,(1001,    11,    '20120301',    '20120401',    'checkout')
    ,(1002,    11,    '20120101',    '20120501',    'xfer')
    ,(1002,    13,    '20120501',    '20120601',    'checkout')
    ,(1003,    13,    '20130211',    '20130218',    'checkout')
    ,(1003,    11,    '20140601',    '20140620',    'xfer')
    ,(1003,    13,    '20140620',    '20140701',    'checkout')
    ,(1003,    11,    '20151220',    NULL,            NULL)
    ,(1004,    13,    '20151220',    '20151227',    'xfer')
    ,(1004,    11,    '20151227',    NULL,            NULL);

What I want to do is group each visit with a start and end date (which may be null if the client is still in the room), so for example the first 2 visits would look like:

ClientID    StartBuilding    EndBuilding        StartDate     EndDate
1001             11              11            2012-01-01    2012-04-01
1002             11              13            2012-01-01    2012-06-01

Does anyone have any advice?

Thanks in advance!

2 
;

WITH cte
AS (
	SELECT *,
		ra = row_number() OVER (
			PARTITION BY ClientID ORDER BY RoomStart
			),
		rd = row_number() OVER (
			PARTITION BY ClientID ORDER BY RoomEnd DESC
			)
	FROM #tb
	WHERE RoomEnd IS NOT NULL
	)
SELECT ClientID,
	StartBuilding = max(CASE 
			WHEN ra = 1
				THEN BuildingID
			END),
	EndBuilding = max(CASE 
			WHEN rd = 1
				THEN BuildingID
			END),
	StartDate = max(CASE 
			WHEN ra = 1
				THEN RoomStart
			END),
	EndDate = max(CASE 
			WHEN rd = 1
				THEN RoomEnd
			END)
FROM cte
WHERE ra = 1
	OR rd = 1
GROUP BY ClientID
3

Thanks for your response khtan. This is close to what I am looking to do, however maybe I should have given more examples of output. Your code gives the beginning and end buildings and times for each ClientID regardless of xfers and checkout.

A visit ends when there is a checkout, but the checkout can occur on the first row, or after any number of transfers. The row can also have null ending values if the visit is still 'open'.

What I need to do is get the start and end for each visit. The results from my sample data would look like this:

ClientID     StartBuilding     Endbuilding    StartDate     EndDate
1001               11              11         2012-01-01   2012-04-01
1002               11              13         2012-01-01   2012-06-01
1003               11              13         2013-02-11   2013-02-18
1003               11              13         2014-06-01   2014-07-01
1003               11              NULL       2015-12-20   NULL
1004               13              NULL       2015-12-20   NULL

I'm beginning to wonder if this is even possible without the 'VisitNumber' column that used to increment each time a Client had a visit.

Thanks again!

4 
;

WITH visits
AS (
	SELECT ClientID,
		BuildingID,
		RoomStart,
		RoomEnd,
		RoomStatus,
		seq = row_number() OVER (
			PARTITION BY ClientID ORDER BY RoomStart
			)
	FROM #tb
	),
visit_last
AS (
	SELECT ClientID,
		BuildingID,
		RoomStart,
		RoomEnd,
		RoomStatus,
		seq,
		visit_no = dense_rank() OVER (
			PARTITION BY ClientID ORDER BY RoomStart
			)
	FROM visits
	WHERE RoomStatus IS NULL
		OR RoomStatus = 'checkout'
	),
visits_2
AS (
	SELECT v.ClientID,
		v.BuildingID,
		v.RoomStart,
		v.RoomEnd,
		v.RoomStatus,
		seq = row_number() OVER (
			PARTITION BY v.ClientID,
			l.visit_no ORDER BY v.seq
			),
		l.visit_no,
		LastRoom = l.RoomEnd,
		LastBuildingID = l.BuildingID
	FROM visits v
	OUTER APPLY (
		SELECT TOP 1 x.BuildingID,
			x.RoomEnd,
			x.visit_no
		FROM visit_last x
		WHERE x.ClientID = v.ClientID
			AND x.seq >= v.seq
		ORDER BY x.visit_no
		) l
	)
SELECT ClientID,
	StartBuilding = max(CASE 
			WHEN seq = 1
				THEN BuildingID
			END),
	EndBuilding = max(LastBuildingID),
	StartRoom = max(CASE 
			WHEN seq = 1
				THEN RoomStart
			END),
	EndRoom = max(LastRoom)
FROM visits_2
GROUP BY ClientID,
	visit_no
ORDER BY ClientID,
	visit_no
5

This works. Thank you very much for your help khtan! :slight_smile: