| Author |
 Topic  |
|
|
allan8964
Posting Yak Master
196 Posts |
 Posted - 11/20/2012 : 12:05:48
|
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
7173 Posts |
Posted - 11/20/2012 : 12:17:53
|
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 |
 |
|
|
allan8964
Posting Yak Master
196 Posts |
Posted - 11/20/2012 : 13:21:54
|
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.
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/20/2012 : 13:36:00
|
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 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/20/2012 : 13:48:38
|
-- 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 |
|
|
|
allan8964
Posting Yak Master
196 Posts |
Posted - 11/20/2012 : 14:47:34
|
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 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/20/2012 : 14:53:00
|
| I wouldn't use trigger in this case. |
|
|
| |
Topic |
|
|
|
Find those which have same ID and add them up
