MonthlyCustomerProfitVouchers.sql

/*
SELECT
MAX(PROFITPERMONTH),
MIN(PROFITPERMONTH)
FROM
(
*/
-- MIN is -645, MAX is 12144. Voucher for our most profitable customers.
SELECT
   c.CUSTOMERNAME,
   EXTRACT(MONTH FROM i.INVOICEDATE) AS TRANSACTIONMONTH,
   EXTRACT(YEAR FROM i.INVOICEDATE) AS TRANSACTIONYEAR,
   SUM(l.LINEPROFIT) AS PROFITPERMONTH,
   SUM(l.TAXAMOUNT) AS TAXPERMONTH,
   WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) AS BUCKET,
   CASE
       WHEN WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) = 1 THEN 250
       WHEN WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) = 2 THEN 500
       WHEN WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) = 3 THEN 750
       WHEN WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) = 4 THEN 1000
       WHEN WIDTH_BUCKET(SUM(l.LINEPROFIT), 5000, 15000, 5) = 5 THEN 1250
       ELSE NULL
   END AS VOUCHER
FROM
   WWI.INVOICELINES l
JOIN
   WWI.INVOICES i
ON
   l.INVOICEID = i.INVOICEID
JOIN
   WWI.CUSTOMERS c
ON
   i.CUSTOMERID = c.CUSTOMERID
GROUP BY
   c.CUSTOMERNAME,
   EXTRACT(YEAR FROM i.INVOICEDATE),
   EXTRACT(MONTH FROM i.INVOICEDATE)
HAVING
   SUM(l.LINEPROFIT) >= 5000
ORDER BY
   EXTRACT(YEAR FROM i.INVOICEDATE),
   EXTRACT(MONTH FROM i.INVOICEDATE),
   c.CUSTOMERNAME
--)
;