Skip to Content
author's profile photo Former Member
Former Member

Query for TOP 5 Values from Groups

Hi

I will greatly appreciate it if someone could assist me with a query to show the TOP 2 Overdue ([aCustomer Key]) from the following:

SELECT

Dim_Customer.customerGroup as [aCustomerGroup], v_age_snapshot.customerkey as [aCustomer Key],

sum(fact_ageanalysis.[30days])+ sum(fact_ageanalysis.[60days])+sum(fact_ageanalysis.[90days]) as Overdue


FROM

v_age_snapshot INNER JOIN Fact_AgeAnalysis ON v_age_snapshot.SnapShotDate = Fact_AgeAnalysis.SnapShotDate

AND v_age_snapshot.AgeDate = Fact_AgeAnalysis.AgeDate AND v_age_snapshot.CustomerKey = Fact_AgeAnalysis.CustomerKey

INNER JOIN Dim_Customer ON v_age_snapshot.CustomerKey = Dim_Customer.CustomerKey AND v_age_snapshot.AgeDate BETWEEN Dim_Customer.RowStartDate AND

Dim_Customer.RowEndDate

INNER JOIN v_age_CustomerTotal ON v_age_snapshot.SnapShotDate = v_age_CustomerTotal.SnapShotDate AND v_age_snapshot.AgeDate = v_age_CustomerTotal.AgeDate AND

v_age_snapshot.CustomerKey = v_age_CustomerTotal.CustomerKey

Group

by Dim_Customer.customerGroup, v_age_snapshot.customerkey

ORDER

By Dim_Customer.customerGroup, v_age_snapshot.customerkey (This is extracted from a data warehouse hence the strange field names)

So the result set must look like this:

CustomerGroup CustomerKey Overdue

BlueChip C1000 23 000 000

BlueChip C3423 18 434 102

Transport C2134 34 215 456

Transport C2111 560 066

.

.

Thank you

Danie

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Nov 12, 2012 at 02:29 PM

    Hi Danie

    Run In sql check does it works

    Select tt.iRank,tt.[aCustomerGroup],tt.[aCustomer Key],tt.Overdue from (

    SELECT

    RANK() OVER(PARTITION BY Dim_Customer.customerGroup order by sum(fact_ageanalysis.[30days])+ sum(fact_ageanalysis.[60days])+sum(fact_ageanalysis.[90days]) desc ) AS iRank,

    Dim_Customer.customerGroup as [aCustomerGroup], v_age_snapshot.customerkey as [aCustomer Key],

    sum(fact_ageanalysis.[30days])+ sum(fact_ageanalysis.[60days])+sum(fact_ageanalysis.[90days]) as Overdue

    FROM

    v_age_snapshot INNER JOIN Fact_AgeAnalysis ON v_age_snapshot.SnapShotDate = Fact_AgeAnalysis.SnapShotDate

    AND v_age_snapshot.AgeDate = Fact_AgeAnalysis.AgeDate AND v_age_snapshot.CustomerKey = Fact_AgeAnalysis.CustomerKey

    INNER JOIN Dim_Customer ON v_age_snapshot.CustomerKey = Dim_Customer.CustomerKey AND v_age_snapshot.AgeDate BETWEEN Dim_Customer.RowStartDate AND

    Dim_Customer.RowEndDate

    INNER JOIN v_age_CustomerTotal ON v_age_snapshot.SnapShotDate = v_age_CustomerTotal.SnapShotDate AND v_age_snapshot.AgeDate = v_age_CustomerTotal.AgeDate AND

    v_age_snapshot.CustomerKey = v_age_CustomerTotal.CustomerKey

    Group

    by Dim_Customer.customerGroup, v_age_snapshot.customerkey

    ) as TT where tt.iRank<='5'

    order by Overdue desc

    Hope helpful 😊

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.