Days between dates.

[Juan]

I am trying to track equipment use. The equipment is
checked out for a length of time which I need to break
down into company 'periods'.

There are typically 5-6 "periods" during a calendar
year. Since the start date for a new period can vary, I
have a user enter in a "period date" at each period
change (for instance August 1 started a new period, so
Sarah entered 08/01/2003 into the Period table). The
Period table has an autonumber primary key and is mostly
just a list of period start dates.

An employee comes in to check out a piece of equipment
and Sarah checks it out to them (date it was checked out,
employee checking it out).

What is kind of screwy, is that they don't actually check
the equipment back in with Sarah. When someone else
needs it, they simply check it out under their name and
get the item from whomever had it checked out.

As an example: Frank Smith checks out laptop-201 from
Sarah on 02/10/2003. On 08/05/2003 Jane Doe needs the
laptop, so Sarah checks it out to her and Jane gets the
laptop from Frank.

What we are trying to track is how many days per period
the equipment was checked out to a certain department
(employee is linked to his/her dept). So in the above
example, Frank's department would have had the laptop
from 02/10/2003 to 08/05/2003. This would equate to part
of period one, all of periods two&three, and part of
period 4.

Anyone know an easy way to handle this?


Juan

 

[Sal Rosario]

Did you try using the DateDiff function in a query? That will tell you the
difference between two dates and you can have it show you the difference as
number of days.

Sal
www.cedrostec.com

 

[John Verhagen]

To make this easier on me, I've assumed some things on the 2 tables that
were used. Each table would have a begin and end date. Here are the
tables. The first is called Period, and has the following fields:

Period, begPeriodDate,endPeriodDate with data:
Period
begPeriodDate
endPeriodDate

20026
11/1/2002
1/1/2003

20031
1/1/2003
3/1/2003

20032
3/1/2003
5/1/2003

20033
5/1/2003
8/1/2003

20034
8/1/2003
11/1/2003

20035
11/1/2003
1/1/2004



The second is called Checkout, and has the following data:
Person
begDate
endDate
EquipmentNumber

Sarah

2/10/2003
201

Frank
2/10/2003
8/5/2003
201

Jane
8/5/2003

201

John
3/12/2003
6/18/2003
202

Frank
1/2/2003
3/12/2003
202



There are 2 functions that I used:
Function Minimum(p1 As Variant, p2 As Variant) As Variant
If p1 <= p2 Then
Minimum = p1
Else
Minimum = p2
End If
End Function
and
Function Maximum(p1 As Variant, p2 As Variant) As Variant
If p2 >= p1 Then
Maximum = p2
Else
Maximum = p1
End If
End Function

The query is:
SELECT Checkout.Person, Checkout.EquipmentNumber, Period.Period,
Maximum([begPeriodDate],[begDate]) AS Start,
Minimum([endPeriodDate],[endDate]) AS End,
Minimum([endPeriodDate],[endDate])-Maximum([begPeriodDate],[begDate]) AS
DaysInPeriod
FROM Checkout, Period
WHERE ((Not (Checkout.begDate) Is Null) AND (Not (Checkout.endDate) Is Null)
AND ((Period.begPeriodDate)<=[endDate] And (Period.begPeriodDate)>=(select
max(begPeriodDate) from Period where begPeriodDate<begDate)))
ORDER BY Checkout.Person, Checkout.EquipmentNumber, Period.Period;

and gives:
Person
EquipmentNumber
Period
Start
End
DaysInPeriod

Frank
201
20031
2/10/2003
3/1/2003
19

Frank
201
20032
3/1/2003
5/1/2003
61

Frank
201
20033
5/1/2003
8/1/2003
92

Frank
201
20034
8/1/2003
8/5/2003
4

Frank
202
20031
1/2/2003
3/1/2003
58

Frank
202
20032
3/1/2003
3/12/2003
11

John
202
20032
3/12/2003
5/1/2003
50

John
202
20033
5/1/2003
6/18/2003
48