select * from ( select e.eventName, e.eventSubjectId, e.eventSubjectType, e.eventTime, null eventSubjectDetail, null territoryName from tbl_SYS_Events e where eventId = -1 union select e.eventName, e.eventSubjectId, e.eventSubjectType, e.eventTime, convert( varchar, opr.operatorId ) + '|' + opr.companyName + '|' + convert( varchar, coalesce( ter.territoryId, 0 )) + '|' + coalesce( ter.name, '' ) eventSubjectDetail, coalesce( ter.name, '' ) territoryName from tbl_SYS_Events e inner join tbl_OPR_ClientOperators opr on opr.ownerId = e.ownerId and opr.fsltablecode = e.ownerType and opr.operatorId = e.eventSubjectId and opr.crmActive = 'Y' inner join tbl_TER_Territories ter on ter.territoryId = opr.territoryId and ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode where eventName = 'operatorAdded' and e.eventTime > dateadd( day, #attributes.lookbackDays * -1#, { fn now() }) and e.ownerId = and e.ownerType = and ( 1 = 0 or ter.territoryId in ( ) or opr.operatorId in ( ) ) union select e.eventName, e.eventSubjectId, e.eventSubjectType, e.eventTime, case when spl.partnerType = '???' then '|||||' when spl.partnerType = 'OPR' then spl.opportunityName + '|' + spl.partnerType + '|' + convert( varchar, opr.operatorId ) + '|' + opr.companyName + '|' + convert( varchar, coalesce( oter.territoryId, 0 )) + '|' + coalesce( oter.name, '' ) when spl.partnerType = 'CDR' then spl.opportunityName + '|' + spl.partnerType + '|' + convert( varchar, cdr.cdr_recordId ) + '|' + cdr.cdr_dstName + '|' + convert( varchar, coalesce( cter.territoryId, 0 )) + '|' + coalesce( cter.name, '' ) else '|||||' end eventSubjectDetail, case when spl.partnerType = '???' then null when spl.partnerType = 'OPR' then coalesce( oter.name, '' ) when spl.partnerType = 'CDR' then coalesce( cter.name, '' ) else null end territoryName from tbl_SYS_Events e inner join tbl_SPL_Opportunities spl on spl.ownerId = e.ownerId and spl.ownerType = e.ownerType and spl.opportunityId = e.eventSubjectId left outer join tbl_OPR_ClientOperators opr on opr.operatorId = spl.partnerId and opr.ownerId = spl.ownerId and opr.fsltablecode = spl.ownerType and opr.crmActive = 'Y' and opr.territoryId in ( ) left outer join tbl_TER_Territories oter on oter.territoryId = opr.territoryId and oter.ownerId = opr.ownerId and oter.fsl_tablecode = opr.fsltablecode left outer join tbl_CDR_Distributors cdr on cdr.cdr_recordId = spl.partnerId and cdr.ownerId = spl.ownerId and cdr.fsl_tablecode = spl.ownerType and cdr.crmActive = 'Y' and cdr.cdr_territoryId in ( ) left outer join tbl_TER_Territories cter on cter.territoryId = cdr.cdr_territoryId and cter.ownerId = cdr.ownerId and cter.fsl_tablecode = cdr.fsl_tablecode where ( eventName = 'opportunityCreated' ) and e.eventTime > dateadd( day, #attributes.lookbackDays * -1#, { fn now() }) and e.ownerId = and e.ownerType = and ( 1 = 0 or ( opr.operatorId is not null and spl.partnerType = 'OPR' ) or ( cdr.cdr_recordId is not null and spl.partnerType = 'CDR') ) union select e.eventName, e.eventSubjectId, e.eventSubjectType, e.eventTime, case when wbl.subjectType = 'OPR' then wbl.postTitle + '|' + wbl.subjectType + '|' + convert( varchar, opr.operatorId ) + '|' + opr.companyName + '|' + convert( varchar, coalesce( oter.territoryId, 0 )) + '|' + coalesce( oter.name, '' ) else wbl.postTitle + '|' + wbl.subjectType + '|' + convert( varchar, cdr.cdr_recordId ) + '|' + cdr.cdr_dstName + '|' + convert( varchar, coalesce( cter.territoryId, 0 )) + '|' + coalesce( cter.name, '' ) end eventSubjectDetail, case when wbl.subjectType = '???' then null when wbl.subjectType = 'OPR' then coalesce( oter.name, '' ) when wbl.subjectType = 'CDR' then coalesce( cter.name, '' ) else null end territoryName from tbl_SYS_Events e inner join tbl_WEBLog_Posts wbl on wbl.ownerId = e.ownerId and wbl.ownerType = e.ownerType and wbl.postId = e.eventSubjectId left outer join tbl_OPR_ClientOperators opr on opr.operatorId = wbl.subjectId and opr.ownerId = wbl.ownerId and opr.fsltablecode = wbl.ownerType and opr.crmActive = 'Y' and ( 1 = 0 or opr.territoryId in ( ) or opr.operatorId in ( ) ) left outer join tbl_CDR_Distributors cdr on cdr.cdr_recordId = wbl.subjectId and cdr.ownerId = wbl.ownerId and cdr.fsl_tablecode = wbl.ownerType and cdr.crmActive = 'Y' and ( 1 = 0 or cdr.cdr_territoryId in ( ) or cdr.cdr_recordId in ( ) ) left outer join tbl_TER_Territories oter on oter.territoryId = opr.territoryId and oter.ownerId = opr.ownerId and oter.fsl_tablecode = opr.fsltablecode left outer join tbl_TER_Territories cter on cter.territoryId = cdr.cdr_territoryId and cter.ownerId = cdr.ownerId and cter.fsl_tablecode = cdr.fsl_tablecode where ( eventName = 'weblogPost' ) and e.eventTime > dateadd( day, #attributes.lookbackDays * -1#, { fn now() }) and e.ownerId = and e.ownerType = and ( ( opr.operatorId is not null and wbl.subjectType = 'OPR' ) or ( cdr.cdr_recordId is not null and wbl.subjectType = 'CDR')) ) x order by territoryName, eventTime desc