|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-10 : 08:20:33
|
| Mark writes "Background: we're a brokerage firm for an insurance company.Requirement: running an aging report on our earned commissionsScenario:Once a month, we receive a CSV file from the insurance carrier listing, line-by-line each case that they are paying us for that month. Usually, we will be paid twice for each case, as the participant pays bi-monthly. The CSV contains the following columns, among others:Subscriber SSNSubscriber NameEffective DatePaid To DateCommission EarnedStatement NameAs an example, John Doe (111-11-1111) with an effective date of 1/1/2004 might look like this on the January statement:111111111,John Doe,01012004,01312004,10.00,Jan04111111111,John Doe,01012004,02282004,10.00,Jan04John would not show up on the February statement, as he's already paid through the end of February. On March's statement, though, he'll look like:111111111,John Doe,01012004,03312004,10.00,Mar04111111111,John Doe,01012004,04302004,10.00,Mar04Again, he would not be on April's statement, and we would expect to see him on May's statement. But John ran into some difficulty, and paid late, so he doesn't show up on May but does show up on June:111111111,John Doe,01012004,05302004,10.00,Jun04111111111,John Doe,01012004,05312004,10.00,Jun04111111111,John Doe,01012004,06302004,10.00,Jun04I need to be able to run an aging report to show who's current within 30 days, 60 days, 90 days, and 120 days (and who's over 120 days). A For...Each loop would work nicely (look at each distinct SSN, loop through to find the latest paid-to date, calculate the difference between that date and the current date, and return the difference as a variable) but I don't know how to do that in SQL. Ideally, I would like to call a stored procedure, supplying a date to use to calculate the report, and get a result set that has one row for each client, including how many days behind in payment they are.Thank you!Mark Lam" |
|
Creating Aging Report