Databricks Community

ossinova · ‎03-15-2023

I want to simplify this query:

SELECT 
year(EntryDate) Year,
AccountNumber,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 1) AS jan_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 2) AS feb_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 3) AS mar_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 4) AS apr_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 5) AS may_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 6) AS jun_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 7) AS jul_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 😎 AS aug_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 9) AS sep_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 10) AS oct_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 11) AS nov_total,
sum(CreditBase - DebitBase) FILTER(WHERE month(EntryDate) = 12) AS dec_total,
sum(CreditBase - DebitBase) FILTER(WHERE quarter(EntryDate) = 1) AS q1_total,
sum(CreditBase - DebitBase) FILTER(WHERE quarter(EntryDate) = 2) AS q2_total,
sum(CreditBase - DebitBase) FILTER(WHERE quarter(EntryDate) = 3) AS q3_total,
sum(CreditBase - DebitBase) FILTER(WHERE quarter(EntryDate) = 4) AS q4_total
FROM prod.gold.finance_financeentry
WHERE EntryDate BETWEEN "2022-01-01" AND "2023-02-28"
GROUP BY year, AccountNumber
ORDER BY Year, AccountNumber;

Into something like this:

SELECT * FROM (
  SELECT  year(EntryDate) year, month(EntryDate) month, quarter(EntryDate) quarter, AccountNumber, CreditBase, DebitBase
  FROM prod.gold.finance_financeentry
  WHERE EntryDate BETWEEN DATE '2022-01-01' AND DATE '2023-02-28'
)
PIVOT (SUM(CreditBase - DebitBase) AS BalanceInBase
  FOR month
  IN (1 JAN, 2 FEB, 3 MAR, 4 APR, 5 MAY, 6 JUN,
      7 JUL, 8 AUG, 9 SEP, 10 OCT, 11 NOV, 12 DEC)
  FOR quarter
  IN (1 Q1, 2 Q2, 3 Q3, 4 Q4)
  )

Is this possible somehow?