0

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

M_O_MEN
  • 1
  • 2
  • Add a union with all the dates truncated to the beginning of the month (with the help of [this question](https://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server) ). And please correct your model description and the results, because: 1) your query accesses two tables, but you've provided only one; 2) your query has different columns than the result you want to get, so it is not obvious how to map them. – astentx Jan 27 '21 at 12:32
  • And what should be the result if there was a prepayment in the previous month? – astentx Jan 27 '21 at 12:33

1 Answers1

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.

davidtj
  • 41
  • 4
  • davidtj Sir can you translate this I am not being able to get it.. It's gonna be really helpful for me since I am in my learning phase and I am still new... It would be of great help if you can translate – M_O_MEN Jan 28 '21 at 04:19