Filter based on results where ROW_NUMBER goes higher than 1

Question

I've managed to get some ordered results using ROW_NUMBER() and partitioned by the Country code and client ref, ordered by the date of the quote. The ROW_NUMBER() column is called PRank.

CountryCode  ¦   ClientRef   ¦   Quote Ref   ¦   Name     ¦    Address     ¦   PostCode   ¦   PreferencesCode   ¦   QuoteDate         ¦   PolicyType   ¦   PRank   ¦
     0           AABB001         AABB001MB1    Mr A Smith   1 Country Lane     WA21 2PU       5934611             2017-03-18 00:00:00     MB               1
     0           AABB001         AABBMM1MB2    Mr A Smith   1 Country Lane     WA21 2PU       5934611             2017-03-18 00:00:00     MB               2
     0           AABB001         AABBMM1MB3    Mr A Smith   1 Country Lane     WA21 2PU       5934611             2017-03-18 00:00:00     MB               3
     0           BBGG003         BBGG003MB1    Mrs B Jones  2 City Road        M1 3XY         312191              2017-09-02 00:00:00     MB               1

This is the code for my ROW_NUMBER() column:

ROW_NUMBER() OVER(PARTITION BY CountryCode, ClientRef, QuoteDate 
                  ORDER BY QuoteDate DESC, PolicyRef asc)

There are a lot of results like the last row; they only count to 1 because the customer just had one quote on the date in question. I want to eliminate those rows without removing any rows from the customer results where multiple quotes occured on the same date (i.e. I still want to see all three rows of the first customer in the results above) so I figured by using a CTE and filtering on the ROW_NUMBER would cause me to lose data from the results that I still need to see.

I was trying to do something with HAVING and summing the PRank column but I couldn't figure out the grouping as I don't want to actually aggregate the data, just get rid of any rows where a ClientRef only has one QuoteRef per day.

Answer 1

If you're looking for customers with more than 1 quote in a day the a CTE would still be the best way to go.

Assuming this is against the ic_dapolicy view in InfocentrePlus (this is obviously OpenGI data, and the icp tables aren't as easy to use) then you're query would be something like:

WITH Quotes AS(
    SELECT *,
           COUNT(*) OVER (PARTITION BY PPY.B@, PPY.Ref@) AS ClientQuotes 
    FROM ic_Dapolicy PPY
    WHERE PPY.Quote_date = @Quote_date) --If you aren't filtering on Quote_date in your where, add it to the PARTITION BY clause
SELECT [YourColumns]
FROM Quotes
WHERE ClientQuotes > 1;