| Author |
 Topic  |
|
|
stamford
Starting Member
United Kingdom
14 Posts |
 Posted - 06/14/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 06/14/2012 : 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
United Kingdom
14 Posts |
Posted - 06/14/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 06/14/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 06/14/2012 : 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. |
Edited by - Transact Charlie on 06/14/2012 12:26:44 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 06/14/2012 : 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
United Kingdom
14 Posts |
Posted - 06/14/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/14/2012 : 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
United Kingdom
14 Posts |
Posted - 06/17/2012 : 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
India
47069 Posts |
Posted - 06/18/2012 : 00:36:49
|
why is 48 & 102 not included?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
stamford
Starting Member
United Kingdom
14 Posts |
Posted - 06/18/2012 : 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
|
|
|
| |
Topic |
|
RANK() OVER PARTITION BY EQUIVALENT in SQL 2000?
