Multi GROUP BY and TOP n Sales Targets

# Multi GROUP BY and TOP n Sales Targets

• Friday, February 01, 2013 11:02 AM

Hi everyone

1 Month after the holidays and my brain already 'stopped responding'

I’ve got to 2 tables 1) Payover 2) clients

Payover:

Acno           Datum         Ref          Amnt
AAB01586   20120630   076976   150.00
AAA01206   20111021   076977   200.00
AAB00217   20130130   076978   150.00
AAC00338   20130129   076939   100.00
AAA00265    20130129   076940   60.00
AAB00181    20130125   076828   30.00

Clients:

ACC   NAME          Cc
AAA   DR XX          R
AAB   YYxyy           J
AAC   DG ZZ          D

I need to calculate a monthly target for each "cc" based on the sum of "Amnt" for the best(top) 6 months from the last 12 months. All controlers("Cc") have multiple clients. Also note that Left("Payover.Acno",3) = "clients.Acc".

Got this far on the query:

```select tbpay.clcd, tbpay.paid, tbpay.maand, tbcl.cc
from  (SELECT left(acno,3) as ClCd, SUM(amnt) as paid, right(LEFT(Datum,6),2) as Maand
FROM [Debtrec].[dbo].[PayOver]
where Datum > '20120101'
group by LEFT(datum,6), left(acno,3)
) as TBPay
left outer join
(select acc, cc from clients
) as TBCl
on tbpay.ClCd = tbcl.acc
order by tbpay.ClCd, TBPay.paid desc```
FYI Maand = Month , Datum = date

Thanks J

### All Replies

• Friday, February 01, 2013 11:37 AM

Hello Jaco ,

Try something as below

```select tbpay.clcd, tbpay.paid, tbpay.maand, tbcl.cc
from  (
SELECT X.Acc,X.maand, SUM(amnt) as paid,RANK() OVER(ORDER BY SUM(amnt) DESC PARTITION BY X.Acc) as Rnk
FROM (
SELECT clcd =left(acno,3) , maand = right(LEFT(Datum,6),2)
FROM [Debtrec].[dbo].[PayOver]
WHERE Datum > '20120101'
) x
GROUP BY X.clcd , X.maand
) as TBPay
left outer join
(select acc, cc from clients
) as TBCl
on tbpay.ClCd = tbcl.acc
and TBPay.RNK = 1
order by tbpay.ClCd, TBPay.paid desc```

Best Regards Sorna

• Friday, February 01, 2013 11:56 AM

You can tryout this query.

```select Cc,sum(Amnt) As amount from (
select C.Cc,P.Acno,DataNum,Amnt, (Rownum () over (Partition by P.Acno order by Amnt desc)) As seq
from Payover P inner join Clients C on left(P.Acno,3) = C.ACC  )A
where A.Seq < 7
group by Cc```

sarat chandra sahoo