UtterAccess Discussion Forums > *Confused* Date Calculations :(

Full Version: *Confused* Date Calculations :(

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Sup3rT3d

Mar 3 2006, 07:00 AM

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

Mar 3 2006, 07:08 AM

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

Mar 3 2006, 07:12 AM

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

Mar 3 2006, 07:50 AM

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

Mar 3 2006, 08:14 AM

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

Mar 3 2006, 08:29 AM

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

Mar 3 2006, 08:30 AM

Read my edit

Sup3rT3d

Mar 3 2006, 08:47 AM

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

Mar 3 2006, 08:48 AM

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

Mar 3 2006, 09:03 AM

i assumed ure edit made it correct, no worries, anyone with any ideas?

souLTower

Mar 3 2006, 10:14 AM

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

Mar 3 2006, 10:15 AM

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

Mar 3 2006, 10:21 AM

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

Mar 3 2006, 10:51 AM

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

Mar 3 2006, 11:08 AM

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

Mar 3 2006, 11:12 AM

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

Mar 3 2006, 11:22 AM

You can.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.