Interesting Date/Time Calculation Query - Please Help! 
Author Message
 Interesting Date/Time Calculation Query - Please Help!

Hi!

I have an interesting problem regarding Date/Time calculations and
comparisons.

I am tasked with coding a routine to find if a date/time range falls
within another date/time range.  For example:  If a working day is
between 09:00 and 18:00, and there is an appointment from 13:00 to
14:00, I need to know if that "appointment" date falls within the
"working day" range.

Now then...I have managed to achieve this with a routine that's
included below, but I'm not convinced it's the best way to accomplish
such a task....  The thing is that this is only half of the problem.
Ultimately, I'll have a "main" date/time range (ie. 09:00 to 18:00)
and I'll have many different appointments within (or possibly outside)
of that range.  What I ultimately need to achieve is to show all the
ranges of time that are available (ie. there's no appointment made)

For example:

The "main" time window is 09:00 to 18:00..

There's an appointment at 10:00 to 10:30, another one from 11:30 to
13:00, and yet another from 14:00 to 15:30.

I need to process this data to return the "free time" result..
With the example above, the "free time" would be:

09:00 to 10:00 and
10:30 to 11:30 and
13:00 to 14:00 and
15:30 to 18:00.

Now, I have previously achieved this, but it was using logic similar
to that shown in my routine below, and it takes an AGE to calculate.
In these examples above, I've restricted the ranges to a day's worth
of hours.  In practice, these queries could span a week, a month, or
even several years!

My question would be, can anyone offer any insight into a better (ie.
faster) method of achieving such results.  Any and all help on this
problem is greatly appreciated.

Here's my "DateInDate" routine below. It works, but it's a very clumsy
way to determine if one date range falls within another one.
(For those not interested in examining the routine, I am essentially
splitting each date range into the minutes within that range, and by a
process of looping round within a loop (very time consuming!)
examining whether each specific minute matches (thus falling within
the range) or not)

----- Cut Here -----

Option Explicit

' #######################################################
' ## Function DateInDate
' ##
' ## Date  : 20 July 2000
' ##
' ## This function will find a set of TWO dates within
' ## another set of TWO dates.  The function returns TRUE
' ## if the "Schedule" date period falls within the "Main"
' ## date period, and will return "FALSE" if the "Schedule" date
' ## period is totally outside of the range of the "Main"
' ## date period.
' ##
' ## The function takes FIVE parameters, described as
' ## follows:
' ##
' ## pMainDateStart         (Type: DATE)
' ## pMainDa{*filter*}d           (Type: DATE)
' ## pScheduleDateStart     (Type: DATE)
' ## pScheduleDa{*filter*}d       (Type: DATE)
' ## pAllowStartEndOverlap  (Type: BOOLEAN)
' ##
' ## pMainDateStart and pMainDa{*filter*}d are the start and end
' ## dates/times of the main "window" of time that you wish
' ## to examine.
' ## pScheduleDateStart and pScheduleDa{*filter*}d is the expanse
' ## of time that you wish to determine wether or not it
' ## falls inside or outside of the range of the "Main" time
' ## "window".  The pAllowStartEndOverlap boolean variable
' ## is used if you wish to allow the Schedule Start/End
' ## date/time to coincide with the "Main" Start/End
' ## date/time.  This explanation is probably as clear as
' ## mud, so here's some examples:
' ##
' ## Example 1
' ## Main Start:        09:00
' ## Main End  :        17:00
' ## Schedule Start:    14:00
' ## Schedule End:      15:00
' ## Result:            The function will return TRUE.  This
' ##                    is irrespective of the
' ##                    bAllowStartEndOverlap value.
' ##
' ## Example 2
' ## Main Start:        09:00
' ## Main End  :        14:00
' ## Schedule Start:    14:00
' ## Schedule End:      15:00
' ## Result:            The function will return TRUE is the
' ##                    bAllowStartEndOverlap value is FALSE,
' ##                    but will return FALSE if the
' ##                    bAllowStartEndOverlap value is TRUE.
' ##

Public Function DateInDate(pMainDateStart As Date, pMainDa{*filter*}d As
Date, _
    pScheduleDateStart As Date, pScheduleDa{*filter*}d As Date, _
    pAllowStartEndOverlap As Boolean) As Boolean

Dim MainDate() As Date
Dim ScheduleDate() As Date
Dim lMainMinutes As Long
Dim lScheduleMinutes As Long
Dim lCounter As Long
Dim lCounter2 As Long

DateInDate = False

lMainMinutes = DateDiff("n", pMainDateStart, pMainDa{*filter*}d) + 1
lScheduleMinutes = DateDiff("n", pScheduleDateStart, pScheduleDa{*filter*}d)
+ 1

ReDim MainDate(lMainMinutes)
ReDim ScheduleDate(lScheduleMinutes)

For lCounter = 1 To lMainMinutes
    MainDate(lCounter) = DateAdd("n", (lCounter - 1), pMainDateStart)
Next lCounter

For lCounter = 1 To lScheduleMinutes
    ScheduleDate(lCounter) = DateAdd("n", (lCounter - 1),
pScheduleDateStart)
Next lCounter

For lCounter = 1 To lMainMinutes
    For lCounter2 = 1 To lScheduleMinutes
        If DateDiff("s", ScheduleDate(lCounter2), MainDate(lCounter))
= 0 Then
            If pAllowStartEndOverlap = True Then
                If (lCounter = 1) Or (lCounter = lMainMinutes) Then
                    ' Do Nothing!
                Else
                    If DateInDate = False Then
                        DateInDate = True
                    End If
                End If
            Else
                If DateInDate = False Then
                    DateInDate = True
                End If
            End If
        End If
    Next lCounter2
Next lCounter

End Function

----- Cut Here -----

Once again, any and all help on this problem is greatly appreciated.
I'm almost certain there's a MUCH better and quicker way to achieve
this, but my brain is frazzled enough by this sticky little problem!

Thanks again...

Best Regards,
Craig.



Mon, 06 Jan 2003 03:00:00 GMT  
 Interesting Date/Time Calculation Query - Please Help!
Give this function a try -- if I understand your request correctly, I
*think* it does what you are asking (your example looks like it misstated
how to use the bAllowStartEndOverlap argument):

Function DateInDate(pMainDateStart As Date, _
                                pMainDa{*filter*}d As Date, _
                                pScheduleDateStart As Date, _
                                pScheduleDa{*filter*}d As Date, _
                                pAllowStartEndOverlap As Boolean) _
                               As Boolean
  If pScheduleDateStart > pMainDateStart And _
         pScheduleDa{*filter*}d < pMainDa{*filter*}d Then
     DateInDate = True
  ElseIf pAllowStartEndOverlap And _
         ((pScheduleDateStart >= pMainDateStart And _
           pScheduleDateStart <= pMainDa{*filter*}d) Or _
          (pScheduleDa{*filter*}d >= pMainDateStart And _
           pScheduleDa{*filter*}d <= pMainDa{*filter*}d)) Then
     DateInDate = True
  End If
End Function

Rick


Quote:
> Hi!

> I have an interesting problem regarding Date/Time calculations and
> comparisons.

> I am tasked with coding a routine to find if a date/time range falls
> within another date/time range.  For example:  If a working day is
> between 09:00 and 18:00, and there is an appointment from 13:00 to
> 14:00, I need to know if that "appointment" date falls within the
> "working day" range.

> Now then...I have managed to achieve this with a routine that's
> included below, but I'm not convinced it's the best way to accomplish
> such a task....  The thing is that this is only half of the problem.
> Ultimately, I'll have a "main" date/time range (ie. 09:00 to 18:00)
> and I'll have many different appointments within (or possibly outside)
> of that range.  What I ultimately need to achieve is to show all the
> ranges of time that are available (ie. there's no appointment made)

> For example:

> The "main" time window is 09:00 to 18:00..

> There's an appointment at 10:00 to 10:30, another one from 11:30 to
> 13:00, and yet another from 14:00 to 15:30.

> I need to process this data to return the "free time" result..
> With the example above, the "free time" would be:

> 09:00 to 10:00 and
> 10:30 to 11:30 and
> 13:00 to 14:00 and
> 15:30 to 18:00.

> Now, I have previously achieved this, but it was using logic similar
> to that shown in my routine below, and it takes an AGE to calculate.
> In these examples above, I've restricted the ranges to a day's worth
> of hours.  In practice, these queries could span a week, a month, or
> even several years!

> My question would be, can anyone offer any insight into a better (ie.
> faster) method of achieving such results.  Any and all help on this
> problem is greatly appreciated.

> Here's my "DateInDate" routine below. It works, but it's a very clumsy
> way to determine if one date range falls within another one.
> (For those not interested in examining the routine, I am essentially
> splitting each date range into the minutes within that range, and by a
> process of looping round within a loop (very time consuming!)
> examining whether each specific minute matches (thus falling within
> the range) or not)

> ----- Cut Here -----

> Option Explicit

> ' #######################################################
> ' ## Function DateInDate
> ' ##
> ' ## Date  : 20 July 2000
> ' ##
> ' ## This function will find a set of TWO dates within
> ' ## another set of TWO dates.  The function returns TRUE
> ' ## if the "Schedule" date period falls within the "Main"
> ' ## date period, and will return "FALSE" if the "Schedule" date
> ' ## period is totally outside of the range of the "Main"
> ' ## date period.
> ' ##
> ' ## The function takes FIVE parameters, described as
> ' ## follows:
> ' ##
> ' ## pMainDateStart         (Type: DATE)
> ' ## pMainDa{*filter*}d           (Type: DATE)
> ' ## pScheduleDateStart     (Type: DATE)
> ' ## pScheduleDa{*filter*}d       (Type: DATE)
> ' ## pAllowStartEndOverlap  (Type: BOOLEAN)
> ' ##
> ' ## pMainDateStart and pMainDa{*filter*}d are the start and end
> ' ## dates/times of the main "window" of time that you wish
> ' ## to examine.
> ' ## pScheduleDateStart and pScheduleDa{*filter*}d is the expanse
> ' ## of time that you wish to determine wether or not it
> ' ## falls inside or outside of the range of the "Main" time
> ' ## "window".  The pAllowStartEndOverlap boolean variable
> ' ## is used if you wish to allow the Schedule Start/End
> ' ## date/time to coincide with the "Main" Start/End
> ' ## date/time.  This explanation is probably as clear as
> ' ## mud, so here's some examples:
> ' ##
> ' ## Example 1
> ' ## Main Start:        09:00
> ' ## Main End  :        17:00
> ' ## Schedule Start:    14:00
> ' ## Schedule End:      15:00
> ' ## Result:            The function will return TRUE.  This
> ' ##                    is irrespective of the
> ' ##                    bAllowStartEndOverlap value.
> ' ##
> ' ## Example 2
> ' ## Main Start:        09:00
> ' ## Main End  :        14:00
> ' ## Schedule Start:    14:00
> ' ## Schedule End:      15:00
> ' ## Result:            The function will return TRUE is the
> ' ##                    bAllowStartEndOverlap value is FALSE,
> ' ##                    but will return FALSE if the
> ' ##                    bAllowStartEndOverlap value is TRUE.
> ' ##

> Public Function DateInDate(pMainDateStart As Date, pMainDa{*filter*}d As
> Date, _
>     pScheduleDateStart As Date, pScheduleDa{*filter*}d As Date, _
>     pAllowStartEndOverlap As Boolean) As Boolean

> Dim MainDate() As Date
> Dim ScheduleDate() As Date
> Dim lMainMinutes As Long
> Dim lScheduleMinutes As Long
> Dim lCounter As Long
> Dim lCounter2 As Long

> DateInDate = False

> lMainMinutes = DateDiff("n", pMainDateStart, pMainDa{*filter*}d) + 1
> lScheduleMinutes = DateDiff("n", pScheduleDateStart, pScheduleDa{*filter*}d)
> + 1

> ReDim MainDate(lMainMinutes)
> ReDim ScheduleDate(lScheduleMinutes)

> For lCounter = 1 To lMainMinutes
>     MainDate(lCounter) = DateAdd("n", (lCounter - 1), pMainDateStart)
> Next lCounter

> For lCounter = 1 To lScheduleMinutes
>     ScheduleDate(lCounter) = DateAdd("n", (lCounter - 1),
> pScheduleDateStart)
> Next lCounter

> For lCounter = 1 To lMainMinutes
>     For lCounter2 = 1 To lScheduleMinutes
>         If DateDiff("s", ScheduleDate(lCounter2), MainDate(lCounter))
> = 0 Then
>             If pAllowStartEndOverlap = True Then
>                 If (lCounter = 1) Or (lCounter = lMainMinutes) Then
>                     ' Do Nothing!
>                 Else
>                     If DateInDate = False Then
>                         DateInDate = True
>                     End If
>                 End If
>             Else
>                 If DateInDate = False Then
>                     DateInDate = True
>                 End If
>             End If
>         End If
>     Next lCounter2
> Next lCounter

> End Function

> ----- Cut Here -----

> Once again, any and all help on this problem is greatly appreciated.
> I'm almost certain there's a MUCH better and quicker way to achieve
> this, but my brain is frazzled enough by this sticky little problem!

> Thanks again...

> Best Regards,
> Craig.



Tue, 07 Jan 2003 03:00:00 GMT  
 Interesting Date/Time Calculation Query - Please Help!
On Fri, 21 Jul 2000 01:10:07 GMT, "Rick Rothstein"

Quote:

>Give this function a try -- if I understand your request correctly, I
>*think* it does what you are asking (your example looks like it misstated
>how to use the bAllowStartEndOverlap argument):

>Function DateInDate(pMainDateStart As Date, _
>                                pMainDa{*filter*}d As Date, _
>                                pScheduleDateStart As Date, _
>                                pScheduleDa{*filter*}d As Date, _
>                                pAllowStartEndOverlap As Boolean) _
>                               As Boolean
>  If pScheduleDateStart > pMainDateStart And _
>         pScheduleDa{*filter*}d < pMainDa{*filter*}d Then
>     DateInDate = True
>  ElseIf pAllowStartEndOverlap And _
>         ((pScheduleDateStart >= pMainDateStart And _
>           pScheduleDateStart <= pMainDa{*filter*}d) Or _
>          (pScheduleDa{*filter*}d >= pMainDateStart And _
>           pScheduleDa{*filter*}d <= pMainDa{*filter*}d)) Then
>     DateInDate = True
>  End If
>End Function

>Rick



>> Hi!

>> I have an interesting problem regarding Date/Time calculations and
>> comparisons.

>> [SNIP!]

Rick,

Thanks for your routine.  This was how I first started to implement my
routine, however I got caught out with VB's inherent problem when
using arithmetic operators to compare dates/times..  If you're unaware
of this, please see article "Q194894 PRB: Date Comparison Can fail
Using Arithmetic Operators" in the Microsoft Knowledge Base.

I'm currently looking at some code that was posted by Larry Serflaten
in microsoft.public.vb.general.discussion which shows an entirely
different methodology for tackling such a task.  It's much quicker,
too!  I appreciate your efforts in regard to this problem, so thanks
again.

Best Regards,
Craig.



Tue, 07 Jan 2003 03:00:00 GMT  
 Interesting Date/Time Calculation Query - Please Help!
If I understand it correctly - you want to find out whether a Date +
Time would overlap with another :

This is how I would do it :

    1) Build a string of :
             Chr$(Year) + Chr$(Month) + Chr$(Day)
             + Chr$(StartHour) + Chr$(StartMin) + "B"
        Another string of :
             Chr$(Year) + Chr$(Month) + Chr$(Day)
             + Chr$(EndHour) + Chr$(EndMin) + "E"

    2) Build a sorted array of appointments
        (Start and End records for each appointment)  

    3) Binary chop for the position of the 'new appointment'
        ie: find its insertion position
        If the appointment before is a "B" type then it is booked
        If the appointment before is an "E" type then check the
        new appointment's "E" type to make sure it is less than
        the next record - or the next record is UBound( Array )

I would be inclined to keep this 'Array' on disk

You could 'block out' non working hours by having 'Time Off' records
ie:  "Ba"  "Bo"   "Ea"  "Eo"    {a}tive {o}ff

I would be very wary of using any locale specific routines.

The machines locale is the responsibility of the user - but who says
that users are responsible.

I also realize that using two records could be reduced to using one
record - but you'd not save much - and it increases the complexity of
the system.

You could also use Julian/Gregorians - but the same thing applies.

I would also 'wrap' the encoding/decoding of dates into a class.

I hope I haven't missed the point completely .....

Good Luck - Jerry



Wed, 08 Jan 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Interesting Date/Time Calculation Query - Please Help!

2. Date Calculation help , please

3. Date Calculation Help Please

4. Help please, Age Calculation Query

5. Date - Date/time comparison - please help

6. Help with interest calculations

7. Date and Time Calculations

8. Date-Time Calculations in VB4

9. date/time calculations

10. Date/Time Calculations

11. Date/Time calculation issues...

12. Date Time Field Calculations

 

 
Powered by phpBB® Forum Software