Skip to Content
0
Former Member
Nov 12, 2012 at 07:54 AM

Query for TOP 5 Values from Groups

36 Views

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