Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Question Unanswered: How do I aggreate/rollup values without using keyword Rollup

    I have flatten my data out but I need to add up Achievement for the quarter and and need to do the Quota_Value the same way. The Quota Value is by Quarter. the Achievement is by Month. I tried to break the unit of measure down for Quota Value by dividing by 3 ,.. but How do I get a rollup sum for the quarter. I want to chart this data and it needs to be step stacked bar. Please help.

    I need running totals/cumulative Achievement totals by Quarter and I need Quarters to be running total too. How can I sum May and June data?

    Click image for larger version. 

Name:	rollup.PNG 
Views:	1 
Size:	14.1 KB 
ID:	16086


    Code:
    SELECT    
              R2.COMMISSIONNAME, 
              R2.QUOTA_NAME,
             -- R2.QUOTA_VALUE,
              --R2.ATTAINED_PCT,
              R2.PQ_NAME,
              R2.PM_NAME,
             -- R2.PM_PERIOD_STARTDATE,
             -- R2.PM_PERIOD_ENDDATE,
             -- R2.PM_DURATION_KEY,
              SUM(R2.JAN_ACHIEVEMENT) JAN_ACHIEVEMENT,
              SUM(R2.FEB_ACHIEVEMENT) FEB_ACHIEVEMENT,
              SUM(R2.MAR_ACHIEVEMENT) MAR_ACHIEVEMENT,
              SUM(R2.APR_ACHIEVEMENT) APR_ACHIEVEMENT,
              SUM(R2.MAY_ACHIEVEMENT) MAY_ACHIEVEMENT,
              SUM(R2.JUN_ACHIEVEMENT) JUN_ACHIEVEMENT,
              SUM(R2.JUL_ACHIEVEMENT) JUL_ACHIEVEMENT,
              SUM(R2.AUG_ACHIEVEMENT) AUG_ACHIEVEMENT,
              SUM(R2.SEP_ACHIEVEMENT) SEP_ACHIEVEMENT,
              SUM(R2.OCT_ACHIEVEMENT) OCT_ACHIEVEMENT,
              SUM(R2.NOV_ACHIEVEMENT) NOV_ACHIEVEMENT,
              SUM(R2.DEC_ACHIEVEMENT) DEC_ACHIEVEMENT --,
              
    
              
           --   SUM(APR_QUOTAVALUE + MAY_QUOTAVALUE + JUN_QUOTAVALUE) Q1_VALUE
            --  SUM(Q1_VALUE + Q2_VALUE) Q2_VALUE,
           --   SUM(Q1_VALUE + Q2_VALUE + Q3_VALUE) Q3_VALUE,
           --   SUM(Q1_VALUE + Q2_VALUE + Q4_VALUE) Q4_VALUE
              
              
              
    FROM      (SELECT    
                        RS.COMMISSIONNAME, 
                        RS.QUOTA_NAME,
                        RS.ATTAINED_PCT,
                        --RS.ACHIEVEMENT,
                        RS.QUOTA_VALUE,
                        RS.PQ_NAME,
                        RS.PM_NAME,
    --                    RS.PM_PERIOD_STARTDATE,
    --                    RS.PM_PERIOD_ENDDATE,
    --                    RS.PM_DURATION_KEY,
                        
                        CASE WHEN MN=1 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END JAN_ACHIEVEMENT,
                        CASE WHEN MN=2 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END FEB_ACHIEVEMENT,
                        CASE WHEN MN=3 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END MAR_ACHIEVEMENT,
                        CASE WHEN MN=4 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END APR_ACHIEVEMENT,
                        CASE WHEN MN=5 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END MAY_ACHIEVEMENT,
                        CASE WHEN MN=6 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END JUN_ACHIEVEMENT,
                        CASE WHEN MN=7 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END JUL_ACHIEVEMENT,
                        CASE WHEN MN=8 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END AUG_ACHIEVEMENT,
                        CASE WHEN MN=9 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END SEP_ACHIEVEMENT,
                        CASE WHEN MN=10 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END OCT_ACHIEVEMENT,
                        CASE WHEN MN=11 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END NOV_ACHIEVEMENT,
                        CASE WHEN MN=12 THEN COALESCE(RS.ACHIEVEMENT,0) ELSE 0 END DEC_ACHIEVEMENT
                        
                         ---QUOTA
    --                    CASE WHEN MN=1 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END JAN_QUOTAVALUE,
    --                    CASE WHEN MN=2 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END FEB_QUOTAVALUE,
    --                    CASE WHEN MN=3 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END MAR_QUOTAVALUE,
    --                    CASE WHEN MN=4 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END APR_QUOTAVALUE,
    --                    CASE WHEN MN=5 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END MAY_QUOTAVALUE,
    --                    CASE WHEN MN=6 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END JUN_QUOTAVALUE,
    --                    CASE WHEN MN=7 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END JUL_QUOTAVALUE,
    --                    CASE WHEN MN=8 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END AUG_QUOTAVALUE,
    --                    CASE WHEN MN=9 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END SEP_QUOTAVALUE,
    --                    CASE WHEN MN=10 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END OCT_QUOTAVALUE,
    --                    CASE WHEN MN=11 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END NOV_QUOTAVALUE,
    --                    CASE WHEN MN=12 THEN COALESCE(RS.QUOTA_VALUE/3,0) ELSE 0 END DEC_QUOTAVALUE
                       
                        
                        
    --                    CASE WHEN MN IN (1,2,3) THEN COALESCE(RS.QUOTA_VALUE,0) ELSE 0 END Q4_VALUE,
    --                    CASE WHEN MN IN (4,5,6) THEN COALESCE(RS.QUOTA_VALUE,0) ELSE 0 END Q1_VALUE,
    --                    CASE WHEN MN IN (7,8,9) THEN COALESCE(RS.QUOTA_VALUE,0) ELSE 0 END Q2_VALUE,
    --                    CASE WHEN MN IN (10,11,12) THEN COALESCE(RS.QUOTA_VALUE,0) ELSE 0 END Q3_VALUE
                        
              FROM 
              (SELECT DISTINCT
          --Period Year Part
          PY.PERIOD_KEY as PY_PERIOD_KEY,
          PY.NAME as PY_NAME,
          PY.DURATION as PY_DURATION,
          PY.DURATION_KEY as PY_DURATION_KEY,
          date(PY.PERIOD_STARTDATE) as PY_PERIOD_STARTDATE,
          date(PY.PERIOD_ENDDATE)as PY_PERIOD_ENDDATE,
          --Period Half Year Part
          PH.PERIOD_KEY as PH_PERIOD_KEY,
          PH.NAME as PH_NAME,
          PH.DURATION as PH_DURATION,
          PH.DURATION_KEY as PH_DURATION_KEY,
          date(PH.PERIOD_STARTDATE) as PH_PERIOD_STARTDATE,
          date(PH.PERIOD_ENDDATE)as PH_PERIOD_ENDDATE,
          --Period Quarter Part
          PQ.PERIOD_KEY as PQ_PERIOD_KEY,
          PQ.NAME as PQ_NAME,
          PQ.DURATION as PQ_DURATION,
          PQ.DURATION_KEY as PQ_DURATION_KEY,
          date(PQ.PERIOD_STARTDATE) as PQ_PERIOD_STARTDATE,
          date(PQ.PERIOD_ENDDATE)as PQ_PERIOD_ENDDATE,
          --Period Month Part
          PM.PERIOD_KEY as PM_PERIOD_KEY,
          PM.NAME as PM_NAME,
          PM.DURATION as PM_DURATION,
          PM.DURATION_KEY as PM_DURATION_KEY,
          date(PM.PERIOD_STARTDATE) as PM_PERIOD_STARTDATE,
          date(PM.PERIOD_ENDDATE)as PM_PERIOD_ENDDATE,
          month(PM.PERIOD_STARTDATE) as MN,
          --Period SemiMonth Part
    --      PSM.PERIOD_KEY as PSM_PERIOD_KEY,
    --      PSM.NAME as PSM_NAME,
    --      PSM.DURATION as PSM_DURATION,
    --      PSM.DURATION_KEY as PSM_DURATION_KEY,
    --      date(PSM.PERIOD_STARTDATE) as PSM_PERIOD_STARTDATE,      
    --      date(PSM.PERIOD_ENDDATE)as PSM_PERIOD_ENDDATE,
          ---MN and PSM Check
        --  month(PSM.PERIOD_STARTDATE) as MN,
        --  RIGHT(PSM.NAME,2) PD,
        
              PE.PAYEE_KEY,
              REPLACE(REPLACE(QT.NAME,'Q_',''),' Quota','') QUOTA_NAME,
              CO.COMMISSIONNAME,
              QV.VALUE QUOTA_VALUE,
              COALESCE(CO.VALUE,0) ATTAINED_PCT,
             --ROUND(COALESCE(CO.DECIMAL1,0) / QV.VALUE,2) COM_PCT2,
              COALESCE(CO.DECIMAL1,0) ACHIEVEMENT --,CO.DECIMAL2
          from PERIOD PY
          JOIN PERIOD PH ON PH.PERIOD_STARTDATE >= PY.PERIOD_STARTDATE AND PH.PERIOD_ENDDATE <=PY.PERIOD_ENDDATE and PH.DURATION_KEY = 'PARENTDUR_half'
          JOIN PERIOD PQ ON PQ.PERIOD_STARTDATE >= PH.PERIOD_STARTDATE AND PQ.PERIOD_ENDDATE <=PH.PERIOD_ENDDATE and PQ.DURATION_KEY = 'PARENTDUR_quarter'
          JOIN PERIOD PM ON PM.PERIOD_STARTDATE >= PQ.PERIOD_STARTDATE AND PM.PERIOD_ENDDATE <=PQ.PERIOD_ENDDATE and PM.DURATION_KEY = 'BASEDUR_month'
          --JOIN PERIOD PSM ON PSM.PERIOD_STARTDATE >= PM.PERIOD_STARTDATE AND PSM.PERIOD_ENDDATE <=PM.PERIOD_ENDDATE and PSM.DURATION_KEY = 'BASEDUR_semimonth'
          
          JOIN      PAYEE PE ON 1=1
    --      JOIN      TITLEASSIGNMENT TA ON TA.PAYEE_KEY=PE.PAYEE_KEY
    --          AND TA.STARTDATE <=PQ.PERIOD_STARTDATE
    --          AND (TA.ENDDATE > PQ.PERIOD_STARTDATE OR TA.ENDDATE IS NULL)
          JOIN      QUOTAASSIGNMENT QA ON QA.PAYEE_KEY=PE.PAYEE_KEY
              AND QA.STARTDATE <=PQ.PERIOD_STARTDATE
              AND (QA.ENDDATE > PQ.PERIOD_STARTDATE OR QA.ENDDATE IS NULL)
          JOIN      QUOTA QT ON QT.QUOTA_KEY=QA.QUOTA_KEY
              AND QT.STARTDATE <=PQ.PERIOD_STARTDATE
              AND (QT.ENDDATE > PQ.PERIOD_STARTDATE OR QT.ENDDATE IS NULL)
          JOIN      QUOTAVALUE QV ON QV.PAYEE_KEY=PE.PAYEE_KEY--(QV.PAYEE_KEY=PE.PAYEE_KEY OR QV.TITLE_KEY=TA.TITLE_KEY)
              AND QV.QUOTA_KEY=QT.QUOTA_KEY
              AND QV.PERIOD_KEY=PQ.PERIOD_KEY
          LEFT JOIN COMMISSION CO ON PM.PERIOD_KEY = CO.PERIOD_KEY 
              AND CO.PAYEE_KEY = PE.PAYEE_KEY
              AND CO.COMMISSIONNAME = 'Personal Target'
               AND CO.COMMISSIONNAME=REPLACE(REPLACE(QT.NAME,'Q_',''),' Quota','')  --OR CO.COMMISSIONNAME IS NULL)
    --      LEFT JOIN RULE R ON R.RULE_KEY = CO.RULE_KEY
    --          AND R.NAME = 'Rule_BNS_Personal Target Commission'
            
              
          WHERE     PE.PAYEE_KEY in ('14911e8bb622715cb90a26edd7b4b1ffffffffeb')
          AND       QT.NAME = 'Q_Personal Target Quota'
          AND       PY.DURATION_KEY = 'PARENTDUR_year'
          --Used to get the Current Year Reports If needed
          AND       DATE(PY.PERIOD_STARTDATE) <= DATE(CURRENT DATE)
          AND       DATE(PY.PERIOD_ENDDATE)>= DATE(CURRENT DATE)
          ORDER BY  PY_PERIOD_STARTDATE, PQ_PERIOD_STARTDATE, PM_PERIOD_STARTDATE
          ) RS
          
          ) R2
    --	  Where R2.PAYEEID is not null
          GROUP BY  R2.COMMISSIONNAME, 
                    R2.QUOTA_NAME,
                   -- R2.QUOTA_VALUE,
                    R2.ATTAINED_PCT,
                    R2.PQ_NAME,
                    R2.PM_NAME
                  --  R2.PM_PERIOD_STARTDATE,
                  --  R2.PM_PERIOD_ENDDATE,
                  --  R2.PM_DURATION_KEY
       --   Order By PM_PERIOD_STARTDATE
    Last edited by jazlady; 01-12-15 at 14:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •