CREATE VIEW "view_nwm_commissions_in" AS

SELECT
MAX(ComIn.id) AS id,
YEAR(ComIn.valdate) AS period_year,
MONTH(ComIn.valdate) AS period_month,
(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,
ComInContractType.descr AS comin_branch,
SUM(ComIn.amountincome) AS comincalc_sum,
SUM(ComIn.amountincomeincome) AS comin_sum,

(CASE WHEN CR.id IS NULL THEN CRDef.shortname ELSE CR.shortname END) AS comout_currency,

SUM(CASE WHEN ComIn.amountincome > 0 THEN ComIn.amountincome ELSE 0 END) AS comincalc_prov,
SUM(CASE WHEN ComIn.amountincome < 0 THEN ComIn.amountincome ELSE 0 END) AS comincalc_storno,

SUM(CASE WHEN ComIn.amountincomeincome > 0 THEN ComIn.amountincomeincome ELSE 0 END) AS comin_prov,
SUM(CASE WHEN ComIn.amountincomeincome < 0 THEN ComIn.amountincomeincome ELSE 0 END) AS comin_storno,

COUNT(DISTINCT ComInContract.id) AS comin_contractcount,

COUNT(DISTINCT CASE WHEN ComIn.amountincome > 0 THEN ComInContract.id ELSE NULL END) AS comincalc_contractcount_prov,
COUNT(DISTINCT CASE WHEN ComIn.amountincome < 0 THEN ComInContract.id ELSE NULL END) AS comincalc_contractcount_storno,

COUNT(DISTINCT CASE WHEN ComIn.amountincomeincome > 0 THEN ComInContract.id ELSE NULL END) AS comin_contractcount_prov,
COUNT(DISTINCT CASE WHEN ComIn.amountincomeincome < 0 THEN ComInContract.id ELSE NULL END) AS comin_contractcount_storno

FROM
nwmgc_commissions AS ComIn
LEFT JOIN nwm_products AS ComInContract ON ComInContract.id = ComIn.contractid
LEFT JOIN nwm_producttypes AS ComInContractType ON ComInContractType.id = ComInContract.prtypeid
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ComInContract.ownerid
LEFT JOIN curr_currencies AS CR ON CR.id = ComIn.currencyid
LEFT JOIN curr_currencies AS CRDef ON CRDef.shortname = 'CHF'

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