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.
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 = 1ASKER
I would have the values in the temptable needed to make the where clause
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ASKER
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.
In the case of just some...I would hope the where clause could be smaller.
ASKER
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.
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 = 1ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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
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
ASKER
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.