We get data from daily files that are bulk inserted into SQL Server and then parse into tables. One set of data represents 'accepted' billing data. The other represents 'rejected' billing data. There are various detail records for an account. Think of it like a phone compnay, and any time someone makes a long-distance call, a detail billing record is created. A person can make no calls on a day or 15, thus there will be a corresponding number of daily detail records.
Since acceptance is based on an all-or-nothing basis, if there is something wrong with the data that the billing system verifies data against, if it is going to reject one of those items, it will reject all of them for an account. Part of the process of importing this data is to create a 'summary' entry into a reject research table. If an account had any detail data rejected (regardless of how many), I create an entry into the reject research table, with the account number and the date of recect.
The following condition applies: If the account had data reject within the previous 7-days, I do not add a new row or update anything in the research table; the premise being, if it rejected within the past 7-days, it is under research and the reason for the error will be corrected, so we can just assume anything rejected within that time-frame is already under research and the resolution will be tied to the more recent rejected data and accepted.
The problem: We have realized that some accounts have not been reported as rejected. I have identified all accounts that NEVER made it into the reject research table, but now I need to identify the following scenario:
An account rejected on date (n). It rejected again on date (n + (8 + m) days), where m >= 0, but there is no record of that account in the reject research table.
So, if an account rejected on 9/1, and again on 9/7, I would expect to see the account with a date of 9/1 in the research table, but not the 9/7 date. However, I would expect to see it if there was a reject on 9/10.
Given the following scenario:
acct reject date
1234 8/10/2007
1234 8/11/2007
1234 8/12/2007
1234 8/19/2007
1234 8/22/2007
1234 9/10/2007
1234 9/20/2007
1234 9/24/2007
1234 9/28/2007
I would expect to find 8/10/2007, 8/19/2007, 9/10/2007, 9/20/2007 and 9/28/2007 in the research table. I need to produce a query that would report to me if, for example, 8/19/2007 was not located in the research table.
The comparision would be between ALL the detail data wihich contains all the individual detail records for the account, and the research table, which contains an account and the date it had rejects.
Account is a BIGINT datatype, and the reject date is DATETIME.
Start Free Trial
