We help IT Professionals succeed at work.
Get Started

SQL Syntax for Update by Ranking by Quote and Customer

59 Views
Last Modified: 2015-12-22
I was working on a script to mark old quotes as inactive but I ran into this bump. Quotes can be duplicated for the same customer or for a different customer. My current script works fine assuming the customer number is the same but breaks down if the customer number is different. For example:

CUSTOMER_NUMBER    QUOTE_NUMBER   SEQUENCE_NUMBER
100,20150100001,0
100,20150100001,1
100,20150100001,2

In this case the first two quotes need to be set to inactive because when the quote was duplicated it was for the same customer.

CUSTOMER_NUMBER    QUOTE_NUMBER   SEQUENCE_NUMBER
100,20150100001,0
101,20150100001,1
102,20150100001,2
100,20150100001,3

In this case, only the first quote would be set to inactive because there is a higher sequence number for the same quote number and customer number. Quotes two and three are not to be set to inactive because even though the quote numbers are the same the customer number is different.

I tried changing the ranking to include the customer number but it still updates the same number of records. How do I update this script to only set duplicate quotes for the same customer?

===============================================================================================================
UPDATE CSTQUTHD
SET INACTIVE=1
FROM CSTQUTHD T1
INNER JOIN
(
  SELECT
            QUOTE_NUMBER
          , SEQUENCE_NUMBER
          , REVALIDATE_NUMB
          , ROW_NUMBER() OVER (PARTITION BY QUOTE_NUMBER
                              ORDER BY SEQUENCE_NUMBER DESC) AS VersionRank
  FROM CSTQUTHD
) as T2
  ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
  WHERE T2.VersionRank>1
Comment
Watch Question
Database Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE