RANK() OVER PARTITION BY EQUIVALENT in SQL 2000? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

RANK() OVER PARTITION BY EQUIVALENT in SQL 2000?

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2012-06-14 : 12:02:33

Greetings,

I have successfully used the above script in SQL 2005 but now I have to come up with an equivalent script to use in SQL 2000 that will add a ranking to each block of PATIENT_ID values based on their appointment dates. So, as below, TABLE A will look like TABLE B.
The RANK column will hopefully give me the flexibility to identify first appointment dates and subsequent appointment dates for each patient. Any help is much appreciated. Thank you.


TABLE A
-------

PATIENT_ID   	CONTACT_DATE
20		2003-01-03
21		2003-01-03
22		2002-10-22
22		2004-11-24
23		2002-12-27
24		2002-11-08
25		2002-12-27
27		2002-12-27
28		2003-01-03
30		2003-01-08
31		2003-04-07
31		2003-06-02
31		2003-08-18
31		2003-08-29
32		2003-01-08
34		2003-01-10
36		1998-10-08
36		2002-11-18
36		2003-01-13
36		2003-03-05



TABLE B
-------

PATIENT_ID   	CONTACT_DATE	RANK
20		2003-01-03	1
21		2003-01-03	1
22		2002-10-22	1
22		2004-11-24	2
23		2002-12-27	1
24		2002-11-08	1
25		2002-12-27	1
27		2002-12-27	1
28		2003-01-03	1
30		2003-01-08	1
31		2003-04-07	1
31		2003-06-02	2
31		2003-08-18	3
31		2003-08-29	4
32		2003-01-08	1
34		2003-01-10	1
36		1998-10-08	1
36		2002-11-18	2
36		2003-01-13	3
36		2003-03-05	4

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-14 : 12:12:35

Does that table have a unique key?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

stamford
Starting Member

47 Posts

Posted - 2012-06-14 : 12:19:53

quote:
Originally posted by Transact Charlie

Does that table have a unique key?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

yes it does - it is CONTACT_ID - it is a self incrementing integer field 1, 2, 3 ..... etc

thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-14 : 12:23:38

Ah cool...

regardless. If the combination of PATIENT_ID and CONTACT_DATE is unique then you could do something like this:



DECLARE @foo TABLE (
	[PatientID] INT
	, [ContactDate] DATETIME
	)
INSERT @foo
      SELECT 20, '2003-01-03'
UNION SELECT 21, '2003-01-03'
UNION SELECT 22, '2002-10-22'
UNION SELECT 22, '2004-11-24'
UNION SELECT 23, '2002-12-27'
UNION SELECT 24, '2002-11-08'
UNION SELECT 25, '2002-12-27'
UNION SELECT 27, '2002-12-27'
UNION SELECT 28, '2003-01-03'
UNION SELECT 30, '2003-01-08'
UNION SELECT 31, '2003-04-07'
UNION SELECT 31, '2003-06-02'
UNION SELECT 31, '2003-08-18'
UNION SELECT 31, '2003-08-29'
UNION SELECT 32, '2003-01-08'
UNION SELECT 34, '2003-01-10'
UNION SELECT 36, '1998-10-08'
UNION SELECT 36, '2002-11-18'
UNION SELECT 36, '2003-01-13'
UNION SELECT 36, '2003-03-05'

SELECT * FROM @foo


DECLARE @output TABLE (
	[PatientID] INT
	, [ContactDate] DATETIME
	, [rank] TINYINT
	)

DECLARE @rowCount TINYINT SET @rowCount = 1
DECLARE @rows INT SET @rows = 1

WHILE @rows > 0
BEGIN
	INSERT @output ([PatientID], [ContactDate], [rank])
	SELECT [PatientID], MIN([ContactDate]), @rowCount
	FROM
		(
			SELECT [PatientID], [ContactDate] FROM @foo AS f
			WHERE NOT EXISTS ( SELECT 1 FROM @output AS o WHERE o.[PatientID] = f.[PatientID] AND o.[ContactDate] = f.[ContactDate])
		)
		AS st
	GROUP BY
		st.[PatientID]
		
	SET @rows = @@ROWCOUNT
	
	SET @rowCount = @rowCount + 1
END

SELECT * FROM @output ORDER BY [PatientID], [rank]

It's pretty messy -- I think others may have a better idea. I don't have a 2000 db any more but I do remember this kind of thing always being horrible,

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-14 : 12:26:26

probably better is to ORDER the set by patientID and ContactDate then iterate over it in your front end layer and assign the rank there.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-14 : 12:27:59

ah they days before window functions and CROSS APPLY...........

How the hell did we do ANYTHING back then?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

stamford
Starting Member

47 Posts

Posted - 2012-06-14 : 13:18:25

quote:

It's pretty messy -- I think others may have a better idea. I don't have a 2000 db any more but I do remember this kind of thing always being horrible,

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thanks for your help thus far. I think we're part way there....

Here is what I tried which gives just the ranking 1 values i.e. just the first appointment dates for each patient. Have I written this correctly? Using the WHILE clause puts the script into an infinite loop so my results exclude its use.


CREATE TABLE temp
(PALLIATIVE_ID INT
, L_CONTACT_DATE DATETIME
, ranking INT)

DECLARE @rowCount INT, @rows INT 
SET @rowCount = 1
SET @rows = 1

--WHILE @rows > 0
BEGIN
INSERT temp
(PALLIATIVE_ID
, L_CONTACT_DATE
, ranking)
SELECT PALLIATIVE_ID, MIN(L_CONTACT_DATE), @rowCount
FROM
(SELECT PALLIATIVE_ID, L_CONTACT_DATE FROM tblPALLIATIVE_CONTACT AS f
WHERE NOT EXISTS
(SELECT 1 FROM temp AS o WHERE o.PALLIATIVE_ID = f.PALLIATIVE_ID AND
o.L_CONTACT_DATE = f.L_CONTACT_DATE))
AS st
GROUP BY st.PALLIATIVE_ID
SET @rows = @@ROWCOUNT
SET @rowCount = @rowCount + 1
END

SELECT * FROM temp
WHERE L_CONTACT_DATE IS NOT NULL
ORDER BY PALLIATIVE_ID, L_CONTACT_DATE

DROP TABLE temp



PALLIATIVE_ID   L_CONTACT_DATE              ranking
2	        2009-07-08 00:00:00.000	    1
5	        2002-01-09 00:00:00.000	    1
9	        2002-11-12 00:00:00.000	    1
10	        2002-10-15 00:00:00.000	    1
15	        2002-12-30 00:00:00.000	    1
20	        2003-01-03 00:00:00.000	    1
21	        2003-01-03 00:00:00.000     1
22	        2002-10-22 00:00:00.000     1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 13:38:42

select PatientID, ContactDate, rank = (select COUNT(*) from @foo f2 where f2.PatientID = f.PatientID and f2.ContactDate <= f.ContactDate)
from @foo f
order by PatientID, ContactDate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stamford
Starting Member

47 Posts

Posted - 2012-06-17 : 19:01:16

quote:
Originally posted by nigelrivett

select PatientID, ContactDate, rank = (select COUNT(*) from @foo f2 where f2.PatientID = f.PatientID and f2.ContactDate <= f.ContactDate)
from @foo f
order by PatientID, ContactDate

Following on from the above I now need to highlight the CONTACT_ID references in the following table which represent follow up appointments for patients which had previously been discharged.
So for instance in the following table CONTACT_ID values for patients who had previously been discharged but are now attending follow up appointents are 46, 54 and 60. Is there an easy way to highlight these three values in a new table in SQL 2000?


