data conversion

[jmk418]

Hi all,
I have a table that contains tranfser information. The table currently has each transfer as a seperate record as below.

001112 16662512 A EDW MICU 2005-06-13 07:20:00.000
001112 16662512 T MICU 7-SO 2005-06-14 16:20:00.000
003312 16753733 A EDC 5TCU 2005-11-16 01:52:00.000
003312 16753733 T 5TCU 4-SO 2005-11-20 17:02:00.000
003312 16761447 A EDW 5TCU 2005-11-30 19:34:00.000
003312 16761447 D 5TCU 2005-12-02 16:33:00.000
003312 16771016 T 5-SO MICU 2005-12-19 03:45:00.000
003312 16771016 T MICU 5TCU 2005-12-19 16:09:00.000
003312 16771016 T 5TCU 6-SO 2005-12-22 15:32:00.000

i would like simplify the data to appear that each record would be one move with a time in and out for transfer location. So the first record would appear as record number, admission number, starting unit, time transfered into, tranfer unit, time transfered out, ending unit

001112 16662512 EDW 2005-06-13 7:20:00.000 MICU 2005-06-14 16:20:00.0000 7-SO

i have been playing with a cursor and a while loop do look at each record 1 at a time and store variables with the information. The problem is when i get to a case where a record has 3 tranfers. Does anyone have an easier solution for this problem or and tips on how to solve this. Let me know if i need more information. Thanks in advance.
Jeremy

 

[checkai]

i would probably do a 4 step process to be the most accurate...

1)insert each distinct transfer # and admission#: 001112 16662512
2)update starting unit and time
3)update transfer unit and time
4)updateending unit




"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[jmk418]

The problem with inserting the distinct record number and admission number is that the last 3 records would only insert 1 record for that admission but i would need 2 records displayed because there were 2 tranfers.

003312 16771016 5-SO 2005-12-19 03:45:00.000 MICU 2005-12-19 16:09:00.000 5TCU
003312 16771016 MICU 2005-12-19 03:45:00.000 5TCU 2005-12-22 15:32:00.000 6-SO