Full Version: *Confused* Date Calculations :(
Sup3rT3d
I have a table holding all staff details.

This table holds names etc.. but also 2 fields, namely [Staff Start Date] and [Staff End Date]

I want to create a query that can display by the first of each month how many staff we have.

eg.

Jan 2006 - 280 Staff
Feb 2006 - 270 Staff
Mar 2006 - 260 Staff

I can display this 1 month at a time, however i cant create it so it displays it month by month!

Any help please boys and girls!
souLTower
Try this.

CODE
SELECT count(empID) as CT from myEmpTable WHERE
startDate <= '1/1/2006' AND (
endDate IS NULL OR endDate > '1/1/2006'

This will give you the count of employees on Jan 1 2006
Sup3rT3d
yeah, i can do month on month,

however i need a total which displays all months in 1 table eg.

Jan 2006 - 280 Staff
Feb 2006 - 270 Staff
Mar 2006 - 260 Staff

not just Jan 2006 - 280 Staff
souLTower
That's tougher. You'll either need a function which runs the query multiple times, create a tally table with all dates in whatever range you choose, or you can hand write a view. Since this is being done monthly as you say a hand written is not a terrible solution but it's not flexible either. Try this.....

CODE
SELECT count(empID) as CT from myEmpTable
WHERE  startDate <= '1/1/2006' AND (endDate IS NULL OR endDate > '1/1/2006'

UNION ALL

SELECT count(empID) as CT from myEmpTable
WHERE  startDate <= '2/1/2006' AND (endDate IS NULL OR endDate > '1/1/2006'

UNION ALL

SELECT count(empID) as CT from myEmpTable
WHERE  startDate <= '3/1/2006' AND (endDate IS NULL OR endDate > '1/1/2006'

That's the ugly method but it will work and considering for a given year you will only have 12 of these it's not too bad. Let me know if you're interested in the other solutions.
jsitraining
No you don't.

CODE
SELECT MONTH(STARTDATE) & " " & YEAR(STARTDATE), count(empID)

FROM MYEMPTABLE

GROUP BY  MONTH(STARTDATE) & " " & YEAR(STARTDATE)

actually I just re-read the original post, my 'solution' is wrong Sorry. Gimme a minute
HTH
Jim

Edited by: jsitraining on Fri Mar 3 8:16:06 EST 2006.
Sup3rT3d
i have very basic knowledge of SQL etc.. however jsitraining, surely that gives me a list of who started in that month, it doesnt take into consideration of anyone who is actually already working, plus anyone whos left in that month. i know this is complicated :s
jsitraining
Sup3rT3d
it doesnt contain the end date, therefore it cant take into consideration when agents leave

QUOTE
SELECT MONTH(Company Start Date) & " " & YEAR(Company Start Date), count(StaffID)
FROM tbLStaffHistory
GROUP BY MONTH(Company Start Date) & " " & YEAR(Company Start Date)

thats my edited version
jsitraining
If you read my edit you would notice that I acknowledged that my answer was wrong due to mis-reading your original post: this is the bit that gives it away:

QUOTE
actually I just re-read the original post, my 'solution' is wrong Sorry. Gimme a minute

Jim
Sup3rT3d
i assumed ure edit made it correct, no worries, anyone with any ideas?
souLTower
I'm confused. Do you want the number of employees which were present on the given date or do you want the number of employees which were present for a given whole month. Meaning a guy hired 1/4/2005 fired 1/8/2005 would appear in the data?

If the latter you will need to use a tally table
Sup3rT3d
if i guy was hired in jan and sacked in dec

he would appear in all months of the year! upto his end date
souLTower
OK. This should do it. Again it's a "hard Coded" way but will work.

<font class="small">Code:</font><hr /><pre>

SELECT 'Jan 2005' as DT, count(empID) as CT from myEmpTable
WHERE startDate <= '2/1/2005' AND (endDate IS NULL OR endDate >= '2/1/2005')

UNION ALL

SELECT 'Feb 2005' as DT, count(empID) as CT from myEmpTable
WHERE startDate <= '3/1/2005' AND (endDate IS NULL OR endDate >= '3/1/2005')

etc

</pre><hr />

Edited by: souLTower on Fri Mar 3 10:23:01 EST 2006.
Sup3rT3d
That kinda works, thanks, however i think if i'm gonna do it like that i'd might aswell use pen and paper. is there no way to automate it as i have to try and make this so monkeys can edit it!
souLTower
Yes, tomake it automatic you must use a tally table. A tally table is a table of numbers.

CODE
TABLE TALLY
NUM
0
1
2
3
etc

Then create a view of dates from this like so.....

SELECT '1/1/2005' + NUM FROM TALLY WHERE   DAY(DATEVALUE('1/1/2005' + NUM )) = 1

This would give you a table or view full of dates like this
1/1/2005
2/1/2005
3/1/2005

Call that table dates.  Now join to it like this

SELECT MONTHNAME(IIF(MONTH(D.DT) - 1 = 0, 12, MONTH(D.DT))) as MONTHNAME,
IIF(MONTH(D.DT) - 1 = 0, YEAR(D.DT) - 1, YEAR(D.DT)) AS YR,
(SELECTcount(empID) FROM empTable WHERE
WHERE startDate <= D.DT AND (endDate IS NULL OR endDate >= D.DT) as CT from
Dates D

Let me know if that works for you
Sup3rT3d
i already have a list of dates in a table called "tblMonthDetails"

this is for another fuction, could i use this in this case?
souLTower
You can.