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.
Service Start Date Service End Date
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
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
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.