June 15, 2005 at 2:46 pm
Ok, that makes sense...so here's the final joining to the additional tables that i needed...
Thanks again...
SELECT
A.FacilityID,
SUM( CASE WHEN DateHired is not null THEN 1 ELSE 0 END) AS '# Hires',
SUM( CASE WHEN DateHired is null THEN 1 ELSE 0 END) AS '# Applicants',
ISNULL(Terms.#Terminations,0)As '# Terminations',
ISNULL(Trans.#Transfers,0)As '# Transfers',
ISNULL(Promo.#Promotions,0)As '# Promotions',
ISNULL(MLeave.#Maternity,0)As '# MLeave.#Maternity'
FROM Applicant A
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN DateTerminated is not null THEN 1 ELSE 0 END ) AS '#Terminations'
FROM Termination
WHERE PlanYear=2001
GROUP BY FacilityID)AS Terms
ON A.FacilityID=Terms.FacilityID
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN TransferPromotionReceived = 'Y' THEN 1 ELSE 0 END ) AS '#Transfers'
FROM TransferPromotion
WHERE PlanYear=2001
AND TransferPromotion='T'
GROUP BY FacilityID)AS Trans
ON A.FacilityID=Trans.FacilityID
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN TransferPromotionReceived = 'Y' THEN 1 ELSE 0 END ) AS '#Promotions'
FROM TransferPromotion
WHERE PlanYear=2001
AND TransferPromotion='P'
GROUP BY FacilityID)AS Promo
ON A.FacilityID=Promo.FacilityID
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN LeaveStatusID = 'O' THEN 1 ELSE 0 END ) AS '#Maternity'
FROM MaternityLeave
WHERE PlanYear=2001
GROUP BY FacilityID)AS MLeave
ON A.FacilityID=MLeave.FacilityID
WHERE A.PlanYear=2001
AND A.FacilityID IS NOT NULL
GROUP BY A.FacilityID,
Terms.#Terminations,
Trans.#Transfers,
Promo.#Promotions,
MLeave.#Maternity
ORDER BY A.FacilityID asc
June 15, 2005 at 2:51 pm
SELECT
A.FacilityID,
SUM( CASE WHEN DateHired is not null THEN 1 ELSE 0 END) AS 'HireCnt',
SUM( CASE WHEN DateHired is null AND A.Facility IS NOT NULL THEN 1 ELSE 0 END) AS 'NotHireCnt',
ISNULL(Terms.TermsCnt,0)As 'TermsCnt'
FROM Applicant A
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN DateTerminated is not null THEN 1 ELSE 0 END ) AS 'TermsCnt'
FROM Termination
WHERE PlanYear=2003
GROUP BY FacilityID)AS Terms
ON A.FacilityID=Terms.FacilityID
WHERE A.PlanYear=2003
GROUP BY A.FacilityID,Terms.TermsCnt
ORDER BY A.FacilityID asc
Vasc
June 15, 2005 at 2:54 pm
I see...i had the not null in the wrong place...
It works great now...thanks for your quick and meaningfull replies...
I'm heading home for the day...
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply