CODEWITHSUNDEEP
sqldatems-accessvbadate-range
Chris Boden
Chris Boden

Reputation: 219

Date range handling logic in Access

I've got a bit of a weird logic problem that I can't seem to wrap my head around (perhaps from studying it for too long).

Where I work we have a very old piece of software that we're required to use to track the status of equipment that we use. This software provides very little functionality to manipulate these statuses to try and provide a good analysis of downtime. I've been working on a database application in Access (since it's the only tool they make available to me) to import status data from the old system into a format that is more easily manipulated.

The way status data is spit out from the old program is fairly straight-forward:

EQUIPNAME STATUS STARTDATETIME ENDDATETIME

It's easy enough to read that text and insert it into the table in Access. The problem I'm having comes from trying to find how many hours a piece of equipment spent in different statuses over different date ranges.

The start/end date/time can be any length of time. Finding which rows contain the dates is difficult. I've been using BETWEEN statements in SQL to try and find them which, for the most part, works out well:

SELECT * FROM Statuses WHERE 
    (StartDateTime BETWEEN [StartDT] AND [EndDT]) 
OR 
    (EndDateTime BETWEEN [StartDT] AND [EndDT])

The real issue is when StartDateTime is BEFORE StartDT and EndDateTime is AFTER EndDT (ie the entire range I'm looking for is INSIDE this status's start/end dates). It simply doesn't find it, which makes sense.

I can't seem to come up with an elegant solution to this. I need to be able to select all rows which contain a status that contains or is contained within the supplied date range. I wouldn't normally come here for such a simple problem, but my brain and Google-fu are failing me.

A little bit of sample data:

EQUIP STATUS STARTDATETIME    ENDDATETIME
A123  OPER   01/30/2013 21:30 12/31/1999 00:00
A123  DFM    01/26/2013 10:42 01/30/2013 21:29
A123  OPER   01/01/2013 00:00 01/26/2013 10:41
B123  OPER   01/01/2013 00:00 12/31/1999 00:00
C123  DFU    01/29/2013 12:31 12/31/1999 00:00
C123  OPER   01/01/2013 00:00 01/29/2013 12:30

Upvotes: 1

Views: 866

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 48989

Any kind of booking collusion occurs when:

RequestStartDate <= EndDate 
and 
RequestEndDate >= StartDate

The above will ALSO return overlaps. So if I query today + tomorrow, and a range starts at the being of the year to the end of the year, the query WILL be included in the range.

Eg:

 Select * from tblEQUIP 
 where 
   #01/31/2013# <= ENDDATETIME
   and
   #02/01/2013# >= StartDateTime

At that point you can "process" each record. You likely have to use something like:

Do while RecordDate.eof = false

   For datePtr = RequestStartDateTime to RequestendDateTime
      If datePtr >= RecordData!StartDateTime and DatePtr <= RecordData!EndDateTime then
         DaysTotal = DaysTotal  + 1
      End if
   Next DatePtr
   recordData.Movenext
loop

The above is air code, but shows the basic processing loop you need to first grab the overlapping records, and then a processing loop to add up days/time for each record in your date range that does fall withing the given date range.

Upvotes: 2

iDevlop
iDevlop

Reputation: 25252

Interesting question ! Sorry not to have much time now to eleborate, but I would advise to explore the Partition function for that, or and/or doing a crosstab query with the date as column heading. More on msoffice site and here.

Upvotes: 0

Related Questions