CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Remove Duplicates while Merging values

How can I remove duplicates and merge Account Types?

I have a call log that reports duplicate phones based on Account Type.

For example:

Telephone       | Account Type

304-555-6666    | R

304-555-6666    | C

In the example the surviving duplicate would be:

Telephone       | Account Type

304-555-6666    | B

Warning, it is not guaranteed that duplicate phones have multiple Account Types.

Example:

Telephone       | Account Type

999-888-6666    | R

999-888-6666    | R

Therefore the surviving duplicate should be:

Telephone       | Account Type

999-888-6666    | R

How can I remove duplicates and reset the account type at the same time?

--
-- Remove Duplicate Recordings
--
SELECT * FROM (
SELECT  i.dateofcall ,
      i.recordingfile ,
        i.telephone ,        
        s.accounttype ,
        ROW_NUMBER() OVER (PARTITION BY i.telephone ORDER BY i.dateofcall DESC) AS 'RANK' ,
                    COUNT(i.telephone) OVER (PARTITION BY  i.telephone) AS 'MAXCOUNT'
FROM    #myactions i
        LEFT JOIN #myphone s ON s.interactionID = i.Interactionid 
) x
WHERE [RANK] = [MAXCOUNT]

Upvotes: 1

Views: 152

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Basically you need to put your business check in a case statement outside.

EDIT: I've also added the logic for B, R and C. Also done a sql fiddle- link to fiddle -http://sqlfiddle.com/#!6/b5ef5/7

SELECT 
    x.dateofcall,
    x.recordingfile,
    x.telephone,
    COALESCE(
        CASE WHEN x.maxcount>1 AND value>x.maxcount AND value<(2*x.maxcount) THEN 'B' ELSE NULL END,
        CASE WHEN x.maxcount>1 AND value= (2*x.maxcount) THEN 'C' ELSE NULL END,
        CASE WHEN x.maxcount>1 AND value= x.maxcount THEN 'R' ELSE NULL END,
        x.accounttype ) as accounttype, 
    x.rank,
    x.maxcount
FROM (
    SELECT  i.dateofcall ,
            i.recordingfile ,
            i.telephone ,        
            s.accounttype ,
            ROW_NUMBER() OVER (PARTITION BY i.telephone ORDER BY i.dateofcall DESC) AS 'RANK' ,
            COUNT(i.telephone) OVER (PARTITION BY  i.telephone) AS 'MAXCOUNT',
            SUM(CASE WHEN s.accounttype LIKE 'R' THEN 1 ELSE 2 END) OVER (PARTITION BY  i.telephone) as Value
    FROM 
        myactions i LEFT JOIN myphone s 
        ON s.interactionID = i.Interactionid 
    ) x
WHERE [RANK] = [MAXCOUNT]

Upvotes: 1

Jaime Marin
Jaime Marin

Reputation: 26

SELECT * FROM (
SELECT  i.dateofcall ,
      i.recordingfile ,
        i.telephone ,        
        s.accounttype ,
        ROW_NUMBER() OVER (PARTITION BY i.telephone ORDER BY i.dateofcall DESC) AS 'RANK' ,
        COUNT(i.telephone) OVER (PARTITION BY  i.telephone) AS 'MAXCOUNT',
        DENSE_RANK() OVER ( PARTITION BY i.telephone ORDER BY s.accounttype DESC ) AS 'ContPhone'
FROM    #myactions i
        LEFT JOIN #myphone s ON s.interactionID = i.Interactionid 
) x
WHERE [RANK] = [MAXCOUNT]

Upvotes: 1

SQLCurious
SQLCurious

Reputation: 524

Try this?

select
    x.dateofcall
    , x.recordingfile
    , x.telephone
    , case when count(*) > 2 then 'B' else max(x.accounttype) end accounttype
    (
    select
        i.dateofcall
        , i.recordingfile
        , i.telephone
        , s.accounttype
    from
        #myactions i
        LEFT JOIN #myphone s ON s.interactionID = i.Interactionid
    group by
        i.dateofcall
        , i.recordingfile
        , i.telephone
        , s.accounttype
    ) x
group by
    x.dateofcall
    , x.recordingfile
    , x.telephone

Upvotes: 1

Related Questions