CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Anthony Kallay
Anthony Kallay

Reputation: 133

Tricky SQL Query for rolling credits

I have an sql query that is baffling me, basically I've credits table where credits can be applied and deducted, simple, it gets tricky when I introduce expiry dates for applied credits as I've to almost rollover dates, the basic table is below:

CreditID     int
Credit       int
AppliedDate  DateTime
ExpiryDate   DateTime

An example of the data entry is below:

CredidID | Credit | AppliedDate | ExpiryDate
---------+--------+-------------+-----------
1        | 10     |             | 1/8/2017       (10 credit added that expire)
2        | 10     |             | 1/1/2018       (10 credit added that expire)
3        | -5     | 1/9/2017    |                (5 credits used on 1/9)

So the answer for this is my current credits, 5, but as the table gets more complex I can't think of an sql query that would give me today's credit balance at any given time?

Any help would be appreciated.

Upvotes: 1

Views: 470

Answers (3)

Vijay
Vijay

Reputation: 146

select sum(credit) +  (select isnull(sum(credit),0) from credits 
        where AppliedDate is not null 
        and AppliedDate > 
                    (select isnull(max(ExpiryDate),0) from Credits 
                    where ExpiryDate < getdate() and ExpiryDate is not null)) 
'Credit Balance'
    from credits where ExpiryDate > getdate() and ExpiryDate is not null

Logic- 1. get the sum of deducted credits later than the applied credits(applied credits that are expired i.e.

Sum of this (1+2) would desired result ( summing the above result since carries the negative value in the table )

Let me know if you still failing any case.

Upvotes: 1

Karolina Ochlik
Karolina Ochlik

Reputation: 938

If I'd have to do it by database (only if I'd have to ;) ), I'd do two tables, one with balance, second with a single credits operation.

For example (in MSSQL): 

create table WalletBalance
(
    WalletID bigint identity(1,1) not null primary key,
    LastModifiedTime datetime,
    Balance bigint
)

create table CreditsOperation
(
    ID bigint identity(1,1) not null primary key,
    WalletID bigint,
    ExpirationDate date,
    ModifiedTime datetime

    foreign key (WalletID) references WalletBalance(WalletID)
)

Then make a trigger for update, delete and insert for operations table, which would add or subtract wallet balance.

Then - I'd do a daily SQL job which would exec procedure for checking expiration date etc.

Upvotes: 1

Matt Hartman
Matt Hartman

Reputation: 64

If your applied credits are always negative and in the past you can use:

SELECT SUM(t.Credit) FROM [yourtable] t 
WHERE ExpiryDate IS NULL OR ExpiryDate > GetDate()

Upvotes: 0

Related Questions