SubQuery Question - two variables 1

[toddl]

Hi,

I'm wondering if this is possible. I have three Tables.


tblTransactions
Patient Procedure Procedure Date Provider
XYZ 99 5/1/2003 1
XYZ 99 5/1/2004 1
XYZ 99 5/1/2005 1


tblProcedures
Procedure Code Amount Start Date End Date
99 75 1/1/2003 12/31/2003
99 80 1/1/2004 12/31/2004
99 78 1/1/2005 12/31/2005

tblProviders
Provider Contract Start Contract End
1 11/1/2002 10/31/2003
1 11/1/2003 10/31/2004
1 11/1/2004 10/31/2005

The procedure codes are in use from there start date to there end date, however, if a provider signs their contract during the year (not on the calendar year), they use that years numbers for their contract year. For example, the transaction on 5/1/2004 should have an amount of 75.00. The transaction on 5/1/2005 should have an amount of 80.

I've tried to come up with a subquery to pull this, but I don't know how to do it with the two variables (Provider contract dates and procedure dates)

Any hints?

Todd

 

[PHV]

Something like this ?
SELECT T.Patient, T.Procedure, T.[Procedure Date], T.Provider, P.Amount, P.[Start Date], P.[End Date], C.[Contract Start], C.[Contract End]
FROM (tblTransactions AS T
INNER JOIN tblProcedures AS P ON T.Procedure = P.[Procedure Code])
INNER JOIN tblProviders AS C ON T.Provider = C.Provider
WHERE (T.[Procedure Date] Between C.[Contract Start] And C.[Contract End])
AND (C.[Contract Start] Between P.[Start Date] And P.[End Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[toddl]

Thanks PH,

This works on my test data. Saved me a lot of work, I was thinking I would have to do two queries with subqueries in them.


Todd