November 4, 2008 at 2:17 am
Hi! There
I have a staff table which contain staff details and vacant positions as well. Now what I need to do is to display vacant positions and the period for which they have been vacant.
The problem I'm experiencing is that a position may have been vacant in January 2008 then was filled in March 2008 but the staff member left in April 2008. I need to calculate the vacant period from May 2008 not Jan but my query is calculating from jan.
Sample data:
Table Name : Headcountdec
Staffno PosID Period
NULL 223 200801
NULL 025 200801
NULL 223 200802
NULL 025 200802
123456 223 200803
NULL 025 200803
NULL 223 200804
NULL 025 200804
NULL 223 200805
227561 025 200805
NULL 223 200806
NULL 025 200806
NULL 223 200807
NULL 025 200807
NULL 223 200808
NULL 025 200808
NULL 223 200809
NULL 025 200809
Now according to this data position 123456 has been vacant for six months while the other one has been vacant for 4 months.
----vw_VacancyMonths is the view I use to connect to Headcountdec and calculate the position has been vacant.
view structure = SELECT PosID, Period as Vacant
FROM dbo.Headcountdec
WHERE (Period LIKE '2008%') AND (StaffNo IS NULL)
GROUP BY PosID, Period
Query:-
SELECT distinct Headcountdec.StaffNo, Headcountdec.PosID, count(vw_VacancyMonths.vacant)
FROM Headcountdec INNER JOIN
vw_VacancyMonths ON Headcountdec.PosID = vw_VacancyMonths.PosID
WHERE (Headcountdec.Period = 200809)
GROUP BY Headcountdec.StaffNo, Headcountdec.PosID, vw_VacancyMonths.Vacant
HAVING (Headcountdec.StaffNo IS NULL)
ORDER BY Headcountdec.PosID
Now when I run this query it counts from January not from the time a staff member left.
Please assist.
November 4, 2008 at 3:18 am
The problem is in the view, where you're selecting from the table where the StaffNo is null. This will return values prior to the position being filled, which you don't want. So what you want to do is, in the view, exclude any rows that fall prior to a period during which a position was filled.
Hope that helps.
November 4, 2008 at 4:57 am
Hi! Thanks for your feedback. I haven't had a chance to review my original query based on your suggestion.
However this is how I temporarily resolved the ad-hoc-
Step 1 ---->
CREATE VIEW Vacancies AS
SELECT PosID
, MAXPERIOD = Max(case WHEN staffno IS NOT NULL and headcount = 'headcount' THEN PERIOD ELSE 0 END)
, VacantPeriod = MIN(case WHEN staffno IS NULL and headcount = 'headcount' THEN PERIOD ELSE 0 END)
FROM Headcountdec
WHERE Period BETWEEN 200801 AND 200809
Group By PosID
Order By PosID
Step 2 ---->
SELECT Vacancies.PosID, Vacancies.MAXPERIOD, Vacancies.VacantPeriod,
PeriodVac = 200809 - (Case When Maxperiod = 0 then vacantperiod else maxperiod end)
FROM Vacancies INNER JOIN
Headcountdec ON Vacancies.PosID = Headcountdec.PosID
WHERE (Headcountdec.Period = 200809) and Staffno IS NULL
Thanks for your assistance
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply