CODEWITHSUNDEEP
sql-server-2008
user2618885
user2618885

Reputation: 9

SQL Server 2008 - multiple orders for the same customer. Split dupes into a separate table

SQL Server 2008 - I have a file that has multiple order for the same customer. I would like to keep just one of the orders and split the other into a separate table. The date looks something like this:

pub_cde        ctm_nbr          rate          term          dnr_typ           amt_pd
--------------------------------------------------------------------------------------    
GRT            12               4.39          044              A             4.39
GRT            12               6.99          011              G             6.99
GRT            12               19.99         011              A             19.99
GRT            14                9.94         060              A             9.94
GRT            14                3.99         024              A             3.99

How would I keep the first record for each ctm_nbr and place the other in a separate file?

Upvotes: 0

Views: 40

Answers (1)

Tom Page
Tom Page

Reputation: 1241

I created some test data.

CREATE Table #testdata (pub char(3), 
                        ctm_number  int ,   
                        rate  smallmoney,
                        term char(3),
                        dnr_type char(1),
                        [amt_pd] smallmoney) 
INSERT INTO #testdata VALUES('GRT', 12,  4.39, '044', 'A',  4.39) 
INSERT INTO #testdata VALUES('GRT', 12,  6.99, '011', 'G',  6.99) 
INSERT INTO #testdata VALUES('GRT', 12, 19.99, '011', 'A', 19.99) 
INSERT INTO #testdata VALUES('GRT', 14,  9.94, '060', 'A',  9.94) 
INSERT INTO #testdata VALUES('GRT', 14,  3.99, '024', 'A',  3.99)

You can then use the ROW_NUMBER Function to assign a row number to each row in a ctm_nbr partition, Ordered within that partition by whatever you choose

select 
pub, ctm_number,rate,term,dnr_type,amt_pd ,
ROW_NUMBER() OVER (PARTITION BY CTM_NUMBER ORDER BY term DESC,rate,dnr_type,amt_pd ,pub) AS RowNumber
from #testdata

This gives output like

pub  ctm_number  rate                  term dnr_type amt_pd                RowNumber
---- ----------- --------------------- ---- -------- --------------------- --------------------
GRT  12          4.39                  044  A        4.39                  1
GRT  12          6.99                  011  G        6.99                  2
GRT  12          19.99                 011  A        19.99                 3
GRT  14          9.94                  060  A        9.94                  1
GRT  14          3.99                  024  A        3.99                  2

(5 row(s) affected)

You can then wrap that up in a subquery and select only the ones where the RowNumber equals 1

SELECT 
sub.pub, sub.CTM_NUMBER,sub.RATE,sub.term,sub.dnr_type,sub.amt_pd 
FROM 
(select 
pub, ctm_number,rate,term,dnr_type,amt_pd ,
ROW_NUMBER() OVER (PARTITION BY CTM_NUMBER
                  ORDER BY term DESC,rate,dnr_type,amt_pd ,pub) AS RowNumber
from #testdata ) sub
WHERE sub.RowNumber = 1

To get one record per ctm_number

pub  ctm_number  RATE                  term dnr_type amt_pd
---- ----------- --------------------- ---- -------- ---------------------
GRT  12          4.39                  044  A        4.39
GRT  14          9.94                  060  A        9.94

(2 row(s) affected)

Or where the rownumber exceeds 1 

SELECT 
sub.pub, sub.CTM_NUMBER,sub.RATE,sub.term,sub.dnr_type,sub.amt_pd 
FROM 
(select 
pub, ctm_number,rate,term,dnr_type,amt_pd ,
ROW_NUMBER() OVER (PARTITION BY CTM_NUMBER
                  ORDER BY term DESC,rate,dnr_type,amt_pd ,pub) AS RowNumber
from #testdata ) sub
WHERE sub.RowNumber >1

To get the other records

pub  CTM_NUMBER  RATE                  term dnr_type amt_pd
---- ----------- --------------------- ---- -------- ---------------------
GRT  12          6.99                  011  G        6.99
GRT  12          19.99                 011  A        19.99
GRT  14          3.99                  024  A        3.99

(3 row(s) affected)

I've tested this on SQL Server 2014 but the documentation says that it should also work on 2008 http://msdn.microsoft.com/en-GB/library/ms186734(v=sql.105).aspx

Upvotes: 1

Related Questions