question

Thameeraviraj avatar image
Thameeraviraj 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
sql-server-2008querystored-procedures
table.png (10.8 KiB)
result.txt (90 B)
4 comments
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.

Requesting to please move this question from the "meta-askssc" section to the "default" section
0 Likes 0 ·
How did you get your initial data in that order? In other words, how do you know that PCN 333 gets added to PCN 123? Why wasn't it 666 or 456?
0 Likes 0 ·
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
0 Likes 0 ·
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.
0 Likes 0 ·

1 Answer

· Write an Answer
west007 avatar image
west007 answered
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
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.