Mandz Mandz - 1 year ago 51
SQL Question

sql lowest running balance in a group

I've been trying for days to solve this problem to no solution.
I want to get the lowest running balance in a group.
Here is a sample data

enter image description here

The running balance is imaginary and is not part of the table.
the running balance is also computed dynamically.

the problem is I want to get the lowest running balance in a Specific month (January)
so the output should be 150 for memberid 10001 and 175 for memberid 10002 as highlighted in the image.
my desired out put should be

memberid | balance

10001 | 150

10002 | 175

Is that possible using sql query only?

PS. Using c# to compute lowest running balance is very slow since I have more than 100,000 records in my table.

I've updated the question.

Answer Source

The answer provided by Mihir Shah gave me the idea how solve my problem. His answer takes to much time to process making it much slower than my computation on my c# program. here is my answer to get the minimum lowest value in a speific group (specific month) with a running value or running total without sacrificing a lot of performance.

with IniMonth1 as 
select a.memberid, a.iniDeposit, a.iniWithdrawal, 
(cast(a.iniDeposit as decimal(10,2)) - cast(a.iniWithdrawal as decimal(10,2))) as RunningTotal
    select b.memberid, sum(b.depositamt) as iniDeposit, sum(b.withdrawalamt) as iniWithdrawal
    from savings b
    where trdate < '01/01/2016'
    group by b.memberid 
) a /*gets all the memberid, sum of deposit amount and withdrawal amt from the beginning of the savings before the specific month */
where cast(a.iniDeposit as decimal(10,2)) - cast(a.iniWithdrawal as decimal(10,2))  > 0 /*filters zero savings */

enter image description here

,DetailMonth1 as 
select a.memberid, a.depositamt,a.withdrawalamt, 
(cast(a.depositamt as decimal(10,2)) - cast(a.withdrawalamt as decimal(10,2))) as totalBal, 
    Row_Number() Over(Partition By a.memberid Order By a.trdate Asc) RowID
from savings a
a.trdate >= '01/01/2016'
a.trdate <= '01/31/2016'
and (a.depositamt<>0 or a.withdrawalamt<>0)
) /* gets all record within the specific month and gives a no of row as an id for the running value in the next procedure*/

enter image description here

,ComputedDetailMonth1 as 
select a.memberid, min(a.runningbalance) as MinRunningBal
    select a.rowid, a.memberid, a.totalbal, 
        sum(b.totalbal) + 
        when c.runningtotal is null then 0
        else c.runningtotal
    )as runningbalance , c.runningtotal as oldbalance
    from DetailMonth1 a
    inner join DetailMonth1 b
        on  b.rowid<=a.rowid
        and a.memberid=b.memberid
    left join IniMonth1 c
    on a.memberid=c.memberid
    group by a.rowid,a.memberid,a.totalbal,c.runningtotal
) a

group by a.memberid
)  /* this gets the running balance of specific month ONLY and ADD the sum total of IniMonth1 using join to get the running balance from the beginning of savings to the specific month */
/* I then get the minimum of the output using the min function*/

enter image description here enter image description here

, OldBalanceWithNoNewSavingsMonth1 as
select a.memberid,a.RunningTotal
IniMonth1 a
left join 
DetailMonth1 b
on a.memberid = b.memberid
where b.totalbal is null
)/*this gets all the savings that is not zero and has no transaction in the specific month making and this will become the default value as the lowest if the member has no transaction in the specific month. It also gets the minimum transaction in the month */

enter image description here

,finalComputedMonth1 as
select a.memberid,a.runningTotal as MinRunTotal from OldBalanceWithNoNewSavingsMonth1 a
select b.memberid,b.MinRunningBal from ComputedDetailMonth1 b
)/*unions the minimum running total with the clients wiht no current transaction */

 select * from finalComputedMonth1 order by memberid /* display the final output */

enter image description here

I have more than 600k savings record on my savings table

Surprisingly the performance of this code is very efficient.

It takes almost 2hrs to compute using my c# program.

This code makes only 2 secs and at most 9 secs just to compute everything. i Just display to c# for another 2secs.

The output was tested and compared with my computation using my c#