sql组中最低的运行余额

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

我一直在努力解决这个问题，没有解决方案。我希望在一组中获得最低的运行平衡。这是一个示例数据

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

问题是我希望在特定月份（1月）获得最低的运行余额，因此对于memberid 10001，输出应为150，对于memberid 10002，输出应为175，如图中突出显示的那样。我希望的出局应该是

memberid | balance

memberid |平衡

10001 | 150

10002 | 175

Is that possible using sql query only?

这可能只使用sql查询吗？

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

PS。使用c＃计算最低运行平衡非常慢，因为我的表中有超过600,000条记录。

I've updated the question.

我已经更新了这个问题。

2 个解决方案

#1

The answer provided by Mihir Shah gave me the idea how solve my problem. His answer takes to much time to process making it as slow as my computation on my c# program because his code loops on every record.

Mihir Shah提供的答案给了我如何解决我的问题的想法。他的回答需要花费很多时间来处理，因为我的c＃程序计算速度慢，因为他的代码在每条记录上循环。

Here is my answer to get the minimum lowest value in a specific 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
from
(
    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 */
)

,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
where
a.trdate >= '01/01/2016'
and 
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*/

,ComputedDetailMonth1 as 
(   
select a.memberid, min(a.runningbalance) as MinRunningBal
from
(
    select a.rowid, a.memberid, a.totalbal, 
    (
        sum(b.totalbal) + 
        (case 
        when c.runningtotal is null then 0
        else c.runningtotal
        end)
    )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
) /* the loop is only for the records of the specific month only making it much faster */  
/* 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*/

, OldBalanceWithNoNewSavingsMonth1 as
(
select a.memberid,a.RunningTotal
from 
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 value if the member has no transaction in the specific month. */

,finalComputedMonth1 as
(
select a.memberid,a.runningTotal as MinRunTotal from OldBalanceWithNoNewSavingsMonth1 a
union 
select b.memberid,b.MinRunningBal from ComputedDetailMonth1 b
)/*the record with minimum running total with clients that has a transaction  in the specific month Unions with the members with no current transaction in the specific month*/

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

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

我的储蓄表上有超过600k的储蓄记录

Surprisingly the performance of this code is very efficient.

令人惊讶的是，此代码的性能非常高效。

It takes almost 2hr using my c# program to manually compute every record of all the members.

使用我的c＃程序手动计算所有成员的每条记录需要将近2小时。

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

此代码只需2秒，最多只需9秒即可计算所有内容。我只是向c＃显示另外2秒。

The output of this code was tested and compared with my computation using my c# program.

测试了该代码的输出，并使用我的c＃程序与我的计算进行了比较。

#2

May be below one is help you

可能会低于一个是帮助你

Set Nocount On;

Declare @CashFlow Table
(
     savingsid      Varchar(50)
    ,memberid       Int
    ,trdate         Date
    ,deposit        Decimal(18,2)
    ,withdrawal     Decimal(18,2)
)

Insert Into @CashFlow(savingsid,memberid,trdate,deposit,withdrawal) Values
 ('10001-0002',10001,'01/01/2015',1000,0)
,('10001-0003',10001,'01/07/2015',25,0)
,('10001-0004',10001,'01/13/2015',25,0)
,('10001-0005',10001,'01/19/2015',0,900)
,('10001-0006',10001,'01/25/2015',25,0)
,('10001-0007',10001,'01/31/2015',25,0)
,('10001-0008',10001,'02/06/2015',25,0)
,('10001-0009',10001,'02/12/2015',25,0)
,('10001-0010',10001,'02/18/2015',0,200)
,('10002-0001',10002,'01/01/2015',500,0)
,('10002-0002',10002,'01/07/2015',25,0)
,('10002-0003',10002,'01/13/2015',0,200)
,('10002-0004',10002,'01/19/2015',25,0)
,('10002-0005',10002,'01/25/2015',25,0)
,('10002-0006',10002,'01/31/2015',0,200)
,('10002-0007',10002,'02/06/2015',25,0)
,('10002-0008',10002,'02/12/2015',25,0)
,('10002-0009',10002,'02/12/2015',0,200)

;With TrialBalance As
(
    Select   Row_Number() Over(Partition By cf.memberid Order By cf.trdate Asc) RowNum
            ,cf.memberid
            ,cf.deposit
            ,cf.withdrawal
            ,cf.trdate
    From    @CashFlow As cf
)
,RunningBalance As
(
    Select   tb.RowNum
            ,tb.memberid
            ,tb.deposit
            ,tb.withdrawal
            ,tb.trdate
    From    TrialBalance As tb
    Where   tb.RowNum = 1

    Union All

    Select   tb.RowNum
            ,rb.memberid
            ,Cast((rb.deposit + tb.deposit - tb.withdrawal) As Decimal(18,2)) 
            ,rb.withdrawal
            ,tb.trdate
    From    TrialBalance As tb
            Join RunningBalance As rb On tb.RowNum = (rb.Rownum + 1) And tb.memberid = rb.memberid
)

Select   rb.memberid
        ,Min(rb.deposit) As runningBalance
From    RunningBalance As rb
Where   Year(rb.trdate) = 2015
        And Month(rb.trdate) = 1
Group By rb.memberid

#1