SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help in fix data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/18/2013 :  02:38:15  Show Profile  Reply with Quote
I HAVE TWO TABLES

TBL_CH :
ID AMOUNT

1 10.20
2 20.42
3 30

TBL_account:
ID AMOUNT
1 8
1 2
1 1
2 16
2 4
3 25
3 4


I NEED TO CHECK that FOR each id the SUM showuld be equal IN BOTH tables.

so i fire following query:


SELECT id, amount- amount1 AS diff
SELECT a.id ,
a.amount ,
(SELECT SUM(amount)
FROM TBL_account WHERE id = a.id) amount1
FROM TBL_CH a


WHEN i find diffrence THEN i know what amount need TO be INSERTED IN TABLE

so a WRITE a PROC AND THEN TAKE a CURSOR WITH above uery AND simply INSERT . everthing is ok.

but WHEN at production the dba deployed it two TIME errorneously AND we got It know AFTER 2 weeks.

there is NO back , kindly suggest me the fix.


challenge everything

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  03:12:53  Show Profile  Reply with Quote
so you want to fix data in tables or just retrieve the records existing with wrong results?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/18/2013 :  03:15:11  Show Profile  Reply with Quote
fix the data.

challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  03:17:12  Show Profile  Reply with Quote
which table got populated wrongly?TBL_account or TBL_CH?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 01/18/2013 :  03:18:49  Show Profile  Reply with Quote
TBL_account

challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  03:32:10  Show Profile  Reply with Quote
try this to get ids which are existing wrongly.

SELECT *
FROM
(
SELECT *,SUM(AMOUNT) OVER (PARTITION BY ID) AS Total
FROM TBL_Account
)t
INNER JOIN TBL_CH c
ON c.ID = t.ID
WHERE c.AMOUNT <> t.Total


once you're staisfied with returned records. you can use below logic for insertion

INSERT INTO TBL_Account
SELECT t.ID,c.AMOUNT - t.Total
FROM (SELECT ID,SUM(AMOUNT) AS Total
FROM TBL_Account
GROUP BY ID)t
INNER JOIN TBL_CH c
ON c.ID = t.ID
WHERE c.AMOUNT <> t.Total


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000