3 Replies Latest reply on Aug 15, 2007 10:29 AM by cf_dev2

    Search Between Dates

    Redcarl
      This is confusing so I will do my best to explain.

      We have an app where employees sign out of the office. They supply there userID, LeaveDate, and ReturnDate and it is written into one record in SQL.

      There are also several searches for this data, one of which is to enter two dates and a userID and return whether that user is signed out between those two dates by looking at whether the LeaveDate is between the two search dates.

      The problem arose that if someone was signed out from the 8th to the 15th, and a search was done on them for the 11th to the 13th, it would not return anything since the LeaveDate (8th) did not fall between the two search dates.

      So now I am tasked with a way to fix this. Is there a search between two dates and determine if any date in that span is in the span of dates that the employee is signed out? Is it possible to, when the employee signs out, create a record for each date in the span of days they are gone? So if someone signed out from the 10th to the 12th, it would create three different entries.

      Any help is appreciated.
        • 1. Re: Search Between Dates
          Dan Bracuk Level 5
          <cfscript>
          Dates=ArrayNew(1);
          ThisDate = TheFirstDate;
          ctr = 1;
          while ThisDate lte TheLastDate {
          Dates[ctr] = ThisDate;
          ThisDate = DateAdd("d", 1, ThisDate);
          ctr = ctr +1;
          }
          NumberOfDates = ArrayLen(Dates);
          <cfscript>

          <cfquery>
          insert into yourtable
          (employee_id, DateAway)
          <cfloop from = "1" to = "#NumberOfDates#" index = "ii">
          select distinct #employee_id#, #Dates[ii]
          from SomeSmallTable
          <cfif ii lt NumberOfDates>
          union
          </cfloop>
          </cfquery>
          • 2. Re: Search Between Dates
            cf_dev2 Level 1
            > return whether that user is signed out between those two
            > dates by looking at whether the LeaveDate is between the
            > two search dates

            Start by creating a few date examples that will cover all possibilities. Such as

            If the search dates are:

            Search Start = 11th
            Search End = 13th

            You want to include these cases

            1. Date1 is before Search Start ..AND.. Date2 is between Search Start and End
            Example: 6th to 13th

            2. Date1 is between Search Start and End ..AND...Date2 is after search End
            Example: 9th to 17th

            3. Date1 is between Search Start and End ..AND...Date2 is between Search Start and End
            Example: 7th to 12th

            4. Date1 is before Search Start ...AND...Date2 is after Search End
            Example: 5th to 21st

            Next translate those conditions to sql and test with some sample values. If your fields contain dates only the comparisons are simple. If they contain a date and time be sure to properly account for the time in your comparisons.

            There are better ways to write this but here is an example of how you might start translating the 4 conditions above. Once you've got the initial query working you can improve the sql.

            Psuedo-CF/SQL

            WHERE ( Date1 < #Start# AND Date2 BETWEEN #Start# AND #End# )
            OR ( Date1 BETWEEN #Start# AND #End# AND Date2 > #End# )
            OR ( Date1 BETWEEN #Start# AND #End# AND Date2 BETWEEN #Start# AND #End# )
            OR ( Date1 < #Start# AND Date2 > #End#)
            • 3. Re: Search Between Dates
              cf_dev2 Level 1
              If you don't need to do anything more advanced like displaying the start/end sign out dates, then the previous suggestion may be a better option.