alter view crm7.v_echoTargetKunder
as
select
c.contact_id,
c.name as Firma,
c.number2 as Kundenummer,
ISNULL (ass.name, '') as Ansvarlig,
'Møtebooking Echo' = (select COUNT(task_idx) from crm7.APPOINTMENT where task_idx = 22 and contact_id = c.contact_id and registered > '2019-09-01 00:00:00.000'),
'Møte Potensiell kunde' = (select COUNT(task_idx) from crm7.APPOINTMENT where task_idx IN  (2, 7) and contact_id = c.contact_id and registered > '2019-09-01 00:00:00.000'),
'MXP Avtale' = (select COUNT(task_idx) from crm7.APPOINTMENT where task_idx = 121 and contact_id = c.contact_id and document_id > 0),
'Åpne salg i SO' = ISNULL((select SUM(amount) from crm7.sale where contact_id = c.contact_id and status = 1 and registered > '2019-09-01 00:00:00.000'),''),
'Solgt i SO' = ISNULL ((select SUM(amount) from crm7.sale where contact_id = c.contact_id and status = 2 and registered > '2019-09-01 00:00:00.000'),''),
'Tapt i SO' = ISNULL ((select SUM(amount) from crm7.sale where contact_id = c.contact_id and status = 3 and registered > '2019-09-01 00:00:00.000'),''),
ISNULL(crm7.f_parseSOMultiLanguageString (ul.name, 'NO'), '') as 'Pot. Vareuttak',
CASE
WHEN ul.UDList_id = 69 then 500000
WHEN ul.UDList_id = 70 then 1250000
WHEN ul.UDList_id = 71 then 3500000
WHEN ul.UDList_id = 72 then 5000000
WHEN ul.udlist_id IS NULL then ''
ELSE ul.name
END AS 'Pot. Vareuttak verdi',
ISNULL (ass.associate_id, '') as associate_id,
ISNULL (ass.group_idx, '') as group_idx,
cat.name as Kategori,
bus.name as Business,
ISNULL (pers.firstname + ' ' + pers.lastname, '') as 'Vår kontakt',
ISNULL(crm7.f_parseSOMultiLanguageString (udlist.name, 'NO'), '') as 'EchoTargetKunde',
ISNULL (usr.name, '') as Gruppe,
ucs.double07 as 'Omsetning HIÅ',
'Omsetning HIF' = ISNULL ((select top 1 CAST (ItemSalesNetYTDLY as nvarchar(100)) from pbase.dbo.PBExportToSO where c.number2 = customerid),'')
from crm7.contact c
left join crm7.associate ass on c.associate_id = ass.associate_id
left join crm7.APPOINTMENT app on c.contact_id = app.contact_id
left join crm7.sale s on c.contact_id = s.contact_id
left join crm7.UDCONTACTSMALL ucs on c.userdef_id = ucs.udcontactSmall_id
left join crm7.udlist ul on ucs.long12 = ul.UDList_id
left join crm7.CATEGORY cat on c.category_idx = cat.Category_id
left join crm7.BUSINESS bus on c.business_idx = bus.business_id
left join crm7.person pers on ass.person_id = pers.person_id
left join crm7.udlist udlist on ucs.long07 = udlist.UDList_id
left join crm7.usergroup usr on ass.group_idx = usr.UserGroup_id
group by c.contact_id, c.number2, c.name, ass.name, ul.name, ass.associate_id, ass.group_idx, cat.name, bus.name, pers.firstname,
pers.lastname, udlist.name, usr.name, ul.UDList_id, ucs.double07
GO