Date Range with UNION ALL

[falcondeer]

Hi all,

I need some help please.

I used UNION ALL to get results from two queries and I Succeeded.

Now I want these results to be in a date range, so I want to enter the "starting date" then the "End Date" to have may results in specific date range.

This is the original code out of UNION ALL which is working fine:

SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt
FROM Patient
GROUP BY Patient.CauseOfAmpLowerLt
HAVING (((Patient.CauseOfAmpLowerLt) Not Like ""))
UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt]
FROM Patient
GROUP BY Patient.[CauseOfAmpLowerRt]
HAVING (((Patient.CauseOfAmpLowerRt) Not Like ""));


And this is what I tried:


SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt
FROM Patient
GROUP BY Patient.CauseOfAmpLowerLt
HAVING (((Patient.CauseOfAmpLowerLt) Not Like ""))
UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt]
FROM Patient
GROUP BY Patient.[CauseOfAmpLowerRt]
HAVING (((Patient.CauseOfAmpLowerRt) Not Like "") AND (PatientService.[Date of Service]) BETWEEN [Start Date] AND [End Date]);

Thanks alot.

 

[Gasman]

If I understand you correctly you want the data criteria applied to both sets of the query?

If so then apply the criteria to both sets. In your code you have applied it only to those with AmpLowerRt.

Alternatively include the DateOfService in your union query and then use another query with the date criteria and the union query as the source.

 

[plog]

And this is what I tried:

And what happened with that attempt? Obviously it failed somehow or you wouldn't be posting here. Did you get any results? An Error? Unexpected results?

Also, just from an SQL semantics point of view--the criteria you are using should probably be in a WHERE clause not a HAVING. WHERE works on individual records, HAVING works on aggregate values. Your criteria seems to be at the record level.

 

[falcondeer]

OMG, thanks Gasman, I applied the criteria to both sets and it works fine.

Thanks everybody.

 

[falcondeer]

I forgot to thank you plog :banghead: , I used Where instead of Having.