Joining 2 Tables wherein one will be made as column(s)

[popseven]

Hi Guys,

I know this is long but I need to explain as much as possible
the problem I have.


Given 2 MSSQL 2000 tables,

PATIENTMEDICATIONS
================================
PATIENTMEDICATIONSKEY INT
PATIENTKEY INT
MEDICATION VARCHAR(20)
ACTION VARCHAR(20)
MEDICATIONDATE DATETIME
================================


PATIENTIOP
================================
PATIENTIOPKEY INT
PATIENTKEY INT
IOPDATE DATETIME
================================



Given the following sample table data:


PATIENTIOP
===============================================
PATIENTIOPKEY PATIENTKEY IOPDATE
1 1 2006-08-09 16:47:00
2 1 2006-07-21 00:40:00
3 1 2006-08-08 00:45:00
4 1 2006-08-08 00:49:00
5 2 2006-07-21 00:40:00
===============================================

PATIENTMEDICATIONS
====================================================================================
PATIENTMEDICATIONSKEY PATIENTKEY MEDICATION ACTION MEDICATIONDATE
1 1 Minidiab Continue 2006-07-21 00:00:00
2 1 Advil Continue 2006-07-12 00:00:00
3 1 ornex Stop 2006-08-08 00:00:00
4 1 Colchicin Review 2006-08-09 00:00:00
5 2 Biogesic Continue 2006-08-09 00:00:00
6 2 Tilcotil Stop 2006-08-10 00:00:00
7 1 Sinutab Stop 2006-08-11 00:00:00
====================================================================================



Anybody can show me a query that will return like this:

where Patientkey = 1

======================================================
MEDICATION C1 C2 C3 C4
======================================================
Advil
Ornex Stop Stop
Minidiab Continue
Colchicin REview
Sinutab
======================================================

Notes:

MEDICATION column is taken from PATIENTMEDICATIONS.MEDICATION field.
C1, C2, C3, C4 are variable columns (depending on the number of records for a particular
PATIENTIOP.PATIENTKEY) based on PATIENTIOP.IOPDATE order should be Ascending.
so C1 = (2006-07-21 00:40:00) ; C2 = (2006-08-08 00:45:00) ; C3 = (2006-08-08 00:49:00)
C4 = (2006-08-09 16:47:00)

Noticed that C2 and C3 are of the same date but of different time. Should something like this
happens, a particular MEDICATION whose MEDICATIONDATE falls under this, should be displayed
as many times as possible under the column of the same date just like in the case of ORNEX above.

If a MEDICATION whose MEDICATIONDATE doesn't falls on any of the columns (C1, C2, C3 and C4), then
it will still be displayed but will have no values across all the "C" columns like in the case of ADVIL and SINUTAB.

Can something like this can be done?

Please Help,
popseven

 

[vbkris]

hi,

first thing, what excatly relates the PATIENTMEDICATIONS and PATIENTIOP table? from what i see it is the patient key, but then how is Advil or Ornex related to the PATIENTOP data?

what you require can be done using cursors (not straight joins). but requires some more clarrifications...

Known is handfull, Unknown is worldfull

 

[popseven]

Hi vbkris,

Yes, PATIENTKEY relates/connects the 2 tables.
The MEDICATION (Advil, Ornex, etc.) are the medication for a particular patient. On the sample output I have shown above, that is true when PATIENTKEY = 1.

popseven

 

[vbkris]

hi,

like i said how do i know which PATIENTMEDICATIONS is to which PATIENTIOP?

patient key 1 is set to both anvil and ornex. therefore both will have the same data...

Known is handfull, Unknown is worldfull