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.
| Author |
Topic |
|
andros30
Yak Posting Veteran
80 Posts |
 Posted - 2009-04-13 : 14:13:12
|
| Hi, I'm stump on trying to write this up. Would appreciate someone to guide me.I am trying to determine the start treatment date relative to the date of the contract if there are more than one contract for a patient.Contract table:CntrNumber|PatsNumber|CntrDate|CntrAmt112245|456821|07/04/07|4150112367|456821|12/12/08|3785Clinical table:ClinicalNumber|PatsNumber|ClinicalDate|ClinicalCode456781|456821|08/11/07|6456894|456821|01/11/09|35Report should look like:PatsNumber|CntrDate|StartTx|CntrAmt456821|07/04/07|08/11/07|4150456821|12/12/08|01/11/09|3785If a patient only has one contract then it would be easy to conclude that the clinical date that is after the contract date would be the treatment date. However, if there is a second contract, I need to be able to put the right clinical date with the right contract. If there is no clinical date after a second contract, then it is assumed that the patient has not started treatment on the second contract and the report should put Null or leave the cell blank.NOTE that the only link between the tables is the PatsNumber. |
|
|
dsindo
Starting Member
45 Posts |
Posted - 2009-04-21 : 16:24:38
|
| select a.patsnumber, cntrdate, clinicaldate as StartTX, cntramt from clinical a inner join contract b on a.patsnumber=b.patsnumber |
 |
|
|
|
|
|
Determining start treatment date