Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Question Unanswered: Needing help to Include dates ranges that begin or end beyond a range

    I'm trying to create a query that sums the number of days a service has been provided between two dates. The problem is that some services start before the query Start Date and continue into the required time frame and others start within the range and extend beyond it and some have a start date but no finish date yet. I have no problems with the services that start and finish between the query [Start Date] and the query [End Date] but those that begin or finish beyond that range are not picked up even though part of the services are within the range of interest. This is the SQL to date and you can see the last OR statement where I have been trying unsuccessfully to get these dates picked up. Any help would be great please.
    I have included a sample date range and below is the problem SQl to date. I cannot get it to pick up periods that begin prior to the query date even though the range extends thru the query date range. Nor does it deal with empty Service End Dates very well.

    F_PlacementbyChildSubform F_PlacementbyChildSubform
    Service Start Date Service End Date

    22/12/2011 22/01/2012
    22/01/2012 24/01/2012
    24/01/2012 09/03/2012
    09/03/2012 11/03/2012
    11/03/2012 30/03/2012
    30/03/2012 02/04/2012
    02/04/2012

    T_Placements.ServiceStartDate, T_Placements.ServiceEndDate, Sum(([serviceEndDate])-([ServiceStartDate])) AS IndTotals ...............

    AND ((T_Placements.ServiceStartDate)=IIf([ServiceStartDate]>=[Start Date],[ServiceStartDate],[Start Date])) AND ((T_Placements.ServiceEndDate)=(IIf(([ServiceEndDate])<=[End Date],[End Date],[ServiceEndDate]))) AND ((T_ServiceType.ServiceType)="Standard Placement")) OR (((T_Placements.ServiceEndDate)=IIf("Nz[serviceEndDate]]",[End Date])))
    Last edited by oldteddybear; 03-30-12 at 01:21. Reason: Trying to simplify my query

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This is pure air code, but I would try something like this
    To do the Day callculation I would hve this
    Code:
    (IF([ServiceEndDate]>[End Date],[End Date],[ServiceEndDate]))-(IIF([ServiceStartDate]>[Start Date], [ServiceStartDate], [Start Date])) AS IndTotals FROM ...
    And in the WHERE Clause I would try this
    Code:
    WHERE [ServiceStartDate] BETWEEN [Start Date] and [End Date] AND [ServiceEndDate] BETWEEN [Start Date] and [End Date] AND [ServiceEndDate] and ....
    This would obviously need integrating with the WHERE conditions.

    Maybe you where clause already does this, but I don't have time to checkm it all.

    You also seem to be trying to cater for Null date, if so use the Nz() function

    As I said not sure if this will work, or work for you, but that is what I would try first.


    MTB

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Thanks but still getting bugs

    I have re written the query to include your suggestions. Have edited this post to reflect it more simply than before. Unfortunately I am still not picking up the dates correctly.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does this help?

    Overlapping records
    Paul

  5. #5
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Overlapping Records

    I think its late on a Friday and me brains getting fried.
    I have tried to reconstruct your example to my requirements without success. It seems that your example is trying to identify only an individual when the event enters the requested time frame whereas I am trying to identify the number of days an individual has accessed a service during a queried time. Or as first stated I have been looking so long I cant see the obvious

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Isn't the first step towards determining the number of days finding the records that qualify? You said "I cannot get it to pick up periods that begin prior to the query date even though the range extends thru the query date range", which I thought my method would do for you.

    I guess I'm not clear on exactly what the problem is.
    Paul

Posting Permissions

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