I am trying to build a report on SQL server which will provide a customer's average days of due settlement. here's the sample table from which i want to generate report from

Here I am trying to generate a report that the customer abc had cleared the due to $10000 which he owed on 2021-04-01 was cleared on 2021-08-11 and which the customer took 132 days to clear and the second due ($1000) owed on 2021-05-02 was cleared on 2021-08-21 which took 101 days and the due of $2000 made on 2021-07-10 was cleared on 2021-09-10 which took 62 days to clear.
so the desired output is

I tried to do this with SQL cursors but I was stuck after I got the result 132. Please find the code below

begin
        declare @date as date        
        declare @debit as int
        declare @debit1 as int=0
        declare @credit as int
        declare @credit1 as int=0
        declare @balance as int=0
        declare @date1 as date
        declare @i as int=0
        declare @count as int
        declare @k as int=0


        declare closing scroll cursor for
        select convert(date,date),debit,credit from samples
        select  @count= count(debit) from samples
        open closing
        fetch next from closing into @date, @debit, @credit ;
        while @@FETCH_STATUS=0
        begin
        set @balance=@credit-@debit
        set @date1=@date
        set @debit1=@debit
        while @@FETCH_STATUS=0 

        begin
                set @balance=@balance @credit
                set @credit1=@credit1 @credit
                set @i=@i @debit
                if (@k<=@debit1)
                begin
                    set @k=@k @debit
                end
                
                if @balance>0
                begin 
                        print(datediff(day,@date1,@date))
                        print (@credit1-@debit1)
                        print (@k)
                        break
        
                end
                fetch next from closing into @date, @debit, @credit;
                
        end
        fetch next from closing into @date, @debit, @credit;
        end
        
        close closing
        deallocate closing
end

I am stuck here about not knowing how to proceed further

CodePudding user response：

Using window function sum() over () to find cumulative sum for Total Debit and Total Credit. After that find earliest date where Total Credit >= Total Debit. That's the settlement date

with 
debit as
(
    select  *, 
            -- cumulative sum
            sum(Debit) over(partition by Customer order by [Date]) as Dr
    from    samples s
    where   Debit   > 0
),
credit as
(
    select  *, 
            -- cumulative sum
            sum(Credit) over(partition by Customer order by [Date]) as Cr
    from    samples s
    where   Credit  > 0
)
select  *, datediff(day, dr.[Date], cr.[Date]) as SettlementDays
from    debit dr
        cross apply
        (
            select  top 1 cr.[Date]
            from    credit cr
            where   cr.Customer  = dr.Customer
            and     cr.Cr       >= dr.Dr
        ) cr
order by dr.Customer, dr.[Date]

db<>fiddle demo