If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating total days minus overlapp
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. |
#2
|
|||
|
|||
Calculating total days minus overlapp
I did not follow your information - how can a second person checkout a file
on 15 Jan 06 if the first person did not return the file until 30 Mar 06? " wrote: 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. |
#3
|
|||
|
|||
Calculating total days minus overlapp
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! |
#4
|
|||
|
|||
Calculating total days minus overlapp
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; " wrote: 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! |
#5
|
|||
|
|||
Calculating total days minus overlapp
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([b],Date())) + DateDiff("d",IIF(Nz([b],Date())Nz([C],Date()), Nz([b],Date()),[C]),IIF(Nz([b],Date()) ...)) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. wrote in message oups.com... 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. |
Thread Tools | |
Display Modes | |
|
|