Is a solution possible/viable?

[maxx233]

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....Timestamp..................Amount....Type
001.....2009-01-02 12:00:00.....$100.00....Promo
002.....2009-01-02 17:00:00.....$50.00......Debit
003.....2009-01-02 18:00:00.....$100.00....Credit
004.....2009-01-02 20:00:00.....$25.00......Debit
005.....2009-01-03 14:00:00.....$25.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

[Delerna]

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

Since you cannot change the database or the front end.

How about a DTS scheduled to run during offpeak times that does the some as the person would have done had they remembered?

[Uncle Dickie]

Would something like the following get what you are after?

It looks to be working on some data I have created as below.

I have assumed that there is also a CustomerID to be taken into account when working out the debits...

Type.......TimeStamp..................Amount...Cus tomerID....TranID
PROMO....02/01/2009 12:00:00....100........1............1
DEBIT......02/01/2009 17:00:00....50.........1............2
CREDIT... 02/01/2009 18:00:00....100........1............3
DEBIT......02/01/2009 20:00:00....25.........1............4
DEBIT......03/01/2009 14:00:00....25.........1............5
PROMO....02/01/2009 12:00:00....100........2............6
DEBIT......02/01/2009 17:00:00....50.........2............7
CREDIT....03/01/2009 01:00:00....100........2............8
DEBIT......03/01/2009 02:00:00....25.........2............9
DEBIT......03/01/2009 14:00:00....25.........2............10
PROMO....23/04/2009 12:00:00....150........1............11
DEBIT......23/04/2009 13:00:00....70.........1............12

SELECT        r.CustomerID
            ,r.TimeStamp                StartPromo
            ,DATEADD(hh,24,r.TimeStamp)    EndPromo
            ,r.TranID
            ,r.Amount PromoAmount
INTO        #temp1
FROM        dbo.R_Test r
WHERE        r.Type = 'PROMO'
 
 
 
SELECT        r.Type
            ,r.TimeStamp
            ,r.Amount
            ,r.CustomerID
            ,t.StartPromo
            ,t.EndPromo
            ,t.TranID
            ,t.PromoAmount
            ,CASE 
                WHEN    r.TimeStamp >= StartPromo AND r.TimeStamp <= EndPromo 
                THEN    CASE
                            WHEN r.Type = 'DEBIT'
                            THEN r.Amount 
                            ELSE 0
                        END
                ELSE    0
            END Spent
            ,CASE 
                WHEN    r.TimeStamp >= StartPromo AND r.TimeStamp <= EndPromo 
                THEN    CASE
                            WHEN r.Type = 'CREDIT'
                            THEN r.Amount 
                            ELSE 0
                        END
                ELSE    0
            END Purchased
INTO        #temp2
FROM        dbo.R_Test r
LEFT JOIN    #temp1 t ON t.CustomerID = r.CustomerID
 

[maxx233]

Uncle Dickie,
Maybe I'm a bit confused or not understanding what you're going for.. but what are you doing with #temp2 once you've got it? I get 19 rows after doing the LEFT JOIN with the above data, and after just SELECTING it, I'm not sure what your next step is to get a final result? I'm envisioning something that just states an end balance of the promo dollars, or that points out the TranIDs of Promos that still carry a balance, or something. Maybe my brain is just stuck in a rut from working on this so much and I'm trying to think of some way to SUM something and do funky mathiness and I'm missing something beyond obvious that you're doing/thinking/seeing? ;) Anywho, if I can get the thing working even halfway decent I swear I'll buy you lunch, it's drivin me nuts!

[Uncle Dickie]

Sorry!

Missed the most important part!!

SELECT        CustomerID
            ,TranID
            ,PromoAmount
            ,sum(Purchased)    Purchased
            ,sum(Spent)        Spent
            ,CASE
                WHEN PromoAmount > sum(Spent)
                THEN PromoAmount - sum(Spent)
                ELSE 0
            END DebitAccountBy
FROM        #temp2
GROUP BY    CustomerID
            ,TranID
            ,PromoAmount
 
 
DROP TABLE    #temp1
DROP TABLE    #temp2
 

I don't think it is a very elegant solution but the overall concept takes every promo (#temp1) for a customer and adds its start and end date along with the value to the original data (so #temp2 could be much larger than the original table)

From this it compares the start and end dates with the other transaction start and end dates, zeroing out values that do not fall within the date range.

The final query then just sums those grouping by the promo code so you will see more than one line for each customer if they have had more than one promo.

[maxx233]

Holy cowcheese... I think I have it working! :) My actual database and the data therein threw in several other obstacles, but I was able to work those out myself. All elegance aside, you definitely helped - and it seems to be doing what I need it to now! Which is very much appreciated, I've been banging my head on this one for way too long. Drop me a PM and I'll buy you lunch as promised! Thanks a ton!

[Uncle Dickie]

No problem at all - it's a pleasure to help someone on here rather than my usual capacity of grovelling for assistance!

Glad you got it working!