-- in case it's not there...
/*
CREATE FUNCTION quarter(dt date)
RETURNS integer
WITH(NOT VARIANT)

-- specified month of the year, zero based and divided by 4 plus 1 yields quarter in { 1, 2, 3, 4 }
RETURN 1 + (MONTH(dt) - 1) / 3;

END FUNCTION;
*/

CREATE VIEW sme_quarter_sum (jahr, quartal, anzahl) AS
SELECT jahr, trunc((monat-1)/3)+1, sum(anzahl) FROM sme_counter GROUP BY 1, 2;

CREATE VIEW sci_sme_quarter (jahr, quartal, anzahl, expected_anzahl, avg_anzahl_pro_tag) AS
SELECT jahr, quartal, anzahl,
       DECODE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR(quartal)),
              TRIM(TO_CHAR(DATE(CURRENT),'%Y'))||TRIM(TO_CHAR(QUARTER(CURRENT))),
              ROUND(anzahl *
                    (ADD_MONTHS(TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'), 3)
                              - TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'))::interval day to day::char(3)::int8
                    /
                    (TRUNC(CURRENT) - TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'))::interval day to day::char(3)::int8
              ),
              anzahl
       ),
       DECODE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR(quartal)),
              TRIM(TO_CHAR(DATE(CURRENT),'%Y'))||TRIM(TO_CHAR(QUARTER(CURRENT))),
              ROUND(anzahl /
                    (TRUNC(CURRENT) - TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'))::interval day to day::char(3)::int8
                    , 2
              ),
              ROUND(anzahl /
                    (ADD_MONTHS(TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'), 3)
                              - TO_DATE(TRIM(TO_CHAR(jahr))||TRIM(TO_CHAR((quartal - 1) * 3 + 1)),'%Y%m'))::interval day to day::char(3)::int8
                    , 2
              )
       )
FROM sme_quarter_sum;
