[dba-SQLServer] Problem Formulating Query

chizotz at mchsi.com chizotz at mchsi.com
Thu Nov 3 18:06:07 CST 2005

I'm having problems formulating a query. Maybe I'm just tired or having a bad 
brain day, but...

I have a table of accounts and associated data including an account number, a 
route number, a start date, and a stop date. These four fields form a compound 
primary key. A route may be assigned at different times to different accounts, 
but never to more than one account at a time. Therefore you would see data 
similar to this:

Account    Route    StartDate    EndDate     OtherData ...
33333      1S01     01/01/2005   10/31/2005  blah blah blah
44444      1S01     11/01/2005   12/31/2999  blah blah blah
12345      1S02     01/01/2005   01/05/2005  blah blah blah
54321      1S02     01/06/2005   08/22/2005  blah blah blah
12345      1S02     08/23/2005   09/15/2005  blah blah blah
88888      1S02     09/17/2005   12/31/2999  blah blah blah

Please note that there is a day unaccounted for in the above data for route 
1S02, 09/16/2005. That is the condition I need to query for, to locate routes 
that were not assigned to an account and the dates they were unassigned. In 
theory, that should never happen but the dates are set manually in the app by 
the district reps and they sometimes error. The app checks for and disallows 
overlapping date ranges (i.e. a route can't be assigned to different accounts 
on the same date), but does not check for missing dates. Also, it is possible 
for a route to simply end and no longer be assigned to an account, so I'm only 
interested in routes with missing dates that have been assigned to accounts on 
later dates.

I also have a date dimension table available with all dates from before the 
data business data begins through 12/31/2999 which I have been trying to use 
in joins to reveal only those routes that were unassigned. So far I can't get 
it to work.

I hope I explained this clearly. Any help with this would be appreciated.



More information about the dba-SQLServer mailing list