looping thru records to find related records

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

General SQL Server Forums

New to SQL Server Programming

looping thru records to find related records

Author

Topic

yoyo_master
Starting Member

4 Posts

Posted - 2007-10-31 : 00:54:41

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data

Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17

If anyone could help it would be much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 02:07:19

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

yoyo_master
Starting Member

4 Posts

Posted - 2007-10-31 : 17:24:53

I need to know a patients admission movement through the table,What I need help on is finding out is when a patient is admitted and discharged from several hospitals in admission sequence and there date gap between discharge from one hospital and admission to another hospital of less than one day then that becomes episode one and when theirs a break of greater than one day that becomes episode two and so on.

I hope I made it a bit more clearer, Any help would be apprecieted.

Thanks

Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 22:13:57

Can you show us how the expected result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

yoyo_master
Starting Member

4 Posts

Posted - 2007-10-31 : 22:42:43

Hi KH,

here is what im after the output is TAB delimited,

the last field Episode is where i need to record there readmission number.

you can see in HERBERT-7/1929 his first discharge date is 17/07/2006 13:37 and the next admission for him is 17/07/2006 13:20 as this falls within one day hi episode = 1 so it still fall as part of the first episode, if you look at the next patient FREDA-11/1925 you will see that the spisode changes from 1 to 2 as the diffrence between the discharge date and admission date is > 1 day and the next record the episode changes to 3.

Basicaly it away of tracking a patient thru the system and recording there lenght of stay for each admission.

Index_key ur_episode Admission_datetime discharge_ datetime Episode
HERBERT-7/1929 513884-1686900 4/07/2006 10:58 17/07/2006 13:37 1
HERBERT-7/1929 C023092-1698859 17/07/2006 13:20 24/07/2006 0:30 1

Index_key ur_episode Admission_datetime discharge_ datetime Episode
FREDA-11/1925 183772-1998910 27/03/2007 9:53 3/04/2007 11:06 1
FREDA-11/1925 G147858-2007408 3/04/2007 10:49 26/04/2007 12:39 1
FREDA-11/1925 183772-2037727 28/04/2007 17:05 9/05/2007 11:41 2
FREDA-11/1925 G147858-2052082 11/05/2007 12:00 25/05/2007 11:17 3

Thanks

Michael

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-11-01 : 08:36:10

There are probably better methods, but in SQL2005 the following may be what you want.
(In SQL2000 you will need another #temp table instead of using DENSE_RANK())

-- *** Test Data ***
CREATE TABLE #test
(
    Index_key varchar(30) COLLATE database_default NOT NULL
    ,ur_episode varchar(30) NOT NULL
    ,Admission_datetime datetime NOT NULL
    ,discharge_datetime datetime NOT NULL
)
INSERT INTO #test
SELECT 'HERBERT-7/1929', '513884-1686900', '20060704 10:58' , '20060717 13:37' UNION ALL
SELECT 'HERBERT-7/1929', 'C023092-1698859', '20060717 13:20', '20060724 0:30' UNION ALL
SELECT 'ELSIE-5/1916', 'G148445-1720874', '20060808 11:00', '20060830 10:00' UNION ALL
SELECT 'STANISLAWA-3/1918', 'G119981-1720045', '20060808 13:01', '20060822 12:13' UNION ALL
SELECT 'FREDA-11/1925', '183772-1998910', '20070327 9:53', '20070403 11:06' UNION ALL
SELECT 'FREDA-11/1925', 'G147858-2007408', '20070403 10:49', '20070426 12:39' UNION ALL
SELECT 'FREDA-11/1925', '183772-2037727', '20070428 17:05', '20070509 11:41' UNION ALL
SELECT 'FREDA-11/1925', 'G147858-2052082', '20070509 12:00', '20070525 11:17'
-- *** End Test Data ***

CREATE TABLE #TmpLinks
(
    Index_key varchar(30) COLLATE database_default NOT NULL
    ,Admission_datetime datetime NOT NULL
    ,discharge_datetime datetime NULL
    ,BaseAdmission_datetime datetime NOT NULL
)
INSERT INTO #TmpLinks
SELECT T2.Index_key, T2.Admission_datetime, T2.discharge_datetime, T1.Admission_datetime
FROM #test T1
    JOIN #test T2
        ON T1.Index_key = T2.Index_key
            AND DATEADD(day, 0, DATEDIFF(day, 0, T1.discharge_datetime))
                = DATEADD(day, 0, DATEDIFF(day, 0, T2.Admission_datetime))

WHILE (1=1)
BEGIN
    UPDATE T2
    SET BaseAdmission_datetime = T1.BaseAdmission_datetime
    FROM #TmpLinks T1
        JOIN #TmpLinks T2
            ON T1.Index_key = T2.Index_key
                AND DATEADD(day, 0, DATEDIFF(day, 0, T1.discharge_datetime))
                    = DATEADD(day, 0, DATEDIFF(day, 0, T2.Admission_datetime))
    IF (@@ROWCOUNT = 0)
        BREAK
END

INSERT INTO #TmpLinks
SELECT DISTINCT Index_key, BaseAdmission_datetime, NULL, BaseAdmission_datetime
FROM #TmpLinks

SELECT T1.Index_key, T1.ur_episode, T1.Admission_datetime, T1.discharge_datetime
    ,DENSE_RANK() OVER (PARTITION BY T1.Index_key ORDER BY T2.BaseAdmission_datetime) AS Episode
FROM #test T1
    LEFT JOIN #TmpLinks T2
        ON T1.Index_key = T2.Index_key
            AND T1.Admission_datetime = T2.Admission_datetime
ORDER BY T1.index_key, T1.admission_datetime

yoyo_master
Starting Member

4 Posts

Posted - 2007-11-04 : 23:42:27

Hi Thanks for the code.

when I ran the code I came up with another problem, when I have a patient whom is admited and discharged in the same day I was getting duplicate records in the TmpLinks table.

I have added another patient ('ALY-ANGELA-5/1933') to the script with this problem.

-- *** Test Data ***
CREATE TABLE #test
(
Index_key varchar(30) COLLATE database_default NOT NULL
,ur_episode varchar(30) NOT NULL
,Admission_datetime datetime NOT NULL
,discharge_datetime datetime NOT NULL
)
INSERT INTO #test
SELECT 'HERBERT-7/1929', '513884-1686900', '20060704 10:58' , '20060717 13:37' UNION ALL
SELECT 'HERBERT-7/1929', 'C023092-1698859', '20060717 13:20', '20060724 0:30' UNION ALL
SELECT 'ELSIE-5/1916', 'G148445-1720874', '20060808 11:00', '20060830 10:00' UNION ALL
SELECT 'STANISLAWA-3/1918', 'G119981-1720045', '20060808 13:01', '20060822 12:13' UNION ALL
SELECT 'FREDA-11/1925', '183772-1998910', '20070327 9:53', '20070403 11:06' UNION ALL
SELECT 'FREDA-11/1925', 'G147858-2007408', '20070403 10:49', '20070426 12:39' UNION ALL
SELECT 'FREDA-11/1925', '183772-2037727', '20070428 17:05', '20070509 11:41' UNION ALL
SELECT 'FREDA-11/1925', 'G147858-2052082', '20070509 12:00', '20070525 11:17' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'STV', '20070209 7:08', '20070220 13:25' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'SGH', '20070220 13:25', '20070225 19:44' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'STV', '20070225 19:44', '20070225 20:38' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'SGH', '20070225 20:38', '20070302 11:37' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'STV', '20070302 11:37', '20070302 12:29' UNION ALL
SELECT 'ALY-ANGELA-5/1933', 'SGH', '20070302 12:29', '20070303 10:00'

-- *** End Test Data ***

Thanks for your help,I think your getting closer.

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources