Need a Query to return

[Cwisofsky]

I am trying to write a query to return to values. I have a database that has clinical_transactions and I need to know if a person received a proc_code = "SC20" and a proc_code = "SC01" transaction in a given time frame. I have tried inner joins, unions, and temp tables and have not gotten the results I need.

 

[fluteplr]

Sorry the clairvoyance module is not working today. Could we PLEASE have table layouts, relations etc. so we might have a clue about how to answer your question.

 

[Cwisofsky]

In table "Clinical Transactions" there field "proc_code" designates a type of transaction. The field "proc_chron" holds the date of the transaction. Field "patient_id" is for the patient who had the transaction. I need a list of which patients had both a proc_code = "SC20" and a proc_code = "SC01" transaction in a given time frame.

 

[tlbroadbent]

Try this self-join query.

Select
a.PatientID,
a.proc_chron As SC20Date,
b.proc_chron As SC01Date

From Clinical_Transactions a
Inner Join Clinical_Transactions b
On a.PatientID=b.PatientID

Where a.proc_code = 'SC20'
And b.proc_code = 'SC01'
Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm