Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Multi GROUP BY and TOP n Sales Targets

Traitée Multi GROUP BY and TOP n Sales Targets

  • Friday, February 01, 2013 11:02 AM
     
      Has Code
    Sign In to Vote
    0

    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
     
     Answered Has Code
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    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