CREATE VIEW "view_nwm_provision_accounts" AS

SELECT
MAX(ProvAccount.id) AS id,
YEAR(ProvAccount.valdate) AS period_year,
MONTH(ProvAccount.valdate) AS period_month,

ProvAccountType.descr AS provaccount_name,

(CASE WHEN CR.id IS NULL THEN (CASE WHEN CRSP.id IS NULL THEN CRDef.shortname ELSE CRSP.shortname END) ELSE CR.shortname END) AS provaccount_currency,
(SELECT LTRIM(RTRIM((CONCAT(companyname, ' ', NAME, ' ', surname)))) FROM adresses WHERE id = SellPartner.id) AS selling_partner_name,
SellPartner.id AS selling_partner_id,
(SELECT agencyid FROM adresses WHERE id = SellPartner.id) AS selling_partner_agencyid,
SUM(ProvAccount.amount) AS provaccount_tr_sum,
SUM(CASE WHEN ProvAccount.amount > 0 THEN ProvAccount.amount ELSE 0 END) AS provaccount_tr_sum_plus,
SUM(CASE WHEN ProvAccount.amount < 0 THEN ProvAccount.amount ELSE 0 END) AS provaccount_tr_sum_minus,

(SELECT SUM(AccBal.amount) FROM nwm_accountrecords AS AccBal WHERE AccBal.adressid = SellPartner.id AND AccBal.accounttypeid = MAX(ProvAccountType.id)
  AND ( (CASE WHEN
    ISNULL(AccBal.currencyid, 0) < 1 THEN
		(CASE WHEN MAX(CRSP.id) IS NULL THEN MAX(CRDef.id) ELSE MAX(CRSP.id) END)
	ELSE
   		AccBal.currencyid
	END) = (CASE WHEN MAX(CR.id) IS NULL THEN (CASE WHEN MAX(CRSP.id) IS NULL THEN MAX(CRDef.id) ELSE MAX(CRSP.id) END) ELSE MAX(CR.id) END)
	
  AND ((YEAR(AccBal.valdate) = YEAR(ProvAccount.valdate) AND MONTH(AccBal.valdate) <= MONTH(ProvAccount.valdate))
	  OR (YEAR(AccBal.valdate) < YEAR(ProvAccount.valdate))))
) AS provaccount_balance

FROM
nwm_accountrecords AS ProvAccount
LEFT JOIN nwm_accounttypes AS ProvAccountType ON ProvAccountType.id = ProvAccount.accounttypeid
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ProvAccount.adressid
LEFT JOIN curr_currencies AS CR ON CR.id = ProvAccount.currencyid
LEFT JOIN curr_currencies AS CRSP ON CRSP.id = SellPartner.currencyid
LEFT JOIN curr_currencies AS CRDef ON CRDef.shortname = 'CHF'

GROUP BY YEAR(ProvAccount.valdate), MONTH(ProvAccount.valdate), SellPartner.id, ProvAccountType.descr, (CASE WHEN CR.id IS NULL THEN (CASE WHEN CRSP.id IS NULL THEN CRDef.shortname ELSE CRSP.shortname END) ELSE CR.shortname END)