Combining distinctive queries to one 1

[gatetec]

I have 4 queries below that each query should return different results due to the qualifications in WHERE.

What I want is the output is showing in a row i.e.
Qry1 Qry2 Qry3 Qry4
Dept A 11 0 1 9
Dept B 3 4 4 7
Dept C 17 9 5 2
....

Pls advise.

thx so much

Qry 1)
select Dept, count (A.AssociateID) as TheCnt
from MedicalINFO M, AssociatePersonal A
where M.AssociateID = A.AssociateID
and FluShotDate between '01-01-2008' and '12-31-2008'
and AccDec ='Accept' and InfluenzaVaccine > '1900-01-01'
group by Dept
order by Dept

Qry 2)
select Dept, count (A.AssociateID) as TheCnt
from MedicalINFO M, AssociatePersonal A
where M.AssociateID = A.AssociateID
and FluShotDate between '01-01-2008' and '12-31-2008'
and AccDec ='Accept'
and (InfluenzaVaccine = '1900-01-01' or
InfluenzaVaccine is null or
len(InfluenzaVaccine) =0)
group by Dept
order by Dept

Qry 3)
select Dept, count (A.AssociateID) as TheCnt
from MedicalINFO M, AssociatePersonal A
where M.AssociateID = A.AssociateID
and FluShotDate between '01-01-2008' and '12-31-2008'
and AccDec ='Decline' and InfluenzaVaccine > '1900-01-01'
group by Dept
order by Dept

Qry 4)
select Dept, count (A.AssociateID) as TheCnt
from MedicalINFO M, AssociatePersonal A
where M.AssociateID = A.AssociateID
and FluShotDate between '01-01-2008' and '12-31-2008'
and AccDec ='Decline'
and (InfluenzaVaccine = '1900-01-01' or
InfluenzaVaccine is null or
len(InfluenzaVaccine) =0)
group by Dept
order by Dept

 

[RiverGuy]

When the queries are pretty similar, you can usually condense them into one (as below). Otherwise, you could use a FULL OUTER JOIN and COALESCE.

SELECT Dept,
SUM(Qry1) AS Qry1,
SUM(Qry2) AS Qry2,
SUM(Qry3) AS Qry3,
SUM(Qry4) AS Qry4
	FROM
	(SELECT Dept,
	CASE
	  WHEN FluShotDate between '01-01-2008' and '12-31-2008' AND AccDec ='Accept' and InfluenzaVaccine > '1900-01-01' THEN 1
	  ELSE 0	
	END AS Qry1,
	CASE
	  WHEN FluShotDate  between '01-01-2008' and '12-31-2008' AND AccDec ='Accept' AND (InfluenzaVaccine = '1900-01-01' or
			   InfluenzaVaccine is null or
			   len(InfluenzaVaccine) =0) THEN 1
	  ELSE 0
	END AS Qry2,
	CASE
	  WHEN FluShotDate between '01-01-2008' and '12-31-2008' AND AccDec ='Decline' and InfluenzaVaccine > '1900-01-01' THEN 1
	  ELSE 0
	END AS Qry3,
	CASE
	  WHEN FluShotDate  between '01-01-2008' and '12-31-2008' AND AccDec ='Decline' AND (InfluenzaVaccine = '1900-01-01' or
			   InfluenzaVaccine is null or
			   len(InfluenzaVaccine) =0) THEN 1
	  ELSE 0
	END AS Qry4
	FROM MedicalINFO M, AssociatePersonal A
	WHERE M.AssociateID = A.AssociateID) x
WHERE Qry1 > 0 OR Qry2 > 0 OR Qry3 > 0 OR Qry4 > 0 --Optional depending on all departments should be returned
GROUP BY Dept
ORDER BY Dept

 

[gatetec]

thx so much