SQL to Handle many where conditions ( create dynamic)

I am trying to write this the most effectively.  I have about 42 values that are past from a multiselect combo in an SSRS report.
These values will represent the column name.

If the user selects all or select discrete choices it should return those values.   I will be making an option called none in the dropdown as well so if that I can give the user a way to ignore and we just ignore this set of parameters.

This is just a snippet of the proc.

Here I am stuffing the values of the dropdown into a temp table.

I have created one example of the where statement..but I think this needs to some how be dynamic.

The column is a bit...so after verifying the column exists I also check that its true as well.

Select * into #tempTable
from (SELECT splitdata FROM dbo.fnSplitString('@Services',',') ) s
declare @servicesCOUNT int
select @servicesCOUNT = count(*) from #tempTable
select * from #tempTable
drop table #tempTable







select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]

from 
cadoc_crm..tClient c	
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 

where 


'ic_lSTaxCCorp' IN ( select * from #tempTable) and cf.ic_lSTaxCCorp = 1

Open in new window

LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robb HillSenior .Net DeveloperAuthor Commented:
It would be nice if I could just create the where clause  by whats in the dropdown.

Then It wouldnt matter that there are 42 items..

if the user only chose 1 or 2 items....it would be 1 or 2 or statements.
0
Robb HillSenior .Net DeveloperAuthor Commented:
I would have the values in the temptable needed to make the where clause
0
Russell FoxDatabase DeveloperCommented:
If you set the big query as the source of your report, you can just use the @Services multi-select values in the WHERE clause:
WHERE 'ic_lSTaxCCorp' IN (@Services)

Open in new window

It's a little trickier if you're passing @Serivces to a stored procedure to generate results, but you should be able to dump the temp table and just do the split in the big query:
WHERE 'ic_lSTaxCCorp' IN (dbo.fnSplitString('@Services', ','))

Open in new window

or in a JOIN
FROM	cadoc_crm..tClient c
		LEFT JOIN cadoc_crm..tClientCustomFields cf ON c.nid = cf.Clientnid
		INNER JOIN ( 
			SELECT splitdata
			FROM dbo.fnSplitString('@Services', ',')
			) s
			ON c.ic_lSTaxCCorp = s.splitdata
WHERE cf.ic_lSTaxCCorp = 1;

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Robb HillSenior .Net DeveloperAuthor Commented:
This where clause coming from temp table has to check against this many columns...im trying to make this more effective by only checking if its in the list ...so I either need a curser here or dyanamic..sql I think

'ic_lSTaxCCorp' IN ( select * from #tempTable) and cf.ic_lSTaxCCorp = 1

 
cf.ic_lSTaxSCorp 
cf.ic_lSTaxFiduciary 
cf.ic_lSTaxForm 
cf.ic_lSTaxEstate 
cf.ic_lSTaxNonProfit 
cf.ic_lSTaxPartnership 
cf.ic_lSTaxMassBusTrusts 
cf.ic_lSTaxPensions 
cf.ic_lSTaxSalesPersPropTax 
cf.ic_lSTaxPayroll1099andW2 
cf.ic_lSTaxInternational 
cf.ic_lSTaxIndividual 
cf.ic_lSTaxScheduleC 
cf.ic_lSTaxScheduleE 
cf.ic_lSTaxEstatePlanIndiv 
cf.ic_lSTaxGiftTaxRelated 
cf.ic_lSTaxInternationalIndiv 
cf.ic_lSBKPWriteup 
cf.ic_lSBKPWriteupMonthly 
cf.ic_lSBKPWriteupQuarterly 
cf.ic_lSBKPGrayPay 
cf.ic_lSBKPOutsourcedAccounting 
cf.ic_lSAandAAOP 
cf.ic_lSAandAAudit 
cf.ic_lSAandAEBPA 
cf.ic_lSAandACompilationMonthly 
cf.ic_lSAandACompilation 
cf.ic_lSAandAOverheadAudit  
cf.ic_lSAandAReview 
cf.ic_lSAandAInternalAuditing 
cf.ic_lSMASAccountingConsultation 
cf.ic_lSMASFuelExchange 
cf.ic_lSMASFuelTrack 
cf.ic_lSMASMgmtConsultation 
cf.ic_lSMASLitigationSupport 
cf.ic_lSMASPersonalFinancialPlanning a
cf.ic_lSMASMandAConsulting 
cf.ic_cSMcDonaldsServices 
cf.ic_lCSValuationAppraisals 
cf.ic_cSGEMFinancialPlan 
cf.ic_cSOtherServices 

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
I would only want to evaluate the above columns if they are in the temp table...the temp table could have the word none returned..meaning 0 rows. or all or just some.

In the case of just some...I would hope the where clause could be smaller.
0
Robb HillSenior .Net DeveloperAuthor Commented:
Here is it written out mostly outside of handling a select all or when the user selects none.

But if the user only selected 3 items it would still do all of this work..please see where clause.

Also keep in mind this is just a segment of the entire proc..I have about 13 other big where clauses as well..that are against other data in this larger stored proc.


select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]

from 
cadoc_crm..tClient c	
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 

where 


'ic_lSTaxCCorp' IN ( select * from #tempTable) and cf.ic_lSTaxCCorp = 1
OR
'ic_lSTaxSCorp' IN ( select * from #tempTable) and cf.ic_lSTaxSCorp = 1
OR
'ic_lSTaxFiduciary' IN ( select * from #tempTable) and cf.ic_lSTaxFiduciary = 1
OR
'ic_lSTaxForm' IN ( select * from #tempTable) and cf.ic_lSTaxForm = 1
OR
'ic_lSTaxEstate' IN ( select * from #tempTable) and cf.ic_lSTaxEstate = 1
OR
'ic_lSTaxNonProfit' IN ( select * from #tempTable) and cf.ic_lSTaxNonProfit = 1
OR
'ic_lSTaxMassBusTrusts' IN ( select * from #tempTable) and cf.ic_lSTaxMassBusTrusts = 1
OR
'ic_lSTaxPensions' IN ( select * from #tempTable) and cf.ic_lSTaxPensions = 1
OR
'ic_lSTaxSalesPersPropTax' IN ( select * from #tempTable) and cf.ic_lSTaxSalesPersPropTax = 1
OR
'ic_lSTaxPayroll1099andW2' IN ( select * from #tempTable) and cf.ic_lSTaxPayroll1099andW2 = 1
OR
'ic_lSTaxInternational' IN ( select * from #tempTable) and cf.ic_lSTaxInternational = 1
OR
'ic_lSTaxIndividual' IN ( select * from #tempTable) and cf.ic_lSTaxIndividual = 1
OR
'ic_lSTaxScheduleC' IN ( select * from #tempTable) and cf.ic_lSTaxScheduleC = 1
OR
'ic_lSTaxScheduleE' IN ( select * from #tempTable) and cf.ic_lSTaxScheduleE = 1
OR
'ic_lSTaxEstatePlanIndiv' IN ( select * from #tempTable) and cf.ic_lSTaxEstatePlanIndiv = 1
OR
'ic_lSTaxGiftTaxRelated' IN ( select * from #tempTable) and cf.ic_lSTaxGiftTaxRelated = 1
OR
'ic_lSTaxInternationalIndiv' IN ( select * from #tempTable) and cf.ic_lSTaxInternationalIndiv = 1
OR
'ic_lSBKPWriteup' IN ( select * from #tempTable) and cf.ic_lSBKPWriteup = 1
OR
'ic_lSBKPWriteupMonthly' IN ( select * from #tempTable) and cf.ic_lSBKPWriteupMonthly = 1
OR
'ic_lSBKPWriteupQuarterly' IN ( select * from #tempTable) and cf.ic_lSBKPWriteupQuarterly = 1
OR
'ic_lSBKPGrayPay' IN ( select * from #tempTable) and cf.ic_lSBKPGrayPay = 1
OR
'ic_lSBKPOutsourcedAccounting' IN ( select * from #tempTable) and cf.ic_lSBKPOutsourcedAccounting = 1
OR
'ic_lSAandAAOP' IN ( select * from #tempTable) and cf.ic_lSAandAAOP = 1
OR
'ic_lSAandAAudit' IN ( select * from #tempTable) and cf.ic_lSAandAAudit = 1
OR
'ic_lSAandAEBPA' IN ( select * from #tempTable) and cf.ic_lSAandAEBPA = 1
OR
'ic_lSAandACompilationMonthly' IN ( select * from #tempTable) and cf.ic_lSAandACompilationMonthly = 1
OR
'ic_lSAandACompilation' IN ( select * from #tempTable) and cf.ic_lSAandACompilation = 1
OR
'ic_lSAandAOverheadAudit' IN ( select * from #tempTable) and cf.ic_lSAandAOverheadAudit = 1
OR
'ic_lSAandAReview' IN ( select * from #tempTable) and cf.ic_lSAandAReview = 1
OR
'ic_lSAandAInternalAuditing' IN ( select * from #tempTable) and cf.ic_lSAandAInternalAuditing = 1
OR
'ic_lSMASAccountingConsultation' IN ( select * from #tempTable) and cf.ic_lSMASAccountingConsultation = 1
OR
'ic_lSMASFuelExchange' IN ( select * from #tempTable) and cf.ic_lSMASFuelExchange = 1
OR
'ic_lSMASFuelTrack' IN ( select * from #tempTable) and cf.ic_lSMASFuelTrack = 1
OR
'ic_lSMASMgmtConsultation' IN ( select * from #tempTable) and cf.ic_lSMASMgmtConsultation = 1
OR
'ic_lSMASLitigationSupport' IN ( select * from #tempTable) and cf.ic_lSMASLitigationSupport = 1
OR
'ic_lSMASPersonalFinancialPlanning' IN ( select * from #tempTable) and cf.ic_lSMASPersonalFinancialPlanning = 1
OR
'ic_lSMASMandAConsulting' IN ( select * from #tempTable) and cf.ic_lSMASMandAConsulting = 1
OR
'ic_lSMASMgmtConsultation' IN ( select * from #tempTable) and cf.ic_lSMASMgmtConsultation = 1
OR
'ic_cSMcDonaldsServices' IN ( select * from #tempTable) and cf.ic_cSMcDonaldsServices = 1
OR
'ic_cSOtherServices' IN ( select * from #tempTable) and cf.ic_cSOtherServices = 1

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Hey Robb,

Try changing like below..


from
cadoc_crm..tClient c      
left join cadoc_crm..tClientCustomFields cf
      on c.nid = cf.Clientnid
INNER JOIN #tempTable t on ( cf.ic_lSTaxCCorp = 1 OR cf.ic_lSTaxSCorp = 1 OR ....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robb HillSenior .Net DeveloperAuthor Commented:
that logic is part of it..but you neeed the select in..because you have to determine if its been selected as a column to evaluate
0
Robb HillSenior .Net DeveloperAuthor Commented:
Ok here is my close to finished product.

The proc is not returning values on the Else if or the final else..

If I comment out those last two eles in this proc..and rerun from the SSRS...I can select none selected...or -1 from the if statement.
And it will return records.

If I run the other two statements..no records are returned......something is wrong in my or statements..or my conditions....


Here is the massive stored proc..in full

USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[CRMDesignation]    Script Date: 11/15/2017 11:06:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER  procedure [dbo].[CRMDesignation]


@SiteCode varchar(max),
@DateClientCreatedStart datetime = '1/1/1970 12:00:000',
@DateClientCreatedEnd datetime = '1/1/9999 12:00:000',
@NativeLinked varchar(20),
@ActiveClient int,
@ActiveContact varchar(20),
@State varchar(max),
@Title varchar(max),
@Industry varchar(max),
@EntityType varchar(max),
@ClientRating varchar(max),
@PartnerDirector varchar(max),
@ReferralSource varchar(max),
@ReferralsourceName varchar(max),
@Services VARCHAR(MAX)    

as

Begin

Select * into ##tempTable
from (SELECT splitdata FROM dbo.fnSplitString(@Services,',') ) s
declare @servicesCOUNT int
select @servicesCOUNT = count(*) from ##tempTable



if @servicesCOUNT = 1 and @Services = '-1'

Begin


select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
t2.[Native/Linked],
t2.Designate,
t2.[Role],
t2.ContactStatus,
t2.[First Name],
t2.[Last Name],
t2.Salutation,
t2.Title,
t2.Email,
t2.[Email 2],
t2.[Email 3],
c.cPhone1 as [Entity Main Phone],
t2.[Work Phone],
t2.[Home Phone],
t2.[Cell Phone],
t2.[Address 1],
t2.[Address 2],
t2.[Address 3],
t2.City,
t2.[State],
t2.Zip,
t2.Country,
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]


     from

(
select 
n1.nidClient, n1.Designate,n1.[Role],n1.[First Name],n1.[Last Name],n1.Salutation,n1.Title,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone], 
COALESCE(ContactAddressNid,ClientAddressNid) as [nid],
COALESCE(ContactAddress1,ClientAddress1) as [Address 1],
COALESCE(ContactAddress2,ClientAddress2) as [Address 2],
COALESCE(ContactAddress3,ClientAddress3) as [Address 3],
COALESCE(ContactAddressCity,ClientAddressCity) as [City],
COALESCE(ContactAddressState,ClientAddressState) as [State],
COALESCE(ContactAddressZip,ClientAddressZip) as [Zip],
COALESCE(ContactAddressCountry,ClientAddressCountry) as [Country],
n1.[Native/Linked],n1.ContactStatus



from

--native
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title], 
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c 
inner  join cadoc_system..tsitexcrmclient xc 
	on  c.nidclient = xc.nidclient
inner join cadoc_system..tsite s 
	on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
	
	
WHERE 
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root' ) n1

 Union 
--linked
select 
t1.nidClient, t1.Designate,t1.[Role],t1.[First Name],t1.[Last Name],t1.Salutation,t1.Title,
t1.Email,t1.[Email 2],t1.[Email 3],t1.[Work Phone],t1.[Home Phone],t1.[Cell Phone], 
COALESCE(ClientAddressNid,ContactAddressNid) as [nid],
COALESCE(ClientAddress1,ContactAddress1) as [Address 1],
COALESCE(ClientAddress2,ContactAddress2) as [Address 2],
COALESCE(ClientAddress3,ContactAddress3) as [Address 3],
COALESCE(ClientAddressCity,ContactAddressCity) as [City],
COALESCE(ClientAddressState,ContactAddressState) as [State],
COALESCE(ClientAddressZip,ContactAddressZip) as [Zip],
COALESCE(ClientAddressCountry,ContactAddressCountry) as [Country],
t1.[Native/Linked],t1.ContactStatus



from

--linked contacts
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
xc.nIDClient,xc.cDesLinked as Designate, xc.cLabel as [Role],
n.cFirstName as [First Name], n.cLastName as [Last Name], n.cSalutation as [Salutation],n.cTitle as [Title], n.cEmail as [Email],n.cEmailAlt as [Email 2],n.cEmailAlt2 as [Email 3],
n.cPhone as [Work Phone], n.cHomePhone as [Home Phone], n.cCellPhone as [Cell Phone],
(LTRIM(n.cLastName)) + ', ' + RTRIM(LTRIM(n.cFirstName)) AS PrimaryContact,'Linked' as 'Native/Linked',
(CASE WHEN n.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
from cadoc_crm..tclient c 
inner join cadoc_crm..tclientXContact xc
	on c.nid = xc.nIDClient
inner join cadoc_crm..tContact n
	on xc.nIDContact = n.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = n.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on xc.nIDClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
Where n.nid not in (
	Select    c.nid
	FROM         
		cadoc_system..tSite s 
	INNER JOIN cadoc_system..tSiteXCrmClient  xc
		ON s.nid = xc.nidSite 
	INNER JOIN cadoc_crm.dbo.tContact c 
		ON xc.nIdClient =c.nidClient
	WHERE     s.cSiteCode = 'Root'
	 and c.cLastName not in('POOL','USER','ADMINISTRATOR') 
)
 and n.cLastName not in('POOL','USER','ADMINISTRATOR')  
)t1
)t2

Left Outer Join 

cadoc_crm..tClient c 
	on t2.nidClient = c.nid
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID

where 
CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or (@NativeLinked = 'All' and t2.[Native/Linked] = t2.[Native/Linked]))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or (@ActiveClient = 2 and c.lActive = c.lActive))
And 
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or (@ActiveContact = 'All' and t2.ContactStatus = t2.ContactStatus))
AND
t2.[State] IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
AND
case when replace(t2.Title, ',', '') = '' then 'None' else replace(t2.Title, ',', '') end IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) 
AND
case when replace(c.cIndustry, ',', '') = '' then 'None' else  replace(c.cIndustry, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@Industry,',') ) 
AND
case when replace(c.cUDF5,',','') = '' or  replace(RTRIM(LTRIM(c.cUDF5)),',','') = 'No Selection' then 'None'  else replace(c.cUdf5, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@EntityType,',') ) 
AND
case when replace(cf.ic_cClientRating, ',', '') = '' then 'None' else replace(cf.ic_cClientRating, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) 
AND
case when replace(c.cUdf7, ',', '') = '' then 'None' else replace(c.cUdf7, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@PartnerDirector,',') )
AND
case when replace(cf.ic_cReferralSource, ',', '') = '' then 'None' else replace(cf.ic_cReferralSource, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') )
AND
case when replace(cf.ic_cReferralSourceName, ',', '') = '' then 'None' else replace(cf.ic_cReferralSourceName, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralsourceName,',') ) 
And @Services is null

order by c.cName asc

drop table ##tempTable

END
ELSE if @servicesCOUNT > 41
Begin


select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
t2.[Native/Linked],
t2.Designate,
t2.[Role],
t2.ContactStatus,
t2.[First Name],
t2.[Last Name],
t2.Salutation,
t2.Title,
t2.Email,
t2.[Email 2],
t2.[Email 3],
c.cPhone1 as [Entity Main Phone],
t2.[Work Phone],
t2.[Home Phone],
t2.[Cell Phone],
t2.[Address 1],
t2.[Address 2],
t2.[Address 3],
t2.City,
t2.[State],
t2.Zip,
t2.Country,
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]


     from

(
select 
n1.nidClient, n1.Designate,n1.[Role],n1.[First Name],n1.[Last Name],n1.Salutation,n1.Title,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone], 
COALESCE(ContactAddressNid,ClientAddressNid) as [nid],
COALESCE(ContactAddress1,ClientAddress1) as [Address 1],
COALESCE(ContactAddress2,ClientAddress2) as [Address 2],
COALESCE(ContactAddress3,ClientAddress3) as [Address 3],
COALESCE(ContactAddressCity,ClientAddressCity) as [City],
COALESCE(ContactAddressState,ClientAddressState) as [State],
COALESCE(ContactAddressZip,ClientAddressZip) as [Zip],
COALESCE(ContactAddressCountry,ClientAddressCountry) as [Country],
n1.[Native/Linked],n1.ContactStatus



from

--native
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title], 
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c 
inner  join cadoc_system..tsitexcrmclient xc 
	on  c.nidclient = xc.nidclient
inner join cadoc_system..tsite s 
	on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
	
	
WHERE 
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root' ) n1

 Union 
--linked
select 
t1.nidClient, t1.Designate,t1.[Role],t1.[First Name],t1.[Last Name],t1.Salutation,t1.Title,
t1.Email,t1.[Email 2],t1.[Email 3],t1.[Work Phone],t1.[Home Phone],t1.[Cell Phone], 
COALESCE(ClientAddressNid,ContactAddressNid) as [nid],
COALESCE(ClientAddress1,ContactAddress1) as [Address 1],
COALESCE(ClientAddress2,ContactAddress2) as [Address 2],
COALESCE(ClientAddress3,ContactAddress3) as [Address 3],
COALESCE(ClientAddressCity,ContactAddressCity) as [City],
COALESCE(ClientAddressState,ContactAddressState) as [State],
COALESCE(ClientAddressZip,ContactAddressZip) as [Zip],
COALESCE(ClientAddressCountry,ContactAddressCountry) as [Country],
t1.[Native/Linked],t1.ContactStatus



from

--linked contacts
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
xc.nIDClient,xc.cDesLinked as Designate, xc.cLabel as [Role],
n.cFirstName as [First Name], n.cLastName as [Last Name], n.cSalutation as [Salutation],n.cTitle as [Title], n.cEmail as [Email],n.cEmailAlt as [Email 2],n.cEmailAlt2 as [Email 3],
n.cPhone as [Work Phone], n.cHomePhone as [Home Phone], n.cCellPhone as [Cell Phone],
(LTRIM(n.cLastName)) + ', ' + RTRIM(LTRIM(n.cFirstName)) AS PrimaryContact,'Linked' as 'Native/Linked',
(CASE WHEN n.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
from cadoc_crm..tclient c 
inner join cadoc_crm..tclientXContact xc
	on c.nid = xc.nIDClient
inner join cadoc_crm..tContact n
	on xc.nIDContact = n.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = n.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on xc.nIDClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
Where n.nid not in (
	Select    c.nid
	FROM         
		cadoc_system..tSite s 
	INNER JOIN cadoc_system..tSiteXCrmClient  xc
		ON s.nid = xc.nidSite 
	INNER JOIN cadoc_crm.dbo.tContact c 
		ON xc.nIdClient =c.nidClient
	WHERE     s.cSiteCode = 'Root'
	 and c.cLastName not in('POOL','USER','ADMINISTRATOR') 
)
 and n.cLastName not in('POOL','USER','ADMINISTRATOR')  
)t1
)t2

Left Outer Join 

cadoc_crm..tClient c 
	on t2.nidClient = c.nid
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID

where 
CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or (@NativeLinked = 'All' and t2.[Native/Linked] = t2.[Native/Linked]))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or (@ActiveClient = 2 and c.lActive = c.lActive))
And 
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or (@ActiveContact = 'All' and t2.ContactStatus = t2.ContactStatus))
AND
t2.[State] IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
AND
case when replace(t2.Title, ',', '') = '' then 'None' else replace(t2.Title, ',', '') end IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) 
AND
case when replace(c.cIndustry, ',', '') = '' then 'None' else  replace(c.cIndustry, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@Industry,',') ) 
AND
case when replace(c.cUDF5,',','') = '' or  replace(RTRIM(LTRIM(c.cUDF5)),',','') = 'No Selection' then 'None'  else replace(c.cUdf5, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@EntityType,',') ) 
AND
case when replace(cf.ic_cClientRating, ',', '') = '' then 'None' else replace(cf.ic_cClientRating, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) 
AND
case when replace(c.cUdf7, ',', '') = '' then 'None' else replace(c.cUdf7, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@PartnerDirector,',') )
AND
case when replace(cf.ic_cReferralSource, ',', '') = '' then 'None' else replace(cf.ic_cReferralSource, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') )
AND
case when replace(cf.ic_cReferralSourceName, ',', '') = '' then 'None' else replace(cf.ic_cReferralSourceName, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralsourceName,',') ) 
AND
--beginning of or conditions
(
 cf.ic_lSTaxCCorp = 1
OR
 cf.ic_lSTaxSCorp = 1
OR
 cf.ic_lSTaxFiduciary = 1
OR
 cf.ic_lSTaxForm = 1
OR
 cf.ic_lSTaxEstate = 1
OR
 cf.ic_lSTaxNonProfit = 1
OR
 cf.ic_lSTaxMassBusTrusts = 1
OR
 cf.ic_lSTaxPensions = 1
OR
 cf.ic_lSTaxSalesPersPropTax = 1
OR
 cf.ic_lSTaxPayroll1099andW2 = 1
OR
 cf.ic_lSTaxInternational = 1
OR
 cf.ic_lSTaxIndividual = 1
OR
 cf.ic_lSTaxScheduleC = 1
OR
 cf.ic_lSTaxScheduleE = 1
OR
 cf.ic_lSTaxEstatePlanIndiv = 1
OR
 cf.ic_lSTaxGiftTaxRelated = 1
OR
 cf.ic_lSTaxInternationalIndiv = 1
OR
 cf.ic_lSBKPWriteup = 1
OR
 cf.ic_lSBKPWriteupMonthly = 1
OR
 cf.ic_lSBKPWriteupQuarterly = 1
OR
 cf.ic_lSBKPGrayPay = 1
OR
 cf.ic_lSBKPOutsourcedAccounting = 1
OR
 cf.ic_lSAandAAOP = 1
OR
 cf.ic_lSAandAAudit = 1
OR
 cf.ic_lSAandAEBPA = 1
OR
 cf.ic_lSAandACompilationMonthly = 1
OR
 cf.ic_lSAandACompilation = 1
OR
 cf.ic_lSAandAOverheadAudit = 1
OR
 cf.ic_lSAandAReview = 1
OR
 cf.ic_lSAandAInternalAuditing = 1
OR
 cf.ic_lSMASAccountingConsultation = 1
OR
 cf.ic_lSMASFuelExchange = 1
OR
 cf.ic_lSMASFuelTrack = 1
OR
 cf.ic_lSMASMgmtConsultation = 1
OR
 cf.ic_lSMASLitigationSupport = 1
OR
 cf.ic_lSMASPersonalFinancialPlanning = 1
OR
 cf.ic_lSMASMandAConsulting = 1
OR
 cf.ic_lSMASMgmtConsultation = 1
OR
 cf.ic_cSMcDonaldsServices = 1
OR
 cf.ic_cSOtherServices = 1
)
--end of or conditions


order by c.cName asc


drop table ##tempTable

END
Else
Begin


select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
LTRIM(RTRIM(c.cName)) as [Client Name], 
t2.[Native/Linked],
t2.Designate,
t2.[Role],
t2.ContactStatus,
t2.[First Name],
t2.[Last Name],
t2.Salutation,
t2.Title,
t2.Email,
t2.[Email 2],
t2.[Email 3],
c.cPhone1 as [Entity Main Phone],
t2.[Work Phone],
t2.[Home Phone],
t2.[Cell Phone],
t2.[Address 1],
t2.[Address 2],
t2.[Address 3],
t2.City,
t2.[State],
t2.Zip,
t2.Country,
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]



     from

(
select 
n1.nidClient, n1.Designate,n1.[Role],n1.[First Name],n1.[Last Name],n1.Salutation,n1.Title,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone], 
COALESCE(ContactAddressNid,ClientAddressNid) as [nid],
COALESCE(ContactAddress1,ClientAddress1) as [Address 1],
COALESCE(ContactAddress2,ClientAddress2) as [Address 2],
COALESCE(ContactAddress3,ClientAddress3) as [Address 3],
COALESCE(ContactAddressCity,ClientAddressCity) as [City],
COALESCE(ContactAddressState,ClientAddressState) as [State],
COALESCE(ContactAddressZip,ClientAddressZip) as [Zip],
COALESCE(ContactAddressCountry,ClientAddressCountry) as [Country],
n1.[Native/Linked],n1.ContactStatus



from

--native
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title], 
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c 
inner  join cadoc_system..tsitexcrmclient xc 
	on  c.nidclient = xc.nidclient
inner join cadoc_system..tsite s 
	on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
	
	
WHERE 
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root' ) n1

 Union 
--linked
select 
t1.nidClient, t1.Designate,t1.[Role],t1.[First Name],t1.[Last Name],t1.Salutation,t1.Title,
t1.Email,t1.[Email 2],t1.[Email 3],t1.[Work Phone],t1.[Home Phone],t1.[Cell Phone], 
COALESCE(ClientAddressNid,ContactAddressNid) as [nid],
COALESCE(ClientAddress1,ContactAddress1) as [Address 1],
COALESCE(ClientAddress2,ContactAddress2) as [Address 2],
COALESCE(ClientAddress3,ContactAddress3) as [Address 3],
COALESCE(ClientAddressCity,ContactAddressCity) as [City],
COALESCE(ClientAddressState,ContactAddressState) as [State],
COALESCE(ClientAddressZip,ContactAddressZip) as [Zip],
COALESCE(ClientAddressCountry,ContactAddressCountry) as [Country],
t1.[Native/Linked],t1.ContactStatus



from

--linked contacts
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
xc.nIDClient,xc.cDesLinked as Designate, xc.cLabel as [Role],
n.cFirstName as [First Name], n.cLastName as [Last Name], n.cSalutation as [Salutation],n.cTitle as [Title], n.cEmail as [Email],n.cEmailAlt as [Email 2],n.cEmailAlt2 as [Email 3],
n.cPhone as [Work Phone], n.cHomePhone as [Home Phone], n.cCellPhone as [Cell Phone],
(LTRIM(n.cLastName)) + ', ' + RTRIM(LTRIM(n.cFirstName)) AS PrimaryContact,'Linked' as 'Native/Linked',
(CASE WHEN n.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
from cadoc_crm..tclient c 
inner join cadoc_crm..tclientXContact xc
	on c.nid = xc.nIDClient
inner join cadoc_crm..tContact n
	on xc.nIDContact = n.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = n.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on xc.nIDClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
Where n.nid not in (
	Select    c.nid
	FROM         
		cadoc_system..tSite s 
	INNER JOIN cadoc_system..tSiteXCrmClient  xc
		ON s.nid = xc.nidSite 
	INNER JOIN cadoc_crm.dbo.tContact c 
		ON xc.nIdClient =c.nidClient
	WHERE     s.cSiteCode = 'Root'
	 and c.cLastName not in('POOL','USER','ADMINISTRATOR') 
)
 and n.cLastName not in('POOL','USER','ADMINISTRATOR')  
)t1
)t2

Left Outer Join 

cadoc_crm..tClient c 
	on t2.nidClient = c.nid
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID

where 
CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or (@NativeLinked = 'All' and t2.[Native/Linked] = t2.[Native/Linked]))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or (@ActiveClient = 2 and c.lActive = c.lActive))
And 
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or (@ActiveContact = 'All' and t2.ContactStatus = t2.ContactStatus))
AND
t2.[State] IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
AND
case when replace(t2.Title, ',', '') = '' then 'None' else replace(t2.Title, ',', '') end IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) 
AND
case when replace(c.cIndustry, ',', '') = '' then 'None' else  replace(c.cIndustry, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@Industry,',') ) 
AND
case when replace(c.cUDF5,',','') = '' or  replace(RTRIM(LTRIM(c.cUDF5)),',','') = 'No Selection' then 'None'  else replace(c.cUdf5, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@EntityType,',') ) 
AND
case when replace(cf.ic_cClientRating, ',', '') = '' then 'None' else replace(cf.ic_cClientRating, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) 
AND
case when replace(c.cUdf7, ',', '') = '' then 'None' else replace(c.cUdf7, ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@PartnerDirector,',') )
AND
case when replace(cf.ic_cReferralSource, ',', '') = '' then 'None' else replace(cf.ic_cReferralSource, ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') )
AND
case when replace(cf.ic_cReferralSourceName, ',', '') = '' then 'None' else replace(cf.ic_cReferralSourceName, ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralsourceName,',') ) 
AND

--beginning of or conditions
(
'ic_lSTaxCCorp' IN ( select * from ##tempTable) and cf.ic_lSTaxCCorp = 1
OR
'ic_lSTaxSCorp' IN ( select * from ##tempTable) and cf.ic_lSTaxSCorp = 1
OR
'ic_lSTaxFiduciary' IN ( select * from ##tempTable) and cf.ic_lSTaxFiduciary = 1
OR
'ic_lSTaxForm' IN ( select * from ##tempTable) and cf.ic_lSTaxForm = 1
OR
'ic_lSTaxEstate' IN ( select * from ##tempTable) and cf.ic_lSTaxEstate = 1
OR
'ic_lSTaxNonProfit' IN ( select * from ##tempTable) and cf.ic_lSTaxNonProfit = 1
OR
'ic_lSTaxMassBusTrusts' IN ( select * from ##tempTable) and cf.ic_lSTaxMassBusTrusts = 1
OR
'ic_lSTaxPensions' IN ( select * from ##tempTable) and cf.ic_lSTaxPensions = 1
OR
'ic_lSTaxSalesPersPropTax' IN ( select * from ##tempTable) and cf.ic_lSTaxSalesPersPropTax = 1
OR
'ic_lSTaxPayroll1099andW2' IN ( select * from ##tempTable) and cf.ic_lSTaxPayroll1099andW2 = 1
OR
'ic_lSTaxInternational' IN ( select * from ##tempTable) and cf.ic_lSTaxInternational = 1
OR
'ic_lSTaxIndividual' IN ( select * from ##tempTable) and cf.ic_lSTaxIndividual = 1
OR
'ic_lSTaxScheduleC' IN ( select * from ##tempTable) and cf.ic_lSTaxScheduleC = 1
OR
'ic_lSTaxScheduleE' IN ( select * from ##tempTable) and cf.ic_lSTaxScheduleE = 1
OR
'ic_lSTaxEstatePlanIndiv' IN ( select * from ##tempTable) and cf.ic_lSTaxEstatePlanIndiv = 1
OR
'ic_lSTaxGiftTaxRelated' IN ( select * from ##tempTable) and cf.ic_lSTaxGiftTaxRelated = 1
OR
'ic_lSTaxInternationalIndiv' IN ( select * from ##tempTable) and cf.ic_lSTaxInternationalIndiv = 1
OR
'ic_lSBKPWriteup' IN ( select * from ##tempTable) and cf.ic_lSBKPWriteup = 1
OR
'ic_lSBKPWriteupMonthly' IN ( select * from ##tempTable) and cf.ic_lSBKPWriteupMonthly = 1
OR
'ic_lSBKPWriteupQuarterly' IN ( select * from ##tempTable) and cf.ic_lSBKPWriteupQuarterly = 1
OR
'ic_lSBKPGrayPay' IN ( select * from ##tempTable) and cf.ic_lSBKPGrayPay = 1
OR
'ic_lSBKPOutsourcedAccounting' IN ( select * from ##tempTable) and cf.ic_lSBKPOutsourcedAccounting = 1
OR
'ic_lSAandAAOP' IN ( select * from ##tempTable) and cf.ic_lSAandAAOP = 1
OR
'ic_lSAandAAudit' IN ( select * from ##tempTable) and cf.ic_lSAandAAudit = 1
OR
'ic_lSAandAEBPA' IN ( select * from ##tempTable) and cf.ic_lSAandAEBPA = 1
OR
'ic_lSAandACompilationMonthly' IN ( select * from ##tempTable) and cf.ic_lSAandACompilationMonthly = 1
OR
'ic_lSAandACompilation' IN ( select * from ##tempTable) and cf.ic_lSAandACompilation = 1
OR
'ic_lSAandAOverheadAudit' IN ( select * from ##tempTable) and cf.ic_lSAandAOverheadAudit = 1
OR
'ic_lSAandAReview' IN ( select * from ##tempTable) and cf.ic_lSAandAReview = 1
OR
'ic_lSAandAInternalAuditing' IN ( select * from ##tempTable) and cf.ic_lSAandAInternalAuditing = 1
OR
'ic_lSMASAccountingConsultation' IN ( select * from ##tempTable) and cf.ic_lSMASAccountingConsultation = 1
OR
'ic_lSMASFuelExchange' IN ( select * from ##tempTable) and cf.ic_lSMASFuelExchange = 1
OR
'ic_lSMASFuelTrack' IN ( select * from ##tempTable) and cf.ic_lSMASFuelTrack = 1
OR
'ic_lSMASMgmtConsultation' IN ( select * from ##tempTable) and cf.ic_lSMASMgmtConsultation = 1
OR
'ic_lSMASLitigationSupport' IN ( select * from ##tempTable) and cf.ic_lSMASLitigationSupport = 1
OR
'ic_lSMASPersonalFinancialPlanning' IN ( select * from ##tempTable) and cf.ic_lSMASPersonalFinancialPlanning = 1
OR
'ic_lSMASMandAConsulting' IN ( select * from ##tempTable) and cf.ic_lSMASMandAConsulting = 1
OR
'ic_lSMASMgmtConsultation' IN ( select * from ##tempTable) and cf.ic_lSMASMgmtConsultation = 1
OR
'ic_cSMcDonaldsServices' IN ( select * from ##tempTable) and cf.ic_cSMcDonaldsServices = 1
OR
'ic_cSOtherServices' IN ( select * from ##tempTable) and cf.ic_cSOtherServices = 1
)
--end of or conditions


order by c.cName asc


drop table ##tempTable

end

end

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.