CONTACT_ID	PATIENT_ID	REGISTRATION_DATE               CONTACT_DATE		        DISCHARGE_DATE
1		12345		01/01/2003 00:00		03/01/2003 00:00		04/01/2003 00:00
2		99999		31/12/2002 00:00		03/01/2003 00:00		05/01/2003 00:00
7		24680		20/10/2002 00:00		22/10/2002 00:00		24/10/2002 00:00
5		24680		20/10/2002 00:00		22/10/2002 00:00		24/10/2002 00:00
9		24681		20/12/2002 00:00		27/12/2002 00:00		05/01/2003 00:00
15		24682		30/10/2002 00:00		08/11/2002 00:00		04/01/2003 00:00
16		24683		20/12/2002 00:00		27/12/2002 00:00		10/01/2003 00:00
46		24680		20/10/2002 00:00		27/12/2002 00:00		29/12/2002 00:00
48		24681		20/12/2002 00:00		03/01/2003 00:00		08/01/2003 00:00
54		12345		01/01/2003 00:00		08/01/2003 00:00		NULL
83		99999		01/01/2003 00:00		08/01/2003 00:00		10/01/2003 00:00
80		99999		20/08/2003 00:00		29/08/2003 00:00		NULL
79		99999		30/07/2003 00:00		18/08/2003 00:00		NULL
61		99999		20/04/2003 00:00		02/06/2003 00:00		NULL
60		99999		31/12/2002 00:00		07/04/2003 00:00		NULL
100		78962		08/01/2003 00:00		10/01/2003 00:00		01/02/2003 00:00
345		54327		20/02/2003 00:00		05/03/2003 00:00		10/03/2003 00:00
234		54327		08/01/2003 00:00		13/01/2003 00:00		01/02/2003 00:00
102		54327		20/10/2002 00:00		18/11/2002 00:00		NULL
101		54327		01/10/1998 00:00		08/10/1998 00:00		20/10/1998 00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 00:36:49

why is 48 & 102 not included?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stamford
Starting Member

47 Posts

Posted - 2012-06-18 : 04:27:03

quote:
Originally posted by visakh16

why is 48 & 102 not included?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Good point. Sorry I omitted a column purposely but now realise it needs to be there. The column I left out was SERIES_ID which is a reference for a series of appointments for a patient. Therefore the SERIES_ID needs to be different to indicate that an appointment is a follow up one. Therefore 48 should be included because it has a different SERIES_ID to the patient's earlier visit, but 102 should not because it is part of the same series as the rest of the patient's appointments. If that makes sense! Here is the revised table. Thank you.


CONTACT_ID	PATIENT_ID	SERIES_ID   REGISTRATION_DATE	CONTACT_DATE	        DISCHARGE_DATE
1	        12345	        20	    01/01/2003 00:00	03/01/2003 00:00	04/01/2003 00:00
2	        99999	        21	    31/12/2002 00:00	03/01/2003 00:00	05/01/2003 00:00
7	        24680	        22	    20/10/2002 00:00	22/10/2002 00:00	24/10/2002 00:00
5	        24680	        22	    20/10/2002 00:00	22/10/2002 00:00	24/10/2002 00:00
9	        24681	        23	    20/12/2002 00:00	27/12/2002 00:00	05/01/2003 00:00
15	        24682	        24	    30/10/2002 00:00	08/11/2002 00:00	04/01/2003 00:00
16	        24683	        25	    20/12/2002 00:00	27/12/2002 00:00	10/01/2003 00:00
46	        24680	        27	    20/10/2002 00:00	27/12/2002 00:00	NULL
48	        24681	        28	    20/12/2002 00:00	03/01/2003 00:00	08/01/2003 00:00
54	        12345	        30	    01/01/2003 00:00	08/01/2003 00:00	NULL
83	        99999	        31	    01/01/2003 00:00	08/01/2003 00:00	10/01/2003 00:00
80	        99999	        31	    20/08/2003 00:00	29/08/2003 00:00	NULL
79	        99999	        31	    30/07/2003 00:00	18/08/2003 00:00	NULL
61	        99999	        31	    20/04/2003 00:00	02/06/2003 00:00	NULL
60	        99999	        31	    31/12/2002 00:00	07/04/2003 00:00	NULL
100	        78962	        34	    08/01/2003 00:00	10/01/2003 00:00	01/02/2003 00:00
345	        54327	        36	    20/02/2003 00:00	05/03/2003 00:00	10/03/2003 00:00
234	        54327	        36	    08/01/2003 00:00	13/01/2003 00:00	01/02/2003 00:00
102	        54327	        36	    20/10/2002 00:00	18/11/2002 00:00	NULL
101	        54327	        36	    01/10/1998 00:00	08/10/1998 00:00	20/10/1998 00:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources