CREATE VIEW "view_nwm_commissions_payout" AS

SELECT
MAX(ComPayOut.id) AS id,
YEAR(ComPayOut.valdate) AS period_year,
MONTH(ComPayOut.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,
(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 comout_currency,
SUM(ComPayOut.amount) AS compayout_sum

FROM
nwm_payoutrecords AS ComPayOut
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ComPayOut.addressid
LEFT JOIN curr_currencies AS CR ON CR.id = ComPayOut.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(ComPayOut.valdate), MONTH(ComPayOut.valdate), SellPartner.id, (CASE WHEN CR.id IS NULL THEN (CASE WHEN CRSP.id IS NULL THEN CRDef.shortname ELSE CRSP.shortname END) ELSE CR.shortname END)