I have a table, policies, which looks like this:
Customers may have multiple policies, so we might have the following in our table:
I need a select which returns the customer ids that had a policy in force between two dates *without any gaps in coverage*.
So, using 2008-01-01 and 2008-12-31 as parameters to the select, only the 3rd record will come back, because he is the only customer who had a policy in effect for every day throughout 2008.
Let's say that the 2nd row had a start date of 2008-06-01 instead of 2008-06-05, he would also get returned, because although he doesn't have a single policy that covers all of 2008, between rows 1 & 2, he does.
Now, here's the kicker, this application is using SQL Server CE 3.5 - which does not support subselect.
Is there another way or am I forced to pull all the rows and calculate it in code?
This is for a report on all customers - doing multiple queries per customer is not an option for performance reasons.