Calculating total days minus overlapp

[jasonjrowe]

Hello,
I have to create a query which will return the total # days a
file has been checked out. The table is set up for two "checkouts"
that can occur in succession, concurrently, or be on-going. For
example:

Checkoutdate1: 1 Jan 06
ReturnDate1: 30 Mar 06

Checkoutdate2: 15 Jan 06
ReturnDate2: 30 Mar 06

I cannot simply subtract the dates and add them together, becasue I
only need the non-overlapping time. In this case it would be 89 days.

Complicating the matter is when I leave a return date blank, or there
is no 2nd Checkout. For example:

Checkoutdate1: 1Jan 06
ReturnDate1: (Null)

Checkoutdate2: 5 Jan 06
Checkoutdate2: 30 Mar 06

Of Course, there are numerous other variables with this situation.


Any suggestions would be greatly appreciated! Thank you.

 

[Guest]

I did not follow your information - how can a second person checkout a file

 

[jasonjrowe]

Thanks for the reply. The file checked out is always a copy, so you
can have two checked out at the same time. To better explain, think
of each file as a "Problem". To solve the problem, we ask other
departments for their inputs. While they are working on the
"problem", we are not. My boss wants to calculate how long it takes
us to solve the "problem". Thus, we must subtract the total time in
days that the file or "problem" has been out of our hands. Simply
adding up the the two total checkout times does not solve the issue
due to overlapping days. Hopefully this explains it better. Thanks
again!

 

[Guest]

Try this, replacing Jason as table name with your table name. and CaseID with
your field name.

SELECT Jason.CaseID, Min(Jason.Checkoutdate) AS Out, Max(IIf([ReturnDate] Is
Null,Date(),[ReturnDate])) AS Return,
DateDiff("d",Min([Checkoutdate]),Max(IIf([ReturnDate] Is
Null,Date(),[ReturnDate]))) AS Days
FROM Jason
GROUP BY Jason.CaseID;

 

[John Spencer]

If I understand correctly your table has four fields CheckOutDate1 (A),
CheckoutDate2 (C), ReturnDate1 (B), and ReturnDate2 (D)

You have mutilple scenarios - dates are filled as follows
A, B, C, and D are all blank
A is filled, B, C, D are blank
A and B are filled, C and D are blank
A, B, and C are filled, D is blank.
A, B, C, and D are filled

A to B can overlap C to D
A to B does not overlap C to D

Nicely complicated and I probably would write a VBA function to handle it.
Air code for that might look something like

Function GetDayCount (A, B, C, D) as integer
Dim dtA as Date, dtB as Date, dtC as Date, dtD as Date

If IsDate(A) = False and IsDate(B) = False and IsDate(C) = False and
IsDate(D) = False then
GetDayCount = 0
Else
dtA = NZ(A,Date())
dtB =NZ(B,Date())
dtC=Nz(C,Date())
dtD=Nz(D,Date())

'Fix C and D if they overlap A to B
If dtB > dtC then dtC = dtB
If dtB > dtD then dtD = dtB

GetDayCount = DateDiff("d",dtA,dtB) + DateDiff("d",dtC,dtD)
End if

If that gives you correct results you could do that with a complicated
expression in a query, the start of which should look something like

DateDiff("d",[A], Nz(,Date())) +
DateDiff("d",IIF(Nz(,Date())>Nz([C],Date()),
Nz(,Date()),[C]),IIF(Nz(,Date())> ...))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..