Hi team,
so I have a query (below) that shows a list exactly how I need it... it displays perfectly... however now I need to show totals at the "Region" and "Team" level... but I can't work out how to do it... any guidance would be great...
I'm still learning SQL... so any examples within the pivot below would be awesome...
select 'QL1' as QL,
SORT_ORDER,
REGION,
TEAM,
TERRITORY,
PRIME_REP,
EMAIL_ADDERSS,
"Q1 Credit",
"Q1_Target",
round((nvl("Q1 Credit",0)/"Q1_Target")*100) as "Q1 % Attainment",
"Q2 Credit",
"Q2_Target",
round((nvl("Q2 Credit",0)/"Q1_Target")*100) as "Q2 % Attainment",
"Q3 Credit",
"Q3_Target",
round((nvl("Q3 Credit",0)/"Q3_Target")*100) as "Q3 % Attainment",
"Q4 Credit",
"Q4_Target",
round((nvl("Q4 Credit",0)/"Q4_Target")*100) as "Q4 % Attainment",
FY_QUOTA,
nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0) as "FY Credit",
round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100) as "FY % Attainment",
to_number(CASE WHEN round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100) >= 100 THEN '100'
else to_char(round(((nvl("Q1 Credit",0) + nvl("Q2 Credit",0) + nvl("Q3 Credit",0) + nvl("Q4 Credit",0))/FY_QUOTA)*100))
END) as FY_Attainment_G
from (select SP_H_SORT_ORDER as SORT_ORDER,
SP_H__REGION as REGION,
SP_H__TEAM as TEAM,
SP_BOOKINGS.TERRITORY as TERRITORY,
SP_BOOKINGS.QTR as QTR_YR,
SP_BOOKINGS.PRIME_REP as PRIME_REP,
SP_BOOKINGS.EMAILADDRESS as EMAIL_ADDERSS,
SP_FY_TARGET.FY_QUOTA as FY_QUOTA,
round(SP_FY_TARGET.Q1_Target) as "Q1_Target",
round(SP_FY_TARGET.Q2_Target) as "Q2_Target",
round(SP_FY_TARGET.Q3_Target) as "Q3_Target",
round(SP_FY_TARGET.Q4_Target) as "Q4_Target",
sum(round(TO_NUMBER(SP_BOOKINGS.SALES_CREDIT))) as SALES_CREDIT
from SP_BOOKINGS SP_BOOKINGS,
SP_FY_TARGET SP_FY_TARGET,
SP_HRCHY SP_HRCHY
where SP_HRCHY.SP_H_T_OWNER_EMAIL=SP_BOOKINGS.EMAILADDRESS and LOWER(SP_FY_TARGET.EMPLOYEE)=LOWER(SP_BOOKINGS.EMAILADDRESS) and
(:P448_GLOBAL =1 or LOWER(SP_BOOKINGS.EMAILADDRESS) = LOWER(:P448_CURRENT_EMAIL)) and (SP_BOOKINGS.REGION is not null and SP_BOOKINGS.DIRECTOR is not null and SP_BOOKINGS.PRIME_REP is not null)
group by SP_HRCHY.SP_H_SORT_ORDER, SP_HRCHY.SP_H__REGION, SP_HRCHY.SP_H__TEAM,
SP_BOOKINGS.TERRITORY, SP_BOOKINGS.QTR, SP_BOOKINGS.PRIME_REP, SP_BOOKINGS.EMAILADDRESS,
SP_FY_TARGET.FY_QUOTA,
SP_FY_TARGET.Q1_Target,
SP_FY_TARGET.Q2_Target,
SP_FY_TARGET.Q3_Target,
SP_FY_TARGET.Q4_Target
)
PIVOT ( SUM (SALES_CREDIT)
for QTR_YR
IN ('Q120' as "Q1 Credit",
'Q220' as "Q2 Credit",
'Q320' as "Q3 Credit",
'Q420' as "Q4 Credit"))