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. Thanks, Ron