Advertisement
Guest User

Untitled

a guest
Jan 18th, 2017
70
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.84 KB | None | 0 0
  1. select a.* from (select 'BusinessEntity' as openDetailEClass,be.e_id as openDetailId,(select case when count(*) > 0 then '4F81BD' else null end from [Crm].[dbo].[subscription] s where s.businessentity_payerbusinessentity_e_id = be.e_id and not s.creationdate is null and s.creationdate <= getdate() and not s.dateofdeactivation is null and s.dateofdeactivation >= getdate()) as x_color, coalesce((select TOP 1 (case when cbi.pagenumber >= 1 and cbi.pagenumber < 4 then 'D' when cbi.pagenumber >= 4 and cbi.pagenumber < 10 then 'C' when cbi.pagenumber >= 10 and cbi.pagenumber < 21 then 'B' when cbi.pagenumber >= 21 and cbi.pagenumber < 777 then 'A' when cbi.pagenumber >= 777 and cbi.pagenumber < 999 then '!' when cbi.pagenumber >= 999 then 'A' else '' end) as area from [Crm].[dbo].[competitionbookingitem] cbi where cbi.businessentity_advertiser_e_id = be.e_id), '') + '' + (select case when count(*) > 0 then '1' else '0' end from [Crm].[dbo].[subscription] s where s.businessentity_payerbusinessentity_e_id = be.e_id and not s.creationdate is null and s.creationdate <= getdate() and not s.dateofdeactivation is null and s.dateofdeactivation >= getdate()) as P, (select TOP 1 name from [Crm].[dbo].[category] c join [Crm].[dbo].[businessentity_categories] bc on (c.e_id = bc.category_e_id) join [Crm].[dbo].[category_children] cc on (c.e_id = cc.category_parent_e_id) where bc.businessentity_e_id = be.e_id and cc.category_children_e_id = '57') as category, be.externalid as ExternalId, be.companyName as Klienti, (SELECT city from Crm.dbo.address a WHERE a.address_businessentity_e_id=be.e_id and a.type='Main') as Mesto, (CASE WHEN lastDateCumul.endtime is not null THEN CONVERT(VARCHAR, lastDateCumul.endtime, 120) + ';;' + COALESCE(lastDateCumul.lastEventResult, '') ELSE CONVERT(VARCHAR, lastDate.endtime, 120) + ';;' + COALESCE(lastDate.lastEventResult, '') END) as PosledniDatum, (CASE WHEN lastMeetingCumul.endtime is not null THEN lastMeetingCumul.endtime ELSE lastMeeting.endtime END) as Schuzka, (select TOP 1 e.subject from [Crm].[dbo].[event] e join [Crm].[dbo].[businessentity_events] pp on (e.e_id = pp.event_e_id) where (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) and (pp.businessentity_e_id = be.e_id OR (0 < (select count(*) FROM businessrelationship brs WHERE brs.businessrelationship_businessentity_e_id = be.e_id AND brs.type IN ('Other', 'AFFILIATED_COMPANY', 'MEDIA_POOL', 'HOLDING', 'Daughter company') AND brs.businessentity_relatedentity_e_id = pp.businessentity_e_id))) and not e.endtime is null and e.endtime > GETDATE() order by e.endtime ASC, e.e_id) as NejblizsiPlanovanyKrok, (select TOP 1 e.endtime from [Crm].[dbo].[event] e join [Crm].[dbo].[businessentity_events] pp on (e.e_id = pp.event_e_id) where (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) and (pp.businessentity_e_id = be.e_id OR (0 < (select count(*) FROM businessrelationship brs WHERE brs.businessrelationship_businessentity_e_id = be.e_id AND brs.type IN ('Other', 'AFFILIATED_COMPANY', 'MEDIA_POOL', 'HOLDING', 'Daughter company') AND brs.businessentity_relatedentity_e_id = pp.businessentity_e_id))) and not e.endtime is null and e.endtime > GETDATE() order by e.endtime ASC, e.e_id) as NejblizsiPlanovanyKrokDatum, (select TOP 1 a.subject + ' ' + convert(varchar, a.startdate, 104) + ' - ' + coalesce(convert(varchar, a.enddate, 104), '') from [Crm].[dbo].[agreement] a where a.businessentity_customer_e_id = be.e_id and a.subject not like '%X%' and a.startdate <= GETDATE() order by case when a.enddate is null then YEAR(GETDATE())*356 + MONTH(a.startdate)*30 + DAY(a.startdate) else YEAR(a.enddate)*356 + MONTH(a.enddate)*30 + DAY(a.enddate) end) as smlouvy, (select TOP 1 a.enddate from [Crm].[dbo].[agreement] a where a.businessentity_customer_e_id = be.e_id and a.subject not like '%X%' and a.startdate <= GETDATE() order by case when a.enddate is null then YEAR(GETDATE())*356 + MONTH(a.startdate)*30 + DAY(a.startdate) else YEAR(a.enddate)*356 + MONTH(a.enddate)*30 + DAY(a.enddate) end) as smlouvyEndDate from businessentity be LEFT OUTER join (SELECT (CASE WHEN brs.e_id IS NULL THEN p.businessentity_e_id ELSE brs.businessrelationship_businessentity_e_id END) as be_e_id, MAX(e.endtime) as endtime, (SELECT TOP 1 er.name FROM eventresult er JOIN event ie ON ie.eventresult_result_e_id = er.e_id JOIN businessentity_events bev ON ie.e_id = bev.event_e_id WHERE ie.endtime = MAX(e.endtime) and bev.businessentity_e_id = (CASE WHEN brs.e_id IS NULL THEN p.businessentity_e_id ELSE brs.businessrelationship_businessentity_e_id END)) as lastEventResult FROM [Crm].[dbo].[event] e JOIN [Crm].[dbo].[businessentity_events] p ON (e.e_id = p.event_e_id) LEFT OUTER JOIN [Crm].[dbo].[businessrelationship] brs ON (p.businessentity_e_id = businessentity_relatedentity_e_id) WHERE (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) AND e.endtime is not null and e.endtime <= GETDATE() and (brs.type is null OR brs.type IN ('Other', 'AFFILIATED_COMPANY', 'MEDIA_POOL', 'HOLDING', 'Daughter company')) GROUP BY (CASE WHEN brs.e_id IS NULL THEN p.businessentity_e_id ELSE brs.businessrelationship_businessentity_e_id END)) lastDateCumul ON (lastDateCumul.be_e_id = be.e_id) LEFT OUTER join ( SELECT p.businessentity_e_id as be_e_id, MAX(e.endtime) as endtime, (SELECT TOP 1 er.name FROM eventresult er JOIN event ie ON ie.eventresult_result_e_id = er.e_id JOIN businessentity_events bev ON ie.e_id = bev.event_e_id WHERE ie.endtime = MAX(e.endtime) and bev.businessentity_e_id = p.businessentity_e_id) as lastEventResult FROM [Crm].[dbo].[event] e JOIN [Crm].[dbo].[businessentity_events] p ON (e.e_id = p.event_e_id) WHERE (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) AND e.endtime is not null and e.endtime <= GETDATE() GROUP BY p.businessentity_e_id) lastDate ON (lastDate.be_e_id = be.e_id) LEFT OUTER join ( SELECT (CASE WHEN brs.e_id IS NULL THEN p.businessentity_e_id ELSE brs.businessrelationship_businessentity_e_id END) as be_e_id, MAX(e.endtime) as endtime FROM [Crm].[dbo].[event] e JOIN [Crm].[dbo].[businessentity_events] p ON (e.e_id = p.event_e_id) LEFT OUTER JOIN [Crm].[dbo].[businessrelationship] brs ON (p.businessentity_e_id = businessentity_relatedentity_e_id) WHERE (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) AND e.endtime is not null and e.endtime <= GETDATE() and e.eventtype_type_e_id = 3 and (brs.type is null OR brs.type IN ('Other', 'AFFILIATED_COMPANY', 'MEDIA_POOL', 'HOLDING', 'Daughter company')) GROUP BY (CASE WHEN brs.e_id IS NULL THEN p.businessentity_e_id ELSE brs.businessrelationship_businessentity_e_id END)) lastMeetingCumul ON (lastMeetingCumul.be_e_id = be.e_id) LEFT OUTER join ( SELECT p.businessentity_e_id as be_e_id, MAX(e.endtime) as endtime FROM [Crm].[dbo].[event] e JOIN [Crm].[dbo].[businessentity_events] p ON (e.e_id = p.event_e_id) WHERE (e.contactperson_createdby_e_id = 1 or 0 < (select count(*) from contactperson_events cpe where cpe.event_e_id = e.e_id and cpe.contactperson_e_id = 1)) AND e.endtime is not null and e.endtime <= GETDATE() and e.eventtype_type_e_id = 3 GROUP BY p.businessentity_e_id) lastMeeting ON (lastMeeting.be_e_id = be.e_id) where 0 < (select count(*) from [Crm].[dbo].[keyaccountmanager] rm WHERE (rm.contactperson_contactperson_e_id = 1) AND (rm.keyaccountmanager_businessentity_e_id = be.e_id))) a order by (case when a.NejblizsiPlanovanyKrokDatum is null then 0 else 1 end) DESC, a.NejblizsiPlanovanyKrokDatum ASC, (case when smlouvyEndDate is null then 0 else 1 end) DESC, a.smlouvyEndDate ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement