Query for TOP 5 Values from Groups

[Former Member]

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

[KennedyT21 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Kennedy

Thank you very much for the query - it's not exactly what I want but it is very close. The result set looks like this (I'm only showing a few rows):

1 Transport Blue Chip      1418      36764829.13

1 Industrial                        510        27269604.00

2 Industrial                        1205      20517333.4492

1 Mining                            1111      19524873.60

2 Transport Blue Chip       1412      9152128.70

1 Construction                   1390      6885536.50

3 Transport Blue Chip        814      5894250.27

3 Industrial                          749      717798.88

4 Industrial                          777      333948.20

1 Transport Other                93        248360.58

2 Construction                     1338    181599.12

So as you can see the results for eg Industrial with its customers must be together. Do you think it's possible to show it like that?

Kind regards

Danie

[KennedyT21 · Accepted solution]

Hi Danie Posthumus...

Try This

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   tt.[aCustomerGroup]

[Former Member · Accepted solution]

Hi Kennedy

Thanks - still not 100%

Result set:

1 Bitumen 879 -14911243.55

2 Bitumen 40 NULL

2 Bitumen 207 NULL

2 Bitumen 1261 NULL

2 Bitumen 1428 NULL

2 Bitumen 2214 NULL

2 Bitumen 1451 NULL

2 Bitumen 211 NULL

2 Bitumen 1036 NULL

2 Bitumen 205 NULL

2 Bitumen 209 NULL

2 Bitumen 212 NULL

1 Cash Transporters 1399 21660.00

2 Cash Transporters 1285 -2173016.73

3 Cash Transporters 1208 -29234491.14

4 Cash Transporters 1283 -82630064.23

5 Cash Transporters 1472 NULL

5 Cash Transporters 1297 NULL

5 Cash Transporters 1374 NULL

5 Cash Transporters 1232 NULL

5 Cash Transporters 1307 NULL

5 Cash Transporters 1376 NULL

I Think if the NULL values are eliminated and the remaining values sorted desc it should be perfect.

I tried doing it but I'm not sure where to do it.

Regards

[KennedyT21 · Accepted solution]

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' and  tt.overdue is not null

order by   tt.[aCustomerGroup]

[Former Member · Accepted solution]

Hi Kennedy

Perfect! Thank you very much - I really appreciate it.

Kind regards

Danie