Count month by month

[harpercl]

I need to create a query that will count the number of people from different areas that are working on a task in a given month. For example, I have a table containing:

Candidate StartDate EndDate Agency
1122 Oct-01 Dec-01 Agency_A
2233 Nov-01 Jan-02 Agency_B
3344 Nov-01 Feb-02 Agency_A
4455 Dec-01 Feb-02 Agency_B

I want the result of my query to give:

Month Agency NumberCandidates
Oct-01 Agency_A 1
Nov-01 Agency_A 2
Nov-01 Agency_B 1
Dec-01 Agency_A 2
Dec-01 Agency_B 2... and so on

Is there anyway I can can write a query to go through the data checking month by month?

Thanks in advance.

 

[jhall156]

I don't think you can do this in a single select statement because you need to group by month and some of the needed month values will not be enumerated. For example if any of the start-end ranges encompass Jan-02 (e.g. Dec-01 to Feb-02) but none of them actually start or end with Jan-02 then I can't see how we can return that value without a little preliminary work. You could write a function to populate a MonthRange table to use on the left side of an outer join query and do aggregate calcs on that value. But if anyone else can figure a way to do it with a single select I'd be very pleased to see it. I started out with nothing, and I still have most of it.

 

[MichaelRed]

Hmmmmmmmmm,

The problem statement is not crystal clear. What if 'good old' 1122 started his task at "Agency_A" on Oct 2, 15th, 31st? Should he STILL be counted in Oct's Totals? From the language and fields, I'm guessing this is "Government work", and ANY part of the month should be counted. If so, just using somethig like beteween Month(SartDate} and Month(EndDate) should be sufficient criteria to determine the count within the groupings. Other than this, a simple crosstab query looks like the be-all/end-all for this one.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jhall156]

If no candidate starts or ends in a particular month but a candidate or candidates have a range that encompasses that month how will you get that month into your column? I started out with nothing, and I still have most of it.

 

[rac2]

I think this logic will catch all who worked on the project for some part of a month. May need to tune it to handle starting or ending on beginning and ending dates of the month. Or, use the MONTH() function.

If (startDate is before periodBeginDate AND (endDate is after periodBeginDate OR endDate is NULL))
OR
(startDate is after periodBeginDate AND startDate is before periodEndDate)


MONTH(startDate) gives a number from 1 to 12 for the month part of the startDate, etc.


Create a VIEW named AgencyMonths with this query -

SELECT Agency, 
CASE
   WHEN
      (MONTH(startDate) < 1 AND ( endDate IS NULL OR MONTH(endDate) > 1 ))
       OR
       (MONTH(startDate) = 2)
      THEN 'January'
   WHEN       (MONTH(startDate) < 2 AND ( endDate IS NULL OR MONTH(endDate) > 2 ))
       OR
       (MONTH(startDate) = 2)
 THEN 'February'

   etc.

   ELSE 'Not Possible'
END AS &quot;Month&quot;

FROM Candidates

Then query the VIEW -

SELECT Month, Agency, COUNT(*) AS "Number of Candidates"
FROM AgencyMonths
GROUP BY Month, Agency

You will need to translate the SQL Server CASE expression into the Access equivalent, perhaps using IIF().

 

[jhall156]

If the start month is jan and the end month is march and there are no other events in that range, and considering that you want to show that activity for that event was occurring as a february record, then I can see how to create an expression that will generate that response. I still don't see how to get any records like that one from a set of records. I'll chew on it some more. I started out with nothing, and I still have most of it.