Date difference for single field

[Guest]

Hi. I'd appreciate any help with this problem. I've read threads on this
before, but couldn't get it right. I want to calculate the difference
between dates in a single field to help calculate trap*nights.

Here's the table structure in the table called tblADateID:
ADateID (pk) Status ADate TrapsSet
1 Opened 3/13/06 2
2 Closed 3/15/06 2
3 Opened 4/1/06 3
4 Checked 4/15/06 2
5 Checked 4/16/06 4
6 Closed 4/17/06 4

I'm trying to get a query that will write this:
TrapsSet Checked PriorCheck Nights TrapNights
2 3/15/06 3/13/06 2 4
3 4/15/06 4/1/06 14 42
2 4/16/06 4/15/06 1 2
4 4/17/06 4/16/06 1 4

"Nights" is the number of nights between an open and a check, a check and a
check, a check and a closed, or an open and a closed- essentially everything
except nights between closing and opening. TrapNights is the number of traps
set previously times the number of nights in that interval.

Eventually, I'd like to supply date parameters to calculate TrapNights for a
given interval.

Thanks for any help.

ak

 

[Tom Ellison]

Dear Kastle:

What you can do is JOIN the table to itself. Create two subsets of the
table, one for "Opened" Status and the other for "Closed", then JOIN them
together:

SELECT T1.ADate, T2.ADate T1.TrapsSet
FROM (SELECT * FROM tblADateID
WHERE Status = "Opened") T1
INNER JOIN (SELECT * FROM tblADateID
WHERE Status = "Closed") T2
ON T2.TrapsSet = T1.TrapsSet

If this works, you can simply add a DateDiff() on T1.ADate and T2.ADate to
give you the result you want.

Did this help? What can I do to help you further?

Tom Ellison
Microsoft Access MVP

 

[Guest]

Thanks, Tom. I tried your SQL, and it seemed to do a Cartesian, so it
doesn't take the dates in order, from one date to the next date, but seems to
throw them all together in all possible combinations. Is there a way to go
from one date to the next?

 

[Tom Ellison]

Dear Kastle:

I had not correctly understood your requirements. Perhaps I do now. Here's
a query that matches your results from the data given:

SELECT T1.*, T2.*, DateDiff("d",[T1].[ADate],[T2].[ADate]) AS Diff,
DateDiff("d",[T1].[ADate],[T2].[ADate])*[T1].[TrapsSet] AS TrapNights
FROM tblADateID AS T1, tblADateID AS T2
WHERE T1.Status <> "Closed" AND T2.Status <> "Opened"
AND T2.ADate = (SELECT MIN(ADate) FROM tblADateID T3
WHERE T3.ADate > T1.ADate);

Perhaps that's what you were wanting.

I believe this imposes some requirements on the way data is entered. In
sequential order, there must be an "Opened", then any number of "Checked"
rows, then a single "Closed". Only the most recent set may have be without
a "Closed". I'm not sure this is what you have in mind, or how you are
going to enforce that, but it would seem to be a necessity.

You might want to have a column which might be called "SetName" which has
the same value for all the rows within a set. Each set would be required to
start with an "Opened" row and would remain "open" until a "closed" row is
added. If you had this, you could then have multiple sets open at any date,
and sets could overlap in time. Don't know what the physical realities of
your system are, but this could be useful.

Tom Ellison