Date Range Different for Each Record

[njvsummit]

Sorry if this is simple, but I’m struggling. I have a table called services that contains service data (million+ records) and it has the following columns: ClientID, Service Date, and Service. I’ve been given a second table that contains the same ClientID and has two additional fields: Start Date and End Date. The table has 500+ records and the Start Date and End Date is different for each ClientID. What I need to do is only select records from the services table that have the same ClientID and falls between each individual client’s Start Date and End Date (the client may have services that are before and after the date range, and those should not be selected).

Any suggestions would be helpful

 

[SgtJarrow]

Personally, I would not say this is an easy question....it can be done, with a bit of VBA coding, but before we can continue, please elaborate a bit more.

What do you want to do? What is going to be done with the "selected" records? Are you date ranges inclusive (in other words, if you date is 5/27/04, do you wish to also include that date?)?

These few questions can help me help you find a solution to your problem.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[njvsummit]

Thanks for the quick response. But a colleague asked me to try the following:

SELECT client.ClientID, client.StartDate, client.EndDate, services.ServiceDate, services.Service

FROM client INNER JOIN services ON client.ClientID = services.ClientID

WHERE services.ServiceDate BETWEEN client.StartDate AND client.EndDate

And it seems to do the trick--it seems too simple...is he overlooking something? Another pair of eyes can never hurt.

Thanks

 

[SgtJarrow]

I would think that would work....but to be honest I am not an expert on this inner joins/second query sql statements. But I would recommend you verify the dates, as I know that sometimes "between somedate and anotherdate" does no include the end dates...you may need to adjust your endpoints to include those dates.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[willir]

I came up with...

SELECT S.ClientID, SD.StartDate, SD.EndDate, S.ServiceDate, S.Service
FROM ServicesTbl AS S, ServiceDatesTbl AS SD
WHERE S.ClientID = SD.ClientID and
S.ServiceDate BETWEEN
SD.StartDate
and
SD.EndDate
;

 

[BobJacksonNCI]

mstrmage1768,

BETWEEN has always worked just fine for me. But, I've learned to double-check the contents of date fields involved to see whether they contain dates only or dates and times.

If one field includes the time and the other doesn't, it can usually be resolved by using DateValue([fld_that_includes_time]) in the expression.



HTH,
Bob