99222 NOT 0007

I

Ian

I'm trying to build a single SQL statement to return all patientID that have
had codes 99222 or 99111 rendered but haven't had code 00007 rendered within
8 weeks.

For instance 99222 is rendered on Sept 1, 2009. If there isn't a
corresponding code of 00007 between Aug 1, 2009 and Oct 1, 2009 for the same
patientID I want the PatientID to be returned.

All codes and patient ID's are in the same table (Treatment). I've linked
to a table called Patient to show names at a later date but the PatientID
field is in both tables.


SELECT dbo_Patient.PatientID, dbo_Treatment.TreatDate, dbo_Treatment.TreatCode
FROM dbo_Patient INNER JOIN dbo_Treatment ON dbo_Patient.PatientID =
dbo_Treatment.PatientID
WHERE (((dbo_Treatment.Billed)=Yes) AND ((dbo_Treatment.Reversed)=No))
GROUP BY dbo_Patient.PatientID, dbo_Treatment.TreatDate,
dbo_Treatment.TreatCode
HAVING (((dbo_Treatment.TreatDate)>#9/1/2009#) AND
((dbo_Treatment.TreatCode)="99111" Or (dbo_Treatment.TreatCode)="99222"));

Any help is appreciated
 
K

KARL DEWEY

Try this --
SELECT dbo_Patient.PatientID, dbo_Treatment.TreatDate, dbo_Treatment.TreatCode
FROM dbo_Patient INNER JOIN dbo_Treatment ON dbo_Patient.PatientID =
dbo_Treatment.PatientID
WHERE (dbo_Treatment.Billed=Yes) AND (dbo_Treatment.Reversed)=No) AND
(dbo_Treatment.TreatDate>=#9/1/2009#) AND (dbo_Treatment.TreatCode="99111" Or
dbo_Treatment.TreatCode)="99222") AND dbo_Patient.PatientID Not In (SELECT
dbo_Patient.PatientID FROM dbo_Patient WHERE dbo_Treatment.TreatCode =
"00007" AND dbo_Treatment.TreatDate>=#9/1/2009#));
 
J

John Spencer

Try a query like the following. Note that I used the DISTINCT keyword to
eliminate duplicates instead of grouping by the fields to get the same result.

SELECT DISTINCT dbo_Patient.PatientID
, dbo_Treatment.TreatDate
, dbo_Treatment.TreatCode
FROM dbo_Patient INNER JOIN dbo_Treatment
ON dbo_Patient.PatientID = dbo_Treatment.PatientID
WHERE dbo_Treatment.Billed)=Yes
ND dbo_Treatment.Reversed=No
AND dbo_Treatment.TreatDate>#9/1/2009#
AND dbo_Treatment.TreatCode= IN("99111","99222")
AND NOT EXISTS
(SELECT *
FROM dbo_Treatment As TEMP
WHERE Temp.PatientID = dbo_Patient.PatientID
AND Temp.TreatCode = "00007"
AND Temp.TreatDate BETWEEN #2009-08-01# and #2009-10-01#)
ORDER BY dbo_Patient.PatientID
, dbo_Treatment.TreatDate
, dbo_Treatment.TreatCode

If you have some reason to use the grouping then the SQL would be

SELECT DISTINCT dbo_Patient.PatientID
, dbo_Treatment.TreatDate
, dbo_Treatment.TreatCode
FROM dbo_Patient INNER JOIN dbo_Treatment
ON dbo_Patient.PatientID = dbo_Treatment.PatientID
WHERE dbo_Treatment.Billed)=Yes
ND dbo_Treatment.Reversed=No
AND dbo_Treatment.TreatDate>#9/1/2009#
AND dbo_Treatment.TreatCode= IN("99111","99222")
AND NOT EXISTS
(SELECT *
FROM dbo_Treatment As TEMP
WHERE Temp.PatientID = dbo_Patient.PatientID
AND Temp.TreatCode = "00007"
AND Temp.TreatDate BETWEEN #2009-08-01# and #2009-10-01#)
GROUP BY dbo_Patient.PatientID
, dbo_Treatment.TreatDate
, dbo_Treatment.TreatCode


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top