ROW_NUMBER() function with a difference

Question

ROW_NUMBER() function with a difference

richardmgreen1

SSChampion

Points: 10352
More actions
February 16, 2016 at 6:10 am

#304678

Hi all
This one has got me stumped.....
I'm trying to track patient movements around our hospital at both bed and ward level.
I've attached the raw data (anonymised) in a spreadsheet with the expected outputs (in the shaded column.
I've got the bed sequence nailed but I'm struggling with the sequence numbers for the wards.
I've tried various combinations of the ROW_NUMBER and COUNT functions (both with Partition and Order By clauses as appropriate) but I'm getting nowhere.
Any help gratefully received.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

My suggestion is something that should be tested really carefully. It involves a technique called Quirky Update which is very fast but unreliable when not done properly. All the information you need to know can be found in here: http://www.sqlservercentral.com/articles/T-SQL/68467/

I'm leaving an example with sample data as you should have posted it. Don't deploy this code if you're not sure how it works. You'll be the one in charge to fix it.

CREATE TABLE PatientMoves(

pkLocalPatientLocalID char(2),

pkSpellLocalID char(3),

WardLocation varchar(10),

WardSequence int,

RoomName varchar(20),

Bed int,

BedSequence int,

StartDateTime datetime,

FinishDateTime datetime,

WardSequenceRequired int

);

INSERT INTO PatientMoves

VALUES

('P1', 'AC1', 'B6', 1, 'B6DBY1', 3, 1, '20120612 08:38:00', '20120612 10:57:00', 1),

('P1', 'AC1', 'THEATRE', 1, 'SURGTHEATR', 16, 2, '20120612 10:57:00', '20120612 14:09:00', 2),

('P1', 'AC1', 'B6', 2, 'B6DBY1', 1, 3, '20120612 14:09:00', '20120612 14:10:00', 3),

('P2', 'AC2', 'TAU', 1, 'TAU', 5, 1, '20120618 10:17:00', '20120618 15:09:00', 1),

('P2', 'AC2', 'THEATRE', 1, 'SURGTHEATR', 14, 2, '20120618 15:09:00', '20120618 16:21:00', 2),

('P2', 'AC2', 'THEATRE', 2, 'DSC WARD', 2, 3, '20120618 16:21:00', '20120618 19:17:00', 2);

CREATE TABLE #Sequences(

pkLocalPatientLocalID char(2),

pkSpellLocalID char(3),

WardLocation varchar(10),

WardSequence int,

RoomName varchar(20),

Bed int,

BedSequence int,

StartDateTime datetime,

FinishDateTime datetime

);

CREATE CLUSTERED INDEX CIX_Sequences ON #Sequences(pkLocalPatientLocalID, StartDateTime);

INSERT INTO #Sequences(

pkLocalPatientLocalID ,

pkSpellLocalID ,

WardLocation ,

RoomName ,

Bed ,

StartDateTime ,

FinishDateTime

)

SELECT pkLocalPatientLocalID,

pkSpellLocalID,

WardLocation,

RoomName,

Bed,

StartDateTime,

FinishDateTime

FROM PatientMoves;

DECLARE @Patient char(2),

@WardLocation varchar(10),

@Bed int,

@BedSequence int,

@WardSequence int;

SELECT @BedSequence = 0,

@WardSequence = 0,

@Patient = '';

UPDATE S WITH(TABLOCKX) SET

@WardSequence = WardSequence = CASE WHEN pkLocalPatientLocalID <> @Patient THEN 1

WHEN WardLocation <> @WardLocation THEN @WardSequence + 1

ELSE @WardSequence END,

@BedSequence = BedSequence = CASE WHEN pkLocalPatientLocalID <> @Patient THEN 1

WHEN Bed <> @Bed THEN @BedSequence + 1

ELSE @BedSequence END,

@Patient = pkLocalPatientLocalID,

@WardLocation = WardLocation,

@Bed = Bed

FROM #Sequences S

OPTION (MAXDOP 1);

SELECT *

FROM #Sequences;

DROP TABLE #Sequences

GO

DROP TABLE PatientMoves;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The dual-rownumber method is also promising:

DROP TABLE #Sample

CREATE TABLE #Sample (pkLocalPatientLocalID VARCHAR(2),pkSpellLocalID VARCHAR(3),WardLocation VARCHAR(20),WardSequence INT,RoomName VARCHAR(20),Bed INT,BedSequence INT,StartDateTime DATETIME,FinishDateTime DATETIME,[WardSequence Required] INT)

INSERT INTO #Sample

(pkLocalPatientLocalID,pkSpellLocalID,WardLocation, WardSequence,RoomName, Bed, BedSequence, StartDateTime, FinishDateTime, [WardSequence Required]) VALUES

('P1','AC1','B6',1,'B6DBY1',3,1,CONVERT(DATETIME,'12/06/2012 08:38:00',103), CONVERT(DATETIME,'12/06/2012 10:57:00',103),1),

('P1','AC1','THEATRE',1,'SURGTHEATR',16,2,CONVERT(DATETIME,'12/06/2012 10:57:00',103), CONVERT(DATETIME,'12/06/2012 12:09:00',103),2),

('P1','AC1','THEATRE',1,'SURGTHEATR',16,2,CONVERT(DATETIME,'12/06/2012 12:57:00',103), CONVERT(DATETIME,'12/06/2012 14:09:00',103),2),

('P1','AC1','B6',2,'B6DBY1',1,3,CONVERT(DATETIME,'12/06/2012 14:09:00',103), CONVERT(DATETIME,'12/06/2012 14:10:00',103),3),

('P2','AC2','TAU',1,'TAU',5,1,CONVERT(DATETIME,'18/06/2012 10:17:00',103), CONVERT(DATETIME,'18/06/2012 15:09:00',103),1),

('P2','AC2','THEATRE',1,'SURGTHEATR',14,2,CONVERT(DATETIME,'18/06/2012 15:09:00',103), CONVERT(DATETIME,'18/06/2012 16:21:00',103),2),

('P2','AC2','THEATRE',2,'DSC WARD',2,3,CONVERT(DATETIME,'18/06/2012 16:21:00',103), CONVERT(DATETIME,'18/06/2012 19:17:00',103),2),

('P2','AC2','THEATRE',2,'DSC WARD',2,3,CONVERT(DATETIME,'18/07/2012 16:21:00',103), CONVERT(DATETIME,'18/07/2012 19:17:00',103),2)

SELECT *, dr = DENSE_RANK() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY grp desc, WardLocation desc)

FROM (

SELECT *, grp = rn2-rn1

FROM (

SELECT *,

rn1 = ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY StartDateTime),

rn2 = ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID, WardLocation ORDER BY StartDateTime)

FROM #Sample

) d

) e

ORDER BY pkLocalPatientLocalID, StartDateTime

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

richardmgreen1 SSChampion Points: 10352 More actions · Answer 3

Thanks both

I'm testing both and they are very close to what I need.

Chris - yours is closest to what I need but I have found a bit of an oddity.

When I run for a specific patient with the following moves:-

DROP TABLE #Sample;

CREATE TABLE #Sample

(

pkLocalPatientLocalID VARCHAR(2)

,pkSpellLocalID VARCHAR(3)

,WardLocation VARCHAR(20)

,RoomName VARCHAR(20)

,Bed INT

,BedSequence INT

,StartDateTime DATETIME

,FinishDateTime DATETIME

,[WardSequence Required] INT

);

INSERT INTO #Sample

(

pkLocalPatientLocalID

,pkSpellLocalID

,WardLocation

,RoomName

,Bed

,BedSequence

,StartDateTime

,FinishDateTime

,[WardSequence Required]

)

VALUES

(

'P1'

,'AC1'

,'B6'

,'B6DBY1'

,3

,1

,CONVERT( DATETIME,'15/02/2016 07:19:00',103)

,CONVERT( DATETIME,'15/02/2016 07:31:00',103)

,1

),

(

'P1'

,'AC1'

,'B6'

,'B6DBY2'

,16

,2

,CONVERT( DATETIME,'15/02/2016 07:31:00',103)

,CONVERT( DATETIME,'15/02/2016 10:24:00',103)

,2

),

(

'P1'

,'AC1'

,'B6'

,'B6OPTHAL'

,16

,2

,CONVERT( DATETIME,'15/02/2016 10:24:00',103)

,CONVERT( DATETIME,'15/02/2016 11:58:00',103)

,2

),

(

'P1'

,'AC1'

,'THEATRE'

,'SURGTHEATR'

,1

,3

,CONVERT( DATETIME,'15/02/2016 11:58:00',103)

,CONVERT( DATETIME,'15/02/2016 12:01:00',103)

,3

);

SELECT

*

,dr=DENSE_RANK() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY grp DESC

,WardLocation DESC)

FROM

(

SELECT

*

,grp=rn2 - rn1

FROM

(

SELECT

*

,rn1=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY

StartDateTime)

,rn2=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID

,WardLocation ORDER BY StartDateTime)

FROM

#Sample

) AS d

) AS e

ORDER BY

pkLocalPatientLocalID

,StartDateTime;

I get almost the perfect result.

If I order it by the bed sequence field, I get the following in the dr column for the ranking:-

1

3

2

when it should be :-

1 (B6)

2 (Theatre)

3 (B6)

It looks like it's picking up the ward changes, just not quite in the right order. I think it's because this particular patient has gone from B6 (the second item) to Theatre and then back to B6 but I'm not entirely sure how to sort it out.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

richardmgreen1 (2/17/2016)
Thanks both
I'm testing both and they are very close to what I need.
Chris - yours is closest to what I need but I have found a bit of an oddity.
When I run for a specific patient with the following moves:-
DROP TABLE #Sample;
CREATE TABLE #Sample
(
pkLocalPatientLocalID VARCHAR(2)
,pkSpellLocalID VARCHAR(3)
,WardLocation VARCHAR(20)
,RoomName VARCHAR(20)
,Bed INT
,BedSequence INT
,StartDateTime DATETIME
,FinishDateTime DATETIME
,[WardSequence Required] INT
);
INSERT INTO #Sample
(
pkLocalPatientLocalID
,pkSpellLocalID
,WardLocation
,RoomName
,Bed
,BedSequence
,StartDateTime
,FinishDateTime
,[WardSequence Required]
)
VALUES
(
'P1'
,'AC1'
,'B6'
,'B6DBY1'
,3
,1
,CONVERT( DATETIME,'15/02/2016 07:19:00',103)
,CONVERT( DATETIME,'15/02/2016 07:31:00',103)
,1
),
(
'P1'
,'AC1'
,'B6'
,'B6DBY2'
,16
,2
,CONVERT( DATETIME,'15/02/2016 07:31:00',103)
,CONVERT( DATETIME,'15/02/2016 10:24:00',103)
,2
),
(
'P1'
,'AC1'
,'B6'
,'B6OPTHAL'
,16
,2
,CONVERT( DATETIME,'15/02/2016 10:24:00',103)
,CONVERT( DATETIME,'15/02/2016 11:58:00',103)
,2
),
(
'P1'
,'AC1'
,'THEATRE'
,'SURGTHEATR'
,1
,3
,CONVERT( DATETIME,'15/02/2016 11:58:00',103)
,CONVERT( DATETIME,'15/02/2016 12:01:00',103)
,3
);
SELECT
*
,dr=DENSE_RANK() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY grp DESC
,WardLocation DESC)
FROM
(
SELECT
*
,grp=rn2 - rn1
FROM
(
SELECT
*
,rn1=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY
StartDateTime)
,rn2=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID
,WardLocation ORDER BY StartDateTime)
FROM
#Sample
) AS d
) AS e
ORDER BY
pkLocalPatientLocalID
,StartDateTime;
I get almost the perfect result.
If I order it by the bed sequence field, I get the following in the dr column for the ranking:-
1
1
3
2
when it should be :-
1 (B6)
1 (B6)
2 (Theatre)
3 (B6)
It looks like it's picking up the ward changes, just not quite in the right order. I think it's because this particular patient has gone from B6 (the second item) to Theatre and then back to B6 but I'm not entirely sure how to sort it out.

I'm sorry, I don't follow - the moves (by startdatetime) are B6 B6 B6 THEATRE which should be 1,1,1,2?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

A couple of tweaks to Chris's query should do it

SELECT

*

,dr=DENSE_RANK() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY grp)

FROM

(

SELECT

*

,grp=MIN(StartDateTime) OVER(PARTITION BY pkLocalPatientLocalID,WardLocation,rn2 - rn1)

FROM

(

SELECT

*

,rn1=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY

StartDateTime)

,rn2=ROW_NUMBER() OVER(PARTITION BY pkLocalPatientLocalID

,WardLocation ORDER BY StartDateTime)

FROM

#Sample

) AS d

) AS e

ORDER BY

pkLocalPatientLocalID

,StartDateTime;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Mark Cowne (2/17/2016)
A couple of tweaks to Chris's query should do it

Funny - the first time I saw this technique used was in one of your posts, Mark.

Thanks for the fix.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

I'm just curious to know what's wrong with the solution that I posted.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

richardmgreen1 SSChampion Points: 10352 More actions · Answer 8

Hi Luis

You solution seemed to run into the same issue as Chris's (a slightly out of sequence ward location).

I'm just testing out the adjusted version from Mark and it looks promising so far......

I'll keep you posted.