CODEWITHSUNDEEP
sqlsql-server-2012
Marin
Marin

Reputation: 69

SELECT TOP 10 rows

I have built an SQL Query that returns me the top 10 customers which have the highest outstanding. The oustanding is on product level (each product has its own outstanding).

enter image description here

Untill now everything works fine, my only problem is that if a certain customer has more then 1 product then the second product or more should be categorized under the same customer_id like in the second picture (because the first product that has the highest outstanding contagions the second product that may have a lower outstanding that the other 9 clients of top 10). How can I modify my query in order to do that? Is it possible in SQL Server 2012?

enter image description here

My query is:

select top 10 CUSTOMER_ID
             ,S90T01_GROSS_EXPOSURE_THSD_EUR
             ,S90T01_COGNOS_PROD_NAME
             ,S90T01_DPD_C
             ,PREVIOUS_BUCKET_DPD_REP
             ,S90T01_BUCKET_DPD_REP
from [dbo].[DM_07MONTHLY_DATA]
where S90T01_CLIENT_SEGMENT = 'PI'
      and YYYY_MM = '2017_01'
group by CUSTOMER_ID
        ,S90T01_GROSS_EXPOSURE_THSD_EUR
        ,S90T01_COGNOS_PROD_NAME
        ,S90T01_DPD_C
        ,PREVIOUS_BUCKET_DPD_REP
        ,S90T01_BUCKET_DPD_REP
order by S90T01_GROSS_EXPOSURE_THSD_EUR desc;

Upvotes: 0

Views: 6934

Answers (1)

iamdave
iamdave

Reputation: 12243

You need to calculate the top Customers first, then pull out all their products. You can do this with a Common Table Expression.

As you haven't provided any test data this is untested, but I think it will work for you:

with top10 as
(
    select top 10 CUSTOMER_ID
                 ,sum(S90T01_GROSS_EXPOSURE_THSD_EUR) as TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR
    from [dbo].[DM_07MONTHLY_DATA]
    where S90T01_CLIENT_SEGMENT = 'PI'
          and YYYY_MM = '2017_01'
    group by CUSTOMER_ID
    order by TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
)
select m.CUSTOMER_ID
        ,m.S90T01_GROSS_EXPOSURE_THSD_EUR
        ,m.S90T01_COGNOS_PROD_NAME
        ,m.S90T01_DPD_C
        ,m.PREVIOUS_BUCKET_DPD_REP
        ,m.S90T01_BUCKET_DPD_REP
from [dbo].[DM_07MONTHLY_DATA] m
    join top10 t
        on m.CUSTOMER_ID = t.CUSTOMER_ID
order by t.TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
    ,m.S90T01_GROSS_EXPOSURE_THSD_EUR;

Upvotes: 2

Related Questions