Need help with a query involving flagging duplicate records.

I have a table named "Purchases" which has records related to  a customers purchases. Each record entry for a customer is uniquely identified by their Social Security number. attached is the scripted database.

I need a query that will peform the following below. Can someone please help me out?

Set the "Active" flag to 1 where the DUP_PARAM = 2 and the MoneyAmount is the largest value (Max) for any one customer.
For those customer records that do not have the Max MoneyAmount, and do have DUP_PARAM=2, then set the ActiveFlag = 9.
Also for the records that do not have DUP_Param = 2, then just ignore those records.

Purchases (Before Query) :

SocialSSN    DUP_Param    MoneyAmount  Active
888667000     2                     100.00                  0
888667000     2                    120.00                   0
888667000     2                    125.00                   0
888667000     0                    175.00                   0
787994098     2                   100.00                    0
787994098     2                   120.00                    0
555443000     1                   45.00                      0
555443000     2                   40.00                      0
474747474     2                   40.00                      0
474747474     2                   40.00                      0

Purchases (After Query) :

SocialSSN    DUP_Param    MoneyAmount  Active
888667000     2                    100.00                   9
888667000     2                    120.00                   9
888667000     2                    125.00                   1
888667000     0                    175.00                   0
787994098     2                    100.00                   9
787994098     2                    120.00                   1
555443000     1                    45.00                     0
555443000     2                    40.00                     1
474747474     2                    40.00                     1
474747474     2                    40.00                     9
Purchases.txt
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without working the details of this question, this article might help .. SQL Server Delete Duplicate Rows, specifically the DELETE #1 HARD DELETE section where you can copy-paste the code into your SSMS, alter to fit your table(s), add a WHERE clause in your CTE to handle 'the following below', and execute.
0
brgdotnetcontractorAuthor Commented:
Hi Jim. I tried a nested sub query today, with other approaches, and I just cannot get it to work. I am absolutely stuck.
0
PortletPaulfreelancerCommented:
with CTE as (
      select
         SocialSS
        , DUP_Param
        , MoneyAmount
        , row_number() over(partition by SocialSS order by MoneyAmount DESC) AS RN
        , Active
      from Purchases
      where DUP_Param = 2
      )
update CTE
set active = case when RN = 1 then 1 else 9 end
;

select
*
from Purchases
order by SocialSS, DUP_Param, MoneyAmount, Active
;

Open in new window

Which produces this result based on your sample data:
| ID |  SocialSS | DUP_Param | MoneyAmount | Active |
|----|-----------|-----------|-------------|--------|
|  1 | 474747474 |         2 |          40 |      1 |
|  2 | 474747474 |         2 |          40 |      9 |
|  3 | 555443000 |         1 |          45 |      0 |
|  4 | 555443000 |         2 |          40 |      1 |
|  5 | 787994098 |         2 |         100 |      9 |
|  6 | 787994098 |         2 |         120 |      1 |
|  7 | 888667000 |         0 |         175 |      0 |
|  8 | 888667000 |         2 |         100 |      9 |
|  9 | 888667000 |         2 |         120 |      9 |
| 10 | 888667000 |         2 |         125 |      1 |

Open in new window

Note I introduced an ID column, details of test data etc. here
    CREATE TABLE Purchases
        ([ID] int, [SocialSS] int, [DUP_Param] int, [MoneyAmount] int, [Active] int)
    ;
        
    INSERT INTO Purchases
        ([ID], [SocialSS], [DUP_Param], [MoneyAmount], [Active])
    VALUES
        (1, 474747474, 2, 40, 0),
        (2, 474747474, 2, 40, 0),
        (3, 555443000, 1, 45, 0),
        (4, 555443000, 2, 40, 0),
        (5, 787994098, 2, 100, 0),
        (6, 787994098, 2, 120, 0),
        (7, 888667000, 0, 175, 0),
        (8, 888667000, 2, 100, 0),
        (9, 888667000, 2, 120, 0),
        (10, 888667000, 2, 125, 0)
    ;
    
**Query 1**:

    with CTE as (
          select
             SocialSS
            , DUP_Param
            , MoneyAmount
            , row_number() over(partition by SocialSS order by MoneyAmount DESC) AS RN
            , Active
          from Purchases
          where DUP_Param = 2
          )
    update CTE
    set active = case when RN = 1 then 1 else 9 end
    

**[Results][2]**:
**Query 2**:

    
    
    select
    *
    from Purchases
    order by SocialSS, DUP_Param, MoneyAmount, Active
    

**[Results][3]**:
    | ID |  SocialSS | DUP_Param | MoneyAmount | Active |
    |----|-----------|-----------|-------------|--------|
    |  1 | 474747474 |         2 |          40 |      1 |
    |  2 | 474747474 |         2 |          40 |      9 |
    |  3 | 555443000 |         1 |          45 |      0 |
    |  4 | 555443000 |         2 |          40 |      1 |
    |  5 | 787994098 |         2 |         100 |      9 |
    |  6 | 787994098 |         2 |         120 |      1 |
    |  7 | 888667000 |         0 |         175 |      0 |
    |  8 | 888667000 |         2 |         100 |      9 |
    |  9 | 888667000 |         2 |         120 |      9 |
    | 10 | 888667000 |         2 |         125 |      1 |

  [1]: http://sqlfiddle.com/#!3/62f09/5

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brgdotnetcontractorAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for brgdotnet's comment #a40947453

for the following reason:

Thank you. I was under a tight deadline and needed that done today. I really appreciate it. SQL is not my strength, but I am learning. When work let's up this week, I will help out some others in other technical areas I am strong in.
0
PortletPaulfreelancerCommented:
I have no idea what your 'today' means, mine is Australian Eastern Standard Time, and i provided an answer in my today.

I believe the answer I provided met the required result.

Not entirely sure why it has been ignored.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Tell you what ... instead of deleting this question, please post the eventual answer so that others with the same issue can read it and benefit.  Thanks.
0
brgdotnetcontractorAuthor Commented:
Thank you Paul. I was under the impression that I had awarded you the points? Did I make a mistake?
0
PortletPaulfreelancerCommented:
yes, you had attempted to delete the question with zero points awarded

all fixed, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.