伪代码:

开始对于主记录中的每条记录开始获取记录的 %MONTH% 和 %YEAR%用 day:1, month=%MONTH%, and year=%YEAR%, Transaction='请支付你的租金'等创建新记录.添加记录并计算余额结尾使用计算出的余额添加最终记录高级资金记录"结尾

如果你有想法，你可以把它翻译成 tsql-stored-proc.

I'm fairly new in ASP.NET and MSSql and I came across a problem, I have a House Rent payment system where I want to automatically generate a certain reminder like Pay-Rent at the starting of every month where I already have the transaction of payment of rent and return of advanced money given to the land-lord by the tenant after the first day of the month

...currently my database shows this

EntryDate   Transaction                Advance     Rent Paid  Description         Advanced  
                                       Return
2021-01-27  Rent Paid                  0.00        2000.00   2k rent received     5000.00   
2021-02-20  Rent Paid                  0.00        9000.00   9000 rent paid       5000.00   
2021-03-10  Rent Paid                  0.00        100.00    100 rent paid        5000.00   
2021-04-6   Rent Paid                  0.00        99.00     99 amount paid       5000.00   
2021-05-2   Advanced Money Returned    1000.00     0.00      1000 rent returned   5000.00   

...what i want to do is this

EntryDate   Transaction               Rent     Advance     RentPaid     Description         Advanced  
                                               Return
2021-01-01  Please Pay your rent      5000      0              0           0                  0
2021-01-27  Rent Paid                  0      0.00             5000.00     5k rent received   5000.00   
2021-02-01  Please Pay your rent      5000      0              0           0                  0
2021-02-20  Rent Paid                  0      0.00             5000.00     5k rent paid       5000.00   
2021-03-01  Please Pay your rent      5000      0              0           0                  0
2021-03-10  Rent Paid                  0      0.00             5000.00     5k rent paid       5000.00   
2021-04-01  Please Pay your rent      5000      0              0           0                  0
2021-04-6   Rent Paid                  0      0.00             5000.00     5k amount paid     5000.00   
2021-05-01  Please Pay your rent      5000      0              0           0                  0
2021-05-2   Advanced Money Returned    0      1000.00          5000.00     5k rent returned   5000.00   

this line which i want is to be autogenerated at the beginning of each month

2021-01-01  Please Pay your rent      5000      0              0           0                  0

and this is the code of my StoredProcedure

        
SELECT RR.EntryDate, (N'RentPaid') as TransactionName, 0 as AdvanceReturn, RR.AmountPaid as RentPaid, RR.Description,MemberStartAmount as Advanced
        FROM tblRentReceive as RR WHERE UserId = @UserId 
        UNION ALL   
        SELECT OGF.EntryDate, (N'Advanced Money Returned') as TransactionName,  AR.Amount as Amount, 0 as RentReceive, AR.Description,
        @MemberStartAmount as Advanced
        FROM tblAdvancedReturn as AR WHERE AR.UserId = @UserId and AR.TenantId=@TenantId 
    ORDER BY EntryDate ASC 
END
END

Where do i need to add that automated monthly row without breaking my union between two tables

The pseudo code:

START    
for each record in master-record
begin
      get the %MONTH% and %YEAR% of the record
      create new record with day:1, month=%MONTH%, and year=%YEAR%, Transaction='Please pay your rent', and etc.
      add the record and calculate the balance
end
add final-record 'Advanced Money Record' with the calculated balance

END

If you got the idea, you can translate it to tsql-stored-proc.