Credit Voucher System With Expiry Periods

[PommyTom]

Hi,

Wondering if anyone can help me overcome this curious problem... I suppose its both a maths problem and SQL problem... If anyone can help with the Maths OR the SQL would be very helpful!

Synopsis:
Capture the value of the current 'credit' of a user. Similar to that of a phone provider in a pre-paid voucher system. However, 'free' vouchers are given each month.

3 tables :
User
Credit Transactions (date, expiry_date, amount)
Messages Sent (date, amount/cost)

Processes:

1. User logs in
2. Credit table is checked to see if 'free voucher' has been added for the month.
3. If not - then add (adds a positive entry to credit table with expiry set to end of month)
4. Calculate the current credit of the user (THIS IS THE PROBLEM)
5. Display on screen.
6. If positive - Display option to send a message (which adds a deduction entry to the credit table)
7. If not - allow them to get a voucher (which adds a positive entry to credit table)

Ok so everything works pretty well...

But it gets pretty complicated with expiry periods on each voucher. I'm struggling to work out the current value correctly with each voucher having varying expiry dates.

It would be very easy if only monthly vouchers, i could capture a total amount spent in a month and deduct from the current monthly voucher. But now with adding 'purchased' vouchers with varying expiry dates its getting pretty tricky.

eg.
1st Voucher = 30 days
2nd = 90 days

I tried to work it out as:
Positives = Sum(CR) over all Time
Negatives = Sum(CR) over all time that is EXPIRED - AND - Sum(DR) Over all time

Total Credit:

Select SUM(Amount) as TotalCredits FROM msg_credittranslog_type WHERE msg_credittranslog_type = 'CR' AND USER_ID = 'PassedVariable';

Expired Credits :

Select SUM(Amount) as TotalExpiredCredits FROM msg_credittranslog where msg_credittranslog_type = 'CR'
AND User_ID = 'PassedVariable' AND msg_credittranslog_expirydate <= dateadd(d,1,getDate()) ;

Spent Total :

Select SUM(Amount) as TotalTrans FROM msg_credittranslog where msg_credittranslog_type = 'DR' AND User_ID = 'PassedVariable';

CurrentCredit = TotalCredit - (Expired Credits + Spent Total)

However this obviously fails as deducting the total of expired credits and spent credits doubles up values.

Anyone have a clue how to overcome this issue for an accurate method to calculate the current credit available (while being aware of both spent credit and available credit that hasn't expired)?

I've probobly explained it rather poorly, if any more info is required just ask!