asked
SQL Stored Procedure
I have table like attached table. ![alt text][2] With PCN column for some rows there is PortedNo. for that PortedNo if there is another line I need to add that line PAidAmount to Original line AND need to remove that second line. If there is no portedNo or for PortedNo if there is no new row no need to do any thing. So after calculation my result set shold be like this. PCN PaidAmount PortedNo 123 30 333 456 5 null 789 72 666 369 2 777 [link text][1] Can someone help please? [1]: /storage/temp/1261-result.txt [2]: /storage/temp/1260-table.png
Requesting to please move this question from the "meta-askssc" section to the "default" section
When inserting data to the table that order will get. That number can any number. But one PCN always can have one Ported No and if PortedNo in PCN column in another row that can not have another portedNo. These validations happens when inserting data to the table
The problem is that the order of your rows is not guaranteed unless you use an order by clause. The rows as you show them just happen to return in that order, but that is unlikely to be repeatable when you start summing things up. Once you start querying the data to sum the rows up, you might get the rows in any order, giving you incorrect totals.
Hi Looks simple. Depending on how big the table is, i would do it like: a) get the list of portedNo --> to temporary table ::select portedNo, PCN into #t1 from yourTable where portedNo is not null group by portedNo , PCN b) sum paidAmount where PCN in (List of PortedNo [Result from a)]) --> to temporary table ::select PCN, sum(paidAmount as paidAmountToAdd into #t2 from yourTable as yt inner join #t1 as t on t.portedNo =yt.PCN group by PCN c) join PCN to result from [c)] und add value(s) to paidAmount --> Update ::update yt set paidAmount =paidAmount + paidAmountToAdd from yourTable as yt inner join #t2 as t on t.pcn=yt.pcn d) join portedNo to result from [c)] and delete --> Delete ::delete yt from yourTable as yt inner join #t1 as t on t.pcn=yt.pcn and t.portedNo =yt.portedNo drop temporary tables Dont frown if i make a mistake Regards
16 People are following this question.
