I have a problem I've been butting my head up against for a while, and I really have no viable solution figured out yet :\ I would have designed the database a bit different, and certainly designed the user interface a lot different, but those two things are set in stone and cannot be altered unforetunately.
Here's the situation: Someone in marketing credits a customer $x (let's say $100 in this case), and that person has 24 hours to come in and use that credit. Problem is, if they don't, it doesn't *automatically* get expired from the system and disappear, someone has to go back and remove it manually with a negative credit for the amount they haven't used. And, you guessed it - people have forgotten to do that, and now I need to find the people with Promo credit that should have already expired!! But this is complicated by the fact that in the same table are credits that don't expire, along with all the correstponding debits that don't distinguish which type of credit they're being taken from. A simplified example would look like:
Tran....Timesta mp............. .....Amount.... Type
001.....2009-01-02 12:00:00.....$1 00.00....Promo
002.....2009-01-02 17:00:00.....$5 0.00......Debit
003.....2009-01-02 18:00:00.....$1 00.00....Credit
004.....2009-01-02 20:00:00.....$2 5.00......Debit
005.....2009-01-03 14:00:00.....$2 5.00......Debit
The above example should be flagged as having a balance of $25 still remaining on the Promo credit in Tran001 (because Debits within 24 hours from Tran001 total $75, which is less than the $100 promo credit, therefore Tran004 doesn't tap into the non-expiring credit from Tran003), and $75 still remaining on the non-expiring Credit from Tran003.
All I can can think of to get this figured out is with looping - but that's way too intensive I fear, because we have something like 50,000 customers, and most of them have several hundred transactions. Looking for a Tran# with type Promo, setting variables for the timestamp and balance, then SUMing the debits within 24 hours and subtracting from the balance, reporting any positive number returned.. and iterating through 50,000 customers, many of which are going to have several Promo credits... eek. I'm hoping there's a better way? All ideas are *greatly* appreciated!
maxx
Here's the situation: Someone in marketing credits a customer $x (let's say $100 in this case), and that person has 24 hours to come in and use that credit. Problem is, if they don't, it doesn't *automatically* get expired from the system and disappear, someone has to go back and remove it manually with a negative credit for the amount they haven't used. And, you guessed it - people have forgotten to do that, and now I need to find the people with Promo credit that should have already expired!! But this is complicated by the fact that in the same table are credits that don't expire, along with all the correstponding debits that don't distinguish which type of credit they're being taken from. A simplified example would look like:
Tran....Timesta mp............. .....Amount.... Type
001.....2009-01-02 12:00:00.....$1 00.00....Promo
002.....2009-01-02 17:00:00.....$5 0.00......Debit
003.....2009-01-02 18:00:00.....$1 00.00....Credit
004.....2009-01-02 20:00:00.....$2 5.00......Debit
005.....2009-01-03 14:00:00.....$2 5.00......Debit
The above example should be flagged as having a balance of $25 still remaining on the Promo credit in Tran001 (because Debits within 24 hours from Tran001 total $75, which is less than the $100 promo credit, therefore Tran004 doesn't tap into the non-expiring credit from Tran003), and $75 still remaining on the non-expiring Credit from Tran003.
All I can can think of to get this figured out is with looping - but that's way too intensive I fear, because we have something like 50,000 customers, and most of them have several hundred transactions. Looking for a Tran# with type Promo, setting variables for the timestamp and balance, then SUMing the debits within 24 hours and subtracting from the balance, reporting any positive number returned.. and iterating through 50,000 customers, many of which are going to have several Promo credits... eek. I'm hoping there's a better way? All ideas are *greatly* appreciated!
maxx
Comment