Created
November 28, 2017 10:22
-
-
Save mrship/6c4fdc59669d9cee6535e0aabed37fff to your computer and use it in GitHub Desktop.
divisor-by-zero.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select 'Qualified pipeline worth ' || | |
'<span class=''xl green''>' || | |
CASE WHEN currency = 'GBP' THEN '£' ELSE '€' END || | |
LTRIM(TO_CHAR(CAST(SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN VALUE ELSE 0 end) AS INTEGER), '999G999G999G999')) || | |
'</span> (' || | |
CAST(100*SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN VALUE ELSE 0 end)/SUM(VALUE) AS INTEGER) || | |
'% of total), made up of ' || | |
'<span class=''xl blue''>' || | |
LTRIM(TO_CHAR(CAST(SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN NUMBER_OF_OPPORTUNITIES end) AS INTEGER), '999G999G999G999')) || | |
'</span>' || | |
' opportunities. ' || | |
case when SUM(NUMBER_OF_OPPORTUNITIES_STALE) > 0 THEN 'Stale (qualified) opportunities worth ' || | |
'<span class=''xl red''>' || | |
case when currency = 'GBP' THEN '£' ELSE '€' end || | |
ltrim(to_char(CAST(SUM(case when OPPORTUNITY_STAGE != 'Identify' THEN VALUE_STALE ELSE 0 end) AS INTEGER), '999G999G999G999')) || | |
'</span>' | |
ELSE '' | |
end as summary | |
FROM | |
(SELECT OPPORTUNITY_STAGE, | |
CAST(SUM(NUMBER_OF_DEALS) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES, | |
CAST(SUM(CASE WHEN STALE_FLAG = 'Fresh' THEN NUMBER_OF_DEALS ELSE 0 END) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES_FRESH, | |
CAST(SUM(CASE WHEN STALE_FLAG = 'Stale' THEN NUMBER_OF_DEALS ELSE 0 END) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES_STALE, | |
CAST(SUM(CASE WHEN STALE_FLAG = 'Fresh' THEN conversion_rate*VALUE ELSE 0 END) AS INTEGER) AS VALUE_FRESH, | |
CAST(SUM(CASE WHEN STALE_FLAG = 'Stale' THEN conversion_rate*VALUE ELSE 0 END) AS INTEGER) AS VALUE_STALE, | |
CAST(SUM(conversion_rate*VALUE) AS INTEGER) AS VALUE, | |
iso_code currency | |
FROM | |
(SELECT OPPORTUNITY_STAGE, | |
CASE WHEN CLOSE_DATE >= TRUNC(SYSDATE, 'DD') THEN 'Fresh' ELSE 'Stale' END AS STALE_FLAG, | |
sum(NVL(aov,0)*multiplier) AS VALUE, | |
COUNT(DISTINCT OPPORTUNITY_ID) AS NUMBER_OF_DEALS, | |
SORT_ORDER | |
FROM DATA_ONE_SF_LOOKUP | |
INNER JOIN DATA_CORE_ONE_SF ON "DATA_ONE_SF_LOOKUP"."LINE_ITEM_ID" = DATA_CORE_ONE_SF."LINE_ITEM_ID" | |
AND "DATA_ONE_SF_LOOKUP"."EBU" = DATA_CORE_ONE_SF."EBU" | |
WHERE (("DATA_CORE_ONE_SF"."MONTH" >= DATE '2017-04-01') AND ("DATA_CORE_ONE_SF"."MONTH" <= DATE '2017-11-28') AND ("DATA_CORE_ONE_SF"."MARKET" IN ('OpCo', '_', 'Partner', 'Off-Footprint')) AND ("DATA_CORE_ONE_SF"."COUNTRY_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."ACCOUNT_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."BILLING_TYPE" IN ('Centrally Billed', '_', 'Locally Billed', 'Non_Vodafone Billed')) AND ("DATA_CORE_ONE_SF"."BUSINESS_TYPE" IN ('New', '_', 'Renewal')) AND ("DATA_CORE_ONE_SF"."PRODUCT_CATEGORY" IN ('Mobile', '_', 'Fixed', 'Other'))) | |
AND PIPELINE = 1 | |
AND ASIS = 0 | |
AND EMPLOYEE_ID = '005b0000006AptlAAC' | |
GROUP BY OPPORTUNITY_STAGE, | |
SORT_ORDER, | |
CASE WHEN CLOSE_DATE >= TRUNC(SYSDATE, 'DD') THEN 'Fresh' ELSE 'Stale' END) | |
CROSS JOIN | |
(SELECT CONVERSION_RATE, | |
ISO AS ISO_CODE | |
FROM EXCHANGE_RATES | |
WHERE sysdate BETWEEN start_date AND next_start_date | |
AND iso = 'EUR' ) | |
GROUP BY OPPORTUNITY_STAGE, | |
SORT_ORDER, | |
iso_code | |
ORDER BY SORT_ORDER) | |
GROUP BY currency |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment