Find a specific case

[Patrick]

Access 2000

Situation:
I have two devices, A and B, that I track the shutdown and startup times
for. At times, A might have shutoff and started up all the while B was
running, or B might have shutoff and started up several times and A might
have shutoff and started up a couple of times with A and B's shutdown periods
overlapping. These are just a couple of examples of cases. There could be any
imanginable combination of cases. I need to find and report, for a given six
month reporting period, each event where both device's shutdown periods
overlap for >= one hour.

Setup:
I have two tables:
1) Devices - with one field named: DeviceID, datatype = text, that stores
the names of the two devices (A & B), and
2) Shutdowns - with four fields named: ShutdownID, datatype = Autonumber;
DeviceID, datatype = text (setup as a combobox that refers back to the
Devices table); ShutdownTime, datatype = date/time; StartupTime, datatype =
date/time, this table records the shutdown and startup times for each device

Problem:
Not sure how to find the case where A and B's shutdown periods overlap for

= one hour.

Can I get the information I need using quearies, VBA or both with my current
setup? If so, how do I get started? Or, do I need to record additional
information and/or add tables?

 

[Stefan Hoffmann]

hi Patrick,

Can I get the information I need using quearies, VBA or both with my current
setup? If so, how do I get started? Or, do I need to record additional
information and/or add tables?

You can do it completely in SQL, but I would use a VBA helper function:

Public Function IsOverlapping(AStartup1 As Date, _
AShutdown1 As Date, _
AStartup2 As Date, _
AShutdown2 As Date _
) As Boolean
' do the date/time calculus

IsOverlapping = True

End Function

And start with this kind of query:

SELECT S1.*, S2.*
FROM Shutdowns AS S1, Shutdowns AS S2
WHERE S1.idDevice <> S2.idDevice
AND IsOverlapping(S1.Startup, S1.Shutdown, S2.Startup, S2.Shutdown);


mfG
--> stefan <--

 

[Patrick]

Stefan,

Please forgive me, I am new to access VBA and SQL. I want to make sure I
understand what you have proposed.

First, the query extracts the shutdown and startup info from the tables for
only thoses events where both device's shutdown periods overlap.
Then, the query passes that info in to the arguments in the IsOverlapping
function where the function determines whether the overlap period is >= one
hour based on the "date/time calculus" code that needs to be written.

Am I following you correctly?

 

[Stefan Hoffmann]

hi Patrick,

First, the query extracts the shutdown and startup info from the tables for
only thoses events where both device's shutdown periods overlap.

Not really.

The query creates a full (cross) join, it builds the cartesian product
with itself to create rows containing the information of two devices.
The conditional clause eliminates the trivial case of comparing a

Then, the query passes that info in to the arguments in the IsOverlapping
function where the function determines whether the overlap period is >= one
hour based on the "date/time calculus" code that needs to be written.

Correct.

One basic optimization you should consider: For an overlapping to occur,
the shutdown period of each device record must be larger or equal than
one hour. So I would use this query

SELECT S.*, DateDiff("n", S.Shutdown, S.Startup) AS Period
FROM Shutdowns S
WHERE DateDiff("n", S.Shutdown, S.Startup) >= 60

as base for the full join to reduce the data.


mfG
--> stefan <--

 

[JimBurke via AccessMonster.com]

That's pretty clever - I wouldn't have thought of the cartesian join, but it
looks like that would do the trick for getting all combinations of startup-
shutdown for each device. The IsOverlapping function is still needed, and I
think this would work. I've never used a user-defined function in SQL, so I
don't know if these can go in a form module or if they would have to go in a
code module. I also don't know if VBA has it's own minimum function (min
doesn't work) so I wrote a quick and dirty one for your purposes:

Public Function IsOverlapping(byval st1 as date, byval sh1 as date, _
byval st2 as date, byval sh2 as date) as boolean

if st1 <= st2 then
IsOverlapping = IsOverlapping2(st1, sh1, st2, sh2)
else
IsOverlapping = IsOverlapping2(st2, sh2, st1, sh1)
End If

End Function


Public Function IsOverlapping2(byval st1 as date, byval sh1 as date, _
byval st2 as date, byval sh2 as date) as boolean

if sh1 >= st2 then
If getmin(sh1, sh2) - st2 >= #1:00:00 AM# Then
IsOverlapping2 = True
Else
IsOverlapping2 = false
End If
Else
IsOverlapping22 = false
End If

End Function

Private Function getmin(ByVal val1 As Date, ByVal val2 As Date) As Date

If val1 <= val2 Then
getmin = val1
Else
getmin = val2
End If

End Function

If VBA does have a min function, you don't need that part - just replace the
call to getmin with VBA's min function name and don't code the getmin
function.

 

[John Spencer]

The following might work as a solution to your problem.

Q1:
SELECT DeviceID, ShutDownTime, StartUpTime
FROM Shutdowns
WHERE DeviceID = "A"

Q2:
SELECT DeviceID, ShutDownTime, StartUpTime
FROM Shutdowns
WHERE DeviceID = "B"

Any overlap over 60 (use saved queries Q1 and Q2):
SELECT *
FROM Q1 INNER JOIN Q2
ON Q1.ShutDownTime <= Q2.StartUpTime
AND Q1.StartUpTime >= Q2.ShutDownTime
WHERE DateDiff("n",Q1.ShutdownTime, IIF(Q1.StartupTime<Q2.StartUpTime,
Q1.StartUpTime,Q2.StartUpTime))>=60

You might be able to do this all in one query.
SELECT *
FROM
(SELECT DeviceID, ShutDownTime, StartUpTime
FROM Shutdowns
WHERE DeviceID = "A") AS Q1
INNER JOIN
(SELECT DeviceID, ShutDownTime, StartUpTime
FROM Shutdowns
WHERE DeviceID = "A") as Q2
ON Q1.ShutDownTime <= Q2.StartUpTime
AND Q1.StartUpTime >= Q2.ShutDownTime
WHERE DateDiff("n",Q1.ShutdownTime, IIF(Q1.StartupTime<Q2.StartUpTime,
Q1.StartUpTime,Q2.StartUpTime))>=60


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================