Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: Multiple BEWEEN DATA query

    Hi Guys

    Sorry about this but I posted a question like this before and didnt get to the bottom of the thing at the end of it all.

    I have a table [period dates] which contains 2 fields. [fromdate] and [Todate].

    I have to set up a way of querying a 2nd table which has starting and termination dates in 2 seperate fields. this will be done using a between function. The final outcome is that I will have 12 periods, each with a from and to date and the query will report people starting between each date and by each period. (hope this is clear guys)

    I pretty certain that the best or only way to do this is to use some code. This is my down fall. I havnt a clue how to write a piece of code that cycles through these 12 from - to period dates and reports people starting between the relevant start end dates.

    Anyone have an example - not to do it just an example will do of how something like this is done

    Many thanks

    Gareth

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are storing your period end dates in a table then you should be able to use SQL to find the period that someone stared in.

    it may not be very efficient but something like

    select DTEmp.empID, DTperiod.PeriodID for DT employee where DTEmp.EmpStartDate between DTPeriod.StartDate and DTPeriod.EndDate;


    remember you will need to index your period end dates
    you dont' actually need to store a start AND end date for each period.


    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Many thanks for that Ill look at it, give it a try and see where we get to

    Gareth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •