question

Kavin avatar image
Kavin asked

How to make Recursive Query in sql Server 2008

I need to calculate the value of indebtedness column so that when openingBalance !=0 then indebtedness = openingBalnce+SalesTotal-SalesReturn. But, when openingBalnce = 0 then indebtedness = indebtedness of the previous monthSales with the same SalesID. If the previous value = 0 get the previous value and continue get previous value till have value in this column: SalesMonth SalesID openingBalance SalesTotal SalesReturn Indebtednes ---------- ------- -------------- ---------- ----------- ------------ 1 1 352200 0 5600 Null ------------------------------------------------------------------------ 1 2 50000 1100 0 Null ------------------------------------------------------------------------ 1 3 9500 6000 0 Null ------------------------------------------------------------------------ 2 1 0 0 1200 Null ------------------------------------------------------------------------ 2 2 0 300 0 Null ------------------------------------------------------------------------ 2 3 0 500 1000 Null ------------------------------------------------------------------------ 3 1 0 600 0 NULL ------------------------------------------------------------------------ 3 2 0 200 0 NULL ----------------------------------------------------------------------- 3 3 0 0 10 NULL ----------------------------------------------------------------------- . . . 12 1 0 0 0 NULL ---------------------------------------------------------------------- 12 2 0 0 0 NULL ---------------------------------------------------------------------- 12 3 0 0 0 NULL **And Output like this:** when openingBalance !=0 then Indebtednes=openingBalnce+SalesTotal-SalesReturn when openingBalnce =0 then Indebtednes=Indebtednes (of the previous month of the same SalesID)+SalesTotal-SalesReturn. **And this is the output i want.** SalesMonth SalesID openingBalance SalesTotal SalesReturn Indebtednes ---------- ------- -------------- ---------- ----------- ------------ 1 1 352200 0 5600 346600 ------------------------------------------------------------------------ 1 2 50000 1100 0 51100 ------------------------------------------------------------------------ 1 3 9500 6000 0 15500 ------------------------------------------------------------------------ 2 1 0 0 1200 345400 ------------------------------------------------------------------------ 2 2 0 300 0 51400 ------------------------------------------------------------------------ 2 3 0 500 1000 15000 ------------------------------------------------------------------------ 3 1 0 600 0 346000 ------------------------------------------------------------------------ 3 2 0 200 0 51600 ----------------------------------------------------------------------- 3 3 0 0 10 14990 ----------------------------------------------------------------------- . . . 12 1 0 0 0 NULL ---------------------------------------------------------------------- 12 2 0 0 0 NULL ---------------------------------------------------------------------- 12 3 0 0 0 NULL
sql-server-2008sql-server-2008-r2sql-serversql server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ssurve avatar image
ssurve answered
Use CTE for recursive call.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.