Loops and cursors in T-Sql [Resolved]

Posted by Sharpcnet under Sql Server on 1/21/2014 | Points: 10 | Views : 930 | Status : [Member] | Replies : 2
Very Much New To Sql Loops & Cursors and desperately need a push here. Please Guide me in this.

With tbl as (
select empcode, refid
from tbltrans where date < getdate()
group by empcode, refid
)

select r.empcode, r.refid, sum(t.debit)debit, sum(t.credit)credit,
(sum(t.credit)- sum(t.debit))balance
from ref r
left join tbltrans t on r.empcode = t.empcode and r.refid = t.refid
group by r.empcode, r.refid

The above query results as :

empcode    refid    debit   credit    balance
----------------------------------------------
e1 r1 0 5 5
e1 r2 2 6 4
e2 r3 3 2 -1

now, I have to insert new records with related debit values to make the balance 0, which would be
empcode    refid    debit   credit    
-----------------------------------
e1 r1 5 0
e1 r2 4 0
e2 r3 -1 0


How should I do this.




Responses

Posted by: Bandi on: 1/28/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--Check this

With tbl as (

select empcode, refid

from tbltrans where date < getdate()

group by empcode, refid

)
INSERT TableName (empcode, refid, debit, credit )
select r.empcode, r.refid, (sum(t.credit)- sum(t.debit)) , 0
from ref r
left join tbltrans t on r.empcode = t.empcode and r.refid = t.refid
group by r.empcode, r.refid


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 1/29/2014 [Member] Starter | Points: 25

Up
0
Down
Thank you very much

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response