Start Date and End Date Count People by Month

[Elvis72]

The insane powers that be that I work with have requested a worker count at site by month/year.

So we have the worker associated with the job and they have individual mobilization and demobilization dates.

What I need to do is be able to convert the date range to a monthly count breakdown.

What I have is:

Project Name: RFCC Study
Bugs Bunny 01/01/2008 12/31/2010
Daffy Duck 05/15/2009 06/15/2012

What I need is:

Project Name: RFCC Study
January 2008 February 2008 March 2008 April 2008 May 2008
1 1 1 1 2

I've read alot of different posts but nothing I have found really hits this topic.

Maybe I'm just not using the correct verbage?

Thanks so much for your help!~

 

[Mike Lewis]

Sounds like a job for Pivot Table wizard. Check the Help topic of that name, or experiment with the wizard, which is on the Tools / Wizards menu.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Elvis72]

OK...I have a Crosstab Query so far with the below:

TRANSFORM Count(TblProjectInformation.ProjectID) AS CountOfProjectID
SELECT TblProjectInformation.JobProposalNo
FROM TblProjectInformation, TblDate
WHERE (((TblDate.TheDate) Between #1/1/2004# And #1/1/2025#) AND ((TblProjectInformation.[Mobilization Date])<=[tblDate].[TheDate]) AND ((TblProjectInformation.[Demobilization Date])>=[tblDate].[TheDate]))
GROUP BY TblProjectInformation.[Mobilization Date], TblProjectInformation.[Demobilization Date], TblProjectInformation.JobProposalNo
PIVOT TblDate.TheDate;

But the above does not give me the totals just counts so instead of the above outcome I have this right now:

JobProposalNo 11/1/2008 12/1/2008 1/1/2009
12345 1 1 1
12345 1 1 1

I am about to check out Mike's above idea

 

[Olaf Doschke]

seems you're using sql server. Why not ask in the sql server forum?

http://www.tek-tips.com/threadminder.cfm?pid=183

Bye, Olaf.