UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> *Confused* Date Calculations :(    
 
   
Sup3rT3d
post Mar 3 2006, 07:00 AM
Post #1

UtterAccess Member
Posts: 23
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!
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 07:08 AM
Post #2

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
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
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 07:12 AM
Post #3

UtterAccess Member
Posts: 23
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
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 07:50 AM
Post #4

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
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.
Go to the top of the page
 
+
jsitraining
post Mar 3 2006, 08:14 AM
Post #5

UtterAccess VIP
Posts: 5,231
From: Scotland (Sunny Glasgow)
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.
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 08:29 AM
Post #6

UtterAccess Member
Posts: 23
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
Go to the top of the page
 
+
jsitraining
post Mar 3 2006, 08:30 AM
Post #7

UtterAccess VIP
Posts: 5,231
From: Scotland (Sunny Glasgow)
Read my edit
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 08:47 AM
Post #8

UtterAccess Member
Posts: 23
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
Go to the top of the page
 
+
jsitraining
post Mar 3 2006, 08:48 AM
Post #9

UtterAccess VIP
Posts: 5,231
From: Scotland (Sunny Glasgow)
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
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 09:03 AM
Post #10

UtterAccess Member
Posts: 23
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) i assumed ure edit made it correct, no worries, anyone with any ideas?
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 10:14 AM
Post #11

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
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
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 10:15 AM
Post #12

UtterAccess Member
Posts: 23
if i guy was hired in jan and sacked in dec

he would appear in all months of the year! upto his end date
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 10:21 AM
Post #13

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
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.
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 10:51 AM
Post #14

UtterAccess Member
Posts: 23
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!
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 11:08 AM
Post #15

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
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
Go to the top of the page
 
+
Sup3rT3d
post Mar 3 2006, 11:12 AM
Post #16

UtterAccess Member
Posts: 23
i already have a list of dates in a table called "tblMonthDetails"

this is for another fuction, could i use this in this case?
Go to the top of the page
 
+
souLTower
post Mar 3 2006, 11:22 AM
Post #17

UtterAccess Addict
Posts: 126
From: Hagerstown, MD
You can.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 19th June 2013 - 07:19 PM