Calculate to dependent column in SQL Server

test0101
 
on Aug 02, 2021 02:28 AM
645 Views

Dear friend

I have the table as image 1, I has tried view Dept as image 2, but I don't know to use with function in SQL.

Any body can help me? 

Total= Dept+Money

Dept( the Month )=Total(Behind the Month )-Paid(Behind the Month)

Example: Dept(03/2021)=Total(12/2020)- Paid(12/2020)

Download FREE API for Word, Excel and PDF in ASP.Net: Download
test0101
 
on Aug 04, 2021 11:28 AM

I wrote sql as below, it's correct answer:

select id, customer, month, isnull((select top 1 (isnull(money, 0)-isnull(paid, 0)) from salary where id=mysalary.id and CONVERT(date, '01/' + month, 103) < CONVERT(date, '01/' + mysalary.month, 103) order by id Desc),0) from salary mysalary)

thanks for your help