I have 3 Tables with the following charateristics:
Table 1 - Procedures: SourceID (PK), ProcedureID (PK), Field1, Field2....
Table 2 - StdEffDate: SourceID (PK), ProcedureID(PK), EffDateTime(PK), Price
Table 3 - AltEffDate: SourceID (PK), ProcedureID(PK), TypeID(PK), EffDateTime(PK), Code
What I need is a query that pulls some flelds from T1 where Field1 = 'Y', the latest price from T2 based on EffDate, The Latest Code Base on EffDate From T3 where the value is TypeID is ABC and the latest code from T3 where the value of TypeID is XYZ. Populating the fields from T2 or T3
should not limit the records from T1 in other words if I queried T1 by itself with the criteria, adding fields from T2 and T3 should not change the number of records returned. By convention Price is usually not blank but nothing in the design prevents it. On the other hand Code could have values for one, both or neither.
Please let me know if this needs further clarification. I do have a query working that does everything but the last piece:
SELECT A.ProcedureID, A.Description, A.ChargeDeptID, A.ChargeCategoryID, B.Charge, A.RelativeValueUnits, C.Code As CPT, D.Code AS HCPCS
FROM (( DBarProcedures AS A
INNER JOIN DBarProcStandardEffectDates AS B ON A.SourceID = B.SourceID AND A.ProcedureID = B.ProcedureID)
LEFT OUTER JOIN DBarProcAltCodeEffectDates AS C ON A.SourceID = C.SourceID AND A.ProcedureID = C.ProcedureID)
LEFT OUTER JOIN DBarProcAltCodeEffectDates AS D ON A.SourceID = D.SourceID AND A.ProcedureID = D.ProcedureID
WHERE B.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcStandardEffectDates AS C WHERE C.ProcedureID = B.ProcedureID ) AND
C.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcAltCodeEffectDates AS E WHERE E.ProcedureID = A.ProcedureID ) AND
D.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcAltCodeEffectDates AS F WHERE F.ProcedureID = A.ProcedureID ) AND
A.ChargeDeptID = '01.762000' AND C.TypeID = 'CPT-4' AND D.TypeID = 'HCPCS'
ORDER BY A.ProcedureID