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 <--

 

[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?

 

[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 <--

 

[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 <--

 

[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
'====================================================

 

[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
'====================================================