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
 Find those which have same ID and add them up
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

249 Posts

Posted - 11/20/2012 :  12:05:48  Show Profile  Reply with Quote
Hi there,

Table1 has coulmns of CodeId, TypeId and ModelId, Num1, Num2 ... the Num1, Num2 have data type of int values and the combination of CodeId, TypeId and ModelId can't be the same. In other word, those CodeId, TypeId and ModelId combination must be unique. If any same combination ID found then add up Num1 and Num2 and save the sum as record then old records must be removed. See following sample:

Rec#|CodeId|TypeId|ModelId|Num1|Num2| ...
1 |11 | 13 | 3 | 24 | 18 |
2 |13 | 9 | 2 | 17 | 21 |
3 |11 | 13 | 3 | 29 | 65 |
4 |14 | 13 | 17 | 24 | 18 |


From above table Rec# 1 and 3 have same ID combination. So Rec#1 and 3 must be removed and a new rec with same ID combination and Num1=53, Num2=83 should be added before removal. New rec looks like this:

5 |11 | 13 | 3 | 53 | 83 |

How can I get that? Thanks in advance.

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  12:17:53  Show Profile  Reply with Quote
Try this in test environment before you apply:

-- Insert into Temp table

Select CodeId,TypeId,ModelId,SUM(Num1)NUM1,SUM,(Num2)NUM2........
into #temp
from Table
Group by CodeId,TypeId,ModelId

-- Delete the entries

Delete P from
(
Select CodeId,TypeId,ModelId,ROW_NUMBER() OVER (PARTITION BY CodeId,TypeId,ModelId Order by CodeId) as Seq
from Table
)P
Where P.Seq > 1

-- Reinsert back

Insert into table

Select CodeId,TypeId,ModelId,NUM1,NUM.....from #temp

Edited by - sodeep on 11/20/2012 12:18:35
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 11/20/2012 :  13:21:54  Show Profile  Reply with Quote
Thanks sodeep. I think the 2nd part of deletion, the P.Seq should be greater than 0 not 1. If P.Seq > 1 it only deletes the duplicated records not all. Later when it comes to 3rd part, write back the #temp table it should write to an empty table.
I am thinking about if it's possible that getting data for only duplicated records in 1st part because the table is huge, about millions records there.
Anyway thanks a lot again.

Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/20/2012 :  13:36:00  Show Profile  Reply with Quote
I hope RecNo is and Identity, if not, disregard

CREATE TABLE #temp (RecNo INT IDENTITY (1,1),CodeID Int,TypeID int,ModelID int,Num1 int,Num2 int)

INSERT INTO #temp
VALUES

(11 , 13 , 3 , 24 , 18) ,
(13 , 9 , 2 , 17 , 21) ,
(11 , 13 , 3 , 29 , 65 ),
(14 , 13 , 17 , 24 , 18 )


SELECT *
FROM #temp

SELECT DISTINCT t1.CodeID,t1.TypeID,t1.ModelID
,SUM(Num1) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NewNum1
,SUM(Num1) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NewNum2
,COUNT(*) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NumRows
INTO #temp_Groups
FROM #temp t1


DELETE tgt
FROM #temp tgt
INNER JOIN #temp_Groups src
ON tgt.CodeID = src.codeID AND tgt.typeId = src.typeid AND tgt.ModelID = src.modelID
WHERE src.NumRows > 1


INSERT INTO #temp(CodeID ,TypeID ,ModelID,Num1,Num2)
SELECT CodeID t,TypeID ,ModelID,NewNum1,NewNum2
FROM #temp_Groups
WHERE NumRows>1

SELECT *
FROM #temp

DROP TABLE #temp
DROP TABLE #temp_Groups

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  13:48:38  Show Profile  Reply with Quote
-- Insert into Temp table

Select CodeId,TypeId,ModelId,Count(*),SUM(Num1)NUM1,SUM,(Num2)NUM2........
into #temp
from Table
Group by CodeId,TypeId,ModelId
Having Count(*) > 1

-- Delete the entries

Delete t from
table t
inner join #temp p on p.CodeId = t.CodeId and 
p.TypeId= t.TypeId and p.ModelId = t.ModelId

-- Reinsert back

Insert into table

Select CodeId,TypeId,ModelId,NUM1,NUM.....from #temp
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 11/20/2012 :  14:47:34  Show Profile  Reply with Quote
Thanks guys.
jim, the select distinct part adds all the columns, even the ones without duplicated.
sodeep, this time it works perfect!!! That Having Count(*) > 1 clears up the puzzle and deletion hits on only the duplicated combination.
One more question: I use this in a store procedure to insert a new record to table, my question is should I embed the code into the stored procedure at the end or put it into the trigger of table? which one gives the best performance?
Thank you guys again!!!


Edited by - allan8964 on 11/20/2012 14:48:14
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  14:53:00  Show Profile  Reply with Quote
I wouldn't use trigger in this case.
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.07 seconds. Powered By: Snitz Forums 2000