CREATE VIEW SCI_SME_QUARTER_HELP (
	JAHR,
	QUARTAL,
	ANZAHL,
	NUM_DAYS_ACTQ,
	PAST_DAYS_ACTQ
) AS
SELECT JAHR, floor((monat-1)/3+1), ANZAHL, 
       DATEDIFF(str_to_date(concat(JAHR,'-',floor((monat-1)/3) * 3 + 1,'-1'),'%Y-%m-%d') + INTERVAL 3 MONTH,
                str_to_date(concat(JAHR,'-',floor((monat-1)/3) * 3 + 1,'-1'),'%Y-%m-%d')
       ),
       DATEDIFF(current_date(), str_to_date(concat(JAHR,'-',floor((monat-1)/3) * 3 + 1,'-1'),'%Y-%m-%d'))
FROM SCI_SME_COUNTER;

CREATE VIEW SCI_SME_QUARTER (
	JAHR,
        QUARTAL,
        ANZAHL,
        EXPECTED_ANZAHL,
        AVG_ANZAHL_PRO_TAG)
AS
SELECT
	JAHR,
	QUARTAL,
	SUM(ANZAHL),
	ROUND(CASE date_format(current_date(),'%Y') * 100 + (floor((date_format(current_date(),'%m')-1)/3)+1)
		WHEN JAHR*100+QUARTAL
		THEN SUM(ANZAHL * NUM_DAYS_ACTQ/PAST_DAYS_ACTQ)
		ELSE SUM(ANZAHL)
		END, 2),
	ROUND(CASE date_format(current_date(),'%Y') * 100 + (floor((date_format(current_date(),'%m')-1)/3)+1)
		WHEN JAHR*100+QUARTAL
		THEN SUM(ANZAHL / PAST_DAYS_ACTQ)
		ELSE SUM(ANZAHL / NUM_DAYS_ACTQ)
		END, 2)
FROM SCI_SME_QUARTER_HELP
GROUP BY JAHR, QUARTAL;

