CREATE VIEW "view_nwm_contractprod_signdate" AS

SELECT
MAX(Contract.id) AS id,

YEAR((CASE WHEN YEAR(Contract.signdate) < 1950 THEN Contract.creadate ELSE Contract.signdate END)) AS period_year,
MONTH((CASE WHEN YEAR(Contract.signdate) < 1950 THEN Contract.creadate ELSE Contract.signdate END)) 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 contract_branch,
COUNT(Contract.id) AS contract_count,

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

\{DatabaseGetSchemaFieldList('nwm_view_contractprod_signdate_fields')\}

SUM(CASE WHEN PriodData.contractamount IS NULL THEN ISNULL(Contract.coastnet, 0) ELSE ISNULL(PriodData.contractamount, 0) END) AS contract_firstpremiumnet,
SUM(ISNULL(PriodData.contractamount, 0)) as constract_firstperiod_contractamount,
SUM(ISNULL(PriodData.contractamount2, 0)) as constract_firstperiod_contractamount2,
SUM(ISNULL(PriodData.contractamount3, 0)) as constract_firstperiod_contractamount3,
SUM(ISNULL(PriodData.contractamount4, 0)) as constract_firstperiod_contractamount4

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

LEFT JOIN (
	SELECT 
		Contr.id as ContractID,
		MIN(PriodData.id) as PriodDataID
	FROM
		nwm_products As Contr INNER JOIN nwm_contractpaymodeperioddata AS PriodData
		ON Contr.id = PriodData.contractid
	GROUP BY Contr.id
) AS Items ON Contract.id = Items.ContractID
LEFT JOIN nwm_contractpaymodeperioddata AS PriodData
ON Items.PriodDataID = PriodData.id

GROUP BY YEAR(CASE WHEN YEAR(Contract.signdate) < 1950 THEN Contract.creadate ELSE Contract.signdate END), MONTH(CASE WHEN YEAR(Contract.signdate) < 1950 THEN Contract.creadate ELSE Contract.signdate END), SellPartner.id, ComInContractType.descr, (CASE WHEN CR.id IS NULL THEN CRDef.shortname ELSE CR.shortname END) 
