How to calculate running total from due amount in SQL Server-CodePudding

I have customer Table ID and Customer Name

CREATE TABLE [dbo].[Customer]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](500) NULL 
) ON [PRIMARY]

I have Payment Schedule Table each customer may have different schedule

CREATE TABLE [dbo].[SchedulePayment]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [Amount] [decimal](18, 0) NULL,
    [CustomerID] [int] NULL
) ON [PRIMARY]

I have ReceivedPayment table:

CREATE TABLE [dbo].[ReceivedPayment]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [ReceivedAmount] [decimal](18, 0) NULL,
    [ReceivedDate] [date] NULL
) ON [PRIMARY]

I want to deduct received amount from the schedule amount and show the remaining amount till today month and year future amount would be 0 because it is not due now but if customer pay extra then it will show in the future months

I want below output

CodePudding user response：

i looked at it too, and there is more going on here than can be explained at first glance. in normal payment posting, a balance carry forward rule to be held pending apply, and another rule would exist as the first of the month occurs, for payments in excess of payment due let alone no arrears. the solution has some rules than in all intense purposes is not necessarily database driven yet process and application driven from an accounting a/r perspective. here is some sample testing I did just to see how close I could come yet then realized expected output requires rules and dates and what I call float.

use test1

/*
CREATE TABLE [dbo].[Customer]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](500) NULL 
) ON [PRIMARY]

-- drop table customer

insert into customer select ('Customer1')
insert into customer select ('Customer2')
select * from customer

CREATE TABLE [dbo].[SchedulePayment]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [Amount] money NULL,
    [CustomerID] [int] NULL
) ON [PRIMARY]


insert into SchedulePayment values  ('2022-01-01', 1000, 1)
insert into SchedulePayment values  ('2022-02-01', 1000, 1)
insert into SchedulePayment values  ('2022-03-01', 1000, 1)
insert into SchedulePayment values  ('2022-04-01', 1000, 1)
insert into SchedulePayment values  ('2022-01-01', 1000, 2)
insert into SchedulePayment values  ('2022-02-01', 1000, 2)
insert into SchedulePayment values  ('2022-03-01', 1000, 2)
insert into SchedulePayment values  ('2022-04-01', 1000, 2)

-- drop table schedulepayment

select * from schedulepayment


CREATE TABLE [dbo].[ReceivedPayment]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [ReceivedAmount] money NULL,
    [ReceivedDate] [date] NULL
) ON [PRIMARY]

insert into ReceivedPayment values (1, 500, '2022-01-06')
insert into ReceivedPayment values (1, 1500, '2022-02-15')
insert into ReceivedPayment values (1, 500, '2022-03-10')
insert into ReceivedPayment values (2, 100, '2022-01-01')
insert into ReceivedPayment values (2, 500, '2022-02-06')
insert into ReceivedPayment values (2, 400, '2022-02-08')
insert into ReceivedPayment values (2, 600, '2022-03-04')

-- drop table receivedpayment


select * from receivedpayment
*/

create table #t
(CustomerID int,
ScheduleDate date,
ReceivedAmount money,
PendingAmount money,
ScheduledAmount money
)

insert into #t (customerid, scheduledate, scheduledamount)
select 
distinct customer.ID,
schedulepayment.date,
schedulepayment.Amount
from customer
left join schedulepayment on SchedulePayment.CustomerID = customer.ID

select * from #t
drop table #t


-- below is a pay flow by yet not a balance over paid and carry over to be spread 
select 
SchedulePayment.CustomerID,
SchedulePayment.Date,
ReceivedAmount = sum(ReceivedPayment.ReceivedAmount) 
from ReceivedPayment 
join SchedulePayment on ReceivedPayment.CustomerID = SchedulePayment.CustomerID
where datepart(month, SchedulePayment.Date) = datepart(month, ReceivedPayment.ReceivedDate) and datepart(year, schedulepayment.Date) = datepart(year, ReceivedPayment.ReceivedDate)
GROUP BY SchedulePayment.CustomerID, SchedulePayment.Date

CodePudding user response：

 

SELECT rp.CustomerID,[date] ScheduleDate,  ReceivedAmount,
(SumAmount - 
    SUM(ReceivedAmount) 
    OVER (PARTITION BY rp.CustomerID ORDER BY Receiveddate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
) AS PendingAmount
,Amount AS ScheduleAmount  
FROM
(SELECT [date], Amount, CustomerID,
    (SUM(amount) 
    OVER (PARTITION BY CustomerID ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ) AS SumAmount
FROM            SchedulePayment) sp
    JOIN ReceivedPayment rp ON rp.CustomerID = sp.CustomerID
    AND ReceivedDate >= [date] 
    AND 
    (
     (DATEPART(MM,ReceivedDate)=DATEPART(MM,[date])) 
     AND (DATEPART(YYYY,ReceivedDate)=DATEPART(YYYY,[date]))
    )
ORDER BY rp.CustomerID,ReceivedDate,[date]