asp.net - Automated Monthly data generation in SQL

Question

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

question from:https://stackoverflow.com/questions/65918586/automated-monthly-data-generation-in-sql

Answer 1

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.