Gathering entries that spread over dates ranges

[oldteddybear]

Can anyone assist please? I cannot seem to get this query to work as I would like.
I have three tables being ServiceStartDate, ServiceEndDate and Service Type.
Clients use the service for periods of time and I need to run a query that identifies what clients have accessed the service and for how long over a specified period of time. I can identify periods of time within the query range however for those that begin prior and run into the time frame and those that run out at the other end I cannot get the query to identify them. To add to this some of the queries that extend past the specific range do not yet have an end date but still need to be caught for the period of time they use the service within the query range.

So I have reduced the query SQL at this point to the following: (I have coloured the text Blue where I feel the problem lies.

SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_ServiceType INNER JOIN (T_OrganisationPickList INNER JOIN (T_EnterClientDemographics INNER JOIN T_Placements ON T_EnterClientDemographics.ClientID = T_Placements.ClientID) ON (T_OrganisationPickList.OrgID = T_Placements.OrgID) AND (T_OrganisationPickList.OrgID = T_EnterClientDemographics.RefOrg)) ON T_ServiceType.ServiceTypeID = T_Placements.ServiceTypeID
GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate, T_ServiceType.ServiceType
HAVING (((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.ServiceStartDate)=IsNull([ServiceStartDate]>=[End Date])) AND ((T_ServiceType.ServiceType)="Standard Placement"));

 

[sparks80]

If I understand your question correctly you are looking to determine the number of days that two date ranges overlap by.

I suggest you search the internet for something like "number days two date ranges intersect"

 

[jdraw]

Your post is not clear. I do not understand exactly what you are trying to do, but I would make the following suggestion for you to consider.
Instead of three tables for ServiceStart, ServiceEnd and ServiceType, think of a Services Table with fields

ID (Primarykey)
ServiceStartDate
ServiceEndDate
ServiceType

and it seems there are other entities (Things) involved in what you are doing that have not been stated. Please clarify as you see fit.

 

[oldteddybear]

I really appreciate the help so as I Hope to clarify my problem. The SQL above is what remained after I dumped the rest when trying to simplify this query in a test database. So what I am after is: Lets say I have 5 entries in the data base and my query asks to show all entries between 01/03/12 and 31/03/12

ServiceStartDate Service EndDate My Expectations
02/02/12 10/02/12 Not Shown
07/02/12 05/03/12 Shows 4 days from 1st to 5th March
04/03/12 15/03/12 Shows 11 days from 4th to 15th
25/03/12 No end Date Entered Shows 6 days from 25th to 31st
03/04/12 No End Date entered Not Shown

 

[jdraw]

Has this been resolved?

 

[oldteddybear]

No not yet. Any suggestions would be appreciated. The relevant SQL has now been altered to:
SELECT T_Placements.PlacementNumber, T_Placements.ClientID, T_Placements.ServiceStartDate, T_Placements.ServiceEndDate, nz([ServiceEndDate],[End Date]) AS EndDateIfNull, T_Placements.ServiceStartDate
FROM T_Placements
WHERE (((nz([ServiceEndDate],[End Date]))>=[Start Date]) AND ((T_Placements.ServiceStartDate)<=[End Date]));

From here I think that
That the calculation then needs to look at the {ServiceStartDates} and if they are before the [Start Date] with a < [EndDateIfNull] entry the ServiceStartDate needs ammending to the [Start Date] in a new field called[Final Start Date]. This would then allow a calculation to follow where the [Final Start Date] is subtracted from the [EndDateIfNull] field to give the relevant days of service for each field thru the query time frame. Exactly what this will look like in a SQL has me puzzled and needing another coffee or two.