By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Is a solution possible/viable?

maxx233
P: 32
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
Mar 23 '09 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
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?
Mar 24 '09 #2

Uncle Dickie
P: 67
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


Expand|Select|Wrap|Line Numbers
  1. SELECT        r.CustomerID
  2.             ,r.TimeStamp                StartPromo
  3.             ,DATEADD(hh,24,r.TimeStamp)    EndPromo
  4.             ,r.TranID
  5.             ,r.Amount PromoAmount
  6. INTO        #temp1
  7. FROM        dbo.R_Test r
  8. WHERE        r.Type = 'PROMO'
  9.  
  10.  
  11.  
  12. SELECT        r.Type
  13.             ,r.TimeStamp
  14.             ,r.Amount
  15.             ,r.CustomerID
  16.             ,t.StartPromo
  17.             ,t.EndPromo
  18.             ,t.TranID
  19.             ,t.PromoAmount
  20.             ,CASE 
  21.                 WHEN    r.TimeStamp >= StartPromo AND r.TimeStamp <= EndPromo 
  22.                 THEN    CASE
  23.                             WHEN r.Type = 'DEBIT'
  24.                             THEN r.Amount 
  25.                             ELSE 0
  26.                         END
  27.                 ELSE    0
  28.             END Spent
  29.             ,CASE 
  30.                 WHEN    r.TimeStamp >= StartPromo AND r.TimeStamp <= EndPromo 
  31.                 THEN    CASE
  32.                             WHEN r.Type = 'CREDIT'
  33.                             THEN r.Amount 
  34.                             ELSE 0
  35.                         END
  36.                 ELSE    0
  37.             END Purchased
  38. INTO        #temp2
  39. FROM        dbo.R_Test r
  40. LEFT JOIN    #temp1 t ON t.CustomerID = r.CustomerID
  41.  
Mar 24 '09 #3

maxx233
P: 32
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!
Mar 25 '09 #4

Uncle Dickie
P: 67
Sorry!

Missed the most important part!!


Expand|Select|Wrap|Line Numbers
  1. SELECT        CustomerID
  2.             ,TranID
  3.             ,PromoAmount
  4.             ,sum(Purchased)    Purchased
  5.             ,sum(Spent)        Spent
  6.             ,CASE
  7.                 WHEN PromoAmount > sum(Spent)
  8.                 THEN PromoAmount - sum(Spent)
  9.                 ELSE 0
  10.             END DebitAccountBy
  11. FROM        #temp2
  12. GROUP BY    CustomerID
  13.             ,TranID
  14.             ,PromoAmount
  15.  
  16.  
  17. DROP TABLE    #temp1
  18. DROP TABLE    #temp2
  19.  
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.
Mar 25 '09 #5

maxx233
P: 32
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!
Mar 25 '09 #6

Uncle Dickie
P: 67
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!
Mar 26 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.