Help with Dynamic SQL

Question

Help with Dynamic SQL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 1

The @Level paramters are passed into the proc by the user. they can display which levels they want the proc to return. Each @level is a bit field.

I tried the statement that you sent but it still returned all levels even when I had @Level30 = 0.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

Actually that last statement worked fine but in order to get it functional I need to add it into my WHERE Clause then I can just get rid of that section of Dynamic SQL. I tried it this way but it still returns all the levels. What am I doing wrong? THIS is the FROM clause

from #Downline D

--LEFT OUTER JOIN #Orders O ON D.ConsultantID = O.ConsultantID

inner JOIN volume.volume V ON D.ConsultantID = V.ConsultantID --AND v.Enddate BETWEEN @MonthStartDt AND @MonthEndDt

LEFT OUTER JOIN uvw_DownlineOrder U ON D.ConsultantID = u.ConsultantID collate Latin1_General_CI_AS

LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID collate Latin1_General_CI_AS AND r.repflag = 'X'

WHERE (D.Active = 1)AND ((d.CurrentLevelXID = 10 AND @Level10 = 1)

OR (d.CurrentLevelXID = 20 AND @Level20 = 1)

OR (d.CurrentLevelXID = 30 AND @Level30 = 1)

OR (d.CurrentLevelXID = 40 AND @Level40 = 1)

OR (d.CurrentLevelXID = 50 AND @Level50 = 1)

OR (d.CurrentLevelXID = 60 AND @Level60 = 1)

OR (d.CurrentLevelXID = 70 AND @Level70 = 1)

OR (d.CurrentLevelXID = 80 AND @Level80 = 1)) AND (BillToState = @State) OR (@State IS NULL) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)

GROUP BY D.ConsultantID ,D.Downlinelevel

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 3

alorenzini (8/1/2008)
The @Level paramters are passed into the proc by the user. they can display which levels they want the proc to return. Each @level is a bit field.
I tried the statement that you sent but it still returned all levels even when I had @Level30 = 0.

Does the consultant ID have a value? What about the other OR statement? I think you have a logic problem in the query somewhere, but that would be my first guess.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

Yes to all that. Here is the script in it's entirety:

DECLARE@ConsultantIDnVarChar(50)

DECLARE@AchieveLevelStartint

DECLARE@AchieveLevelEndint

DEclare @Level10bit

Declare @Level20bit

Declare @Level30bit

Declare @Level40bit

Declare @Level50bit

Declare @Level60bit

Declare @Level70bit

Declare @Level80bit

DECLARE @LineFilterVarchar(100)

DECLARE@statusvarchar(100)

DECLARE@MTDGreaterThanDecimal

DECLARE@MTDLessThanDecimal

DECLARE @QTDGreaterThanDecimal

DECLARE @QTDLessThanDecimal

DECLARE @StateVarchar(20)

DECLARE @AndNVARCHAR(1000)

DECLARE @QTDAndNVARCHAR(1000)

,@SQLStrNVARCHAR(4000)

,@SQLLevelNVARCHAR(4000)

,@PeriodDateDateTime

,@ORStrNvarchar(50)

,@StateSQLnvarchar(50)

SET @ConsultantID = @ConsultantID

SET @PeriodDate = GEtDate()

Set @ConsultantID = '0000344'

SET @AchieveLevelStart = 10

SET @AchieveLevelEnd = 80

Set @Level10 = 1

Set @Level20 = 1

Set @Level30 = 0

Set @Level40 = 0

Set @Level50 = 0

Set @Level60 = 1

Set @Level70 = 1

Set @Level80 = 1

SET @status = 'All active'

SET @MTDGreaterThan =0

SET @MTDLessThan = 2000

SET @QTDGreaterThan = -1

SET @QTDLessThan = -1

SET @OrStr = ' OR (ConsultantID IS NULL)'

SET @LineFilter ='Lines 1, 2 and 3'

SET @State = 'All States'

Achieve Level Filter

SET @AchieveLevelStart = @AchieveLevelStart

SET @AchieveLevelEnd = @AchieveLevelEnd

Status Filter

SET @status = @status

Downline level filter

SET @LineFilter = @LineFilter

Sales level Filter

SET @MTDGreaterThan = @MTDGreaterThan

SET @QTDLessThan = @QTDLessThan

String that holds the AND statment for the

Sales level filter.

SET @AND = NULL

String that hold the SQL Execution statement

SET @SQLStr = NULL

Declare Local Variables

Declare @MonthStartDt As DateTime

DECLARE @MonthEndDt AS DateTime

DECLARE @QuarterStartDtASDateTime

DECLARE @QuarterEndDt AS DateTime

Declare @YearASDateTime

Declare @PeriodStartDt As DateTime

Declare @PeriodEndDt as DateTime

Breakdown @PeriodDate into Quarter and Months

SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)

Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)

SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)

SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0)

Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1);

IF @MonthEndDt > GETDATE()

BEGIN

SET @MonthEndDt = GETDATE()

END;

IF @State = 'All States'

BEGIN

SET @State = Null

END;

Declare the Downline Recursive Query

With downline (ConsultantID,EffectiveDate, ConsultantName,DownLineLevel,

ConsultantXId,BumpupDate, DeactivationDate,CurrentLevelAchieveDate,

CurrentLevelXID, Active, BillToAddressLine1,BillToAddressLine2,BillToCity,BillToState,BillToZip

,BilltoRegionXID,HomePhone,BusinessPhone,Fax,OtherPhone,

EmailAddress, SponsorXID, StatusXID,NACDate )

AS

(

Anchor member defintion

SELECTA.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName as ConsultantName

,0 as DownLineLevel

,A.XID

,A.Bumpup as BumpupDate

,A.InactiveDate As DeactivationDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.Active

,A.BillToAddressLine1

,A.BillToAddressLine2

,A.BillToCity

,A.BillToStateCode AS BillToState

,A.BillToPostalCode AS BillToZip

,A.BilltoRegionXID

,A.HomePhone

,A.WorkPhone AS BusinessPhone

,A.Fax

,A.MobilePhone as OtherPhone

,A.EmailAddress

,A.SponsorXID

,A.StatusXID

,A.NACDate

FROM [consultant].[uvw_ConsultantDownline] a wITH ( NOLOCK )

WHERE A.ConsultantID = @ConsultantID

AND @MonthEndDt Between a.EffectiveDate

and A.EffectiveEndDate

UNION ALL

Recursive member definition

SELECTA.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName as ConsultantName

,DownLineLevel + 1

,A.XID AS ConsultantXID

,A.Bumpup AS BumpupDate

,A.InactiveDate as DeactivationDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.Active

,A.BillToAddressLine1

,A.BillToAddressLine2

,A.BillToCity

,A.BillToStateCode as BillToState

,A.BillToPostalCode AS BillToZip

,A.BilltoRegionXID

,A.HomePhone

,A.WorkPhone AS BusinessPhone

,A.Fax

,A.MobilePhone AS OtherPhone

,A.EmailAddress

,A.SponsorXID

,A.StatusXID

,A.NACDate

FROM [consultant].[uvw_ConsultantDownline] AS A with ( nolock )

INNER JOIN DownLine AS B ON A.SponsorXID = B.ConsultantXID

WHERE @MonthEndDt Between a.EffectiveDate

and A.EffectiveEndDate

AND A.Active = 1

AND DownLineLevel + 1 <= 4

) -- Appropriate records

Select * From downline

Create the Temp table #Downline that returns the CTE results

SELECT D.DownLineLevel

,D.ConsultantID

,D.EffectiveDate

,D.ConsultantName

,s.Title AS AchieveTitle

,D.ConsultantXID

,D.BumpupDate

,D.DeactivationDate

,D.CurrentLevelAchieveDate

,D.CurrentLevelXID

,D.Active

,D.BillToAddressLine1

,D.BillToAddressLine2

,D.BillToCity

,D.BillToState

,D.BillToZip

,D.BilltoRegionXID

,D.HomePhone

,D.BusinessPhone

,D.Fax

,D.OtherPhone

,D.EmailAddress

,D.SponsorXID

,D.StatusXID AS StatusID

,'StatusIDNAme' = CASE WHEN s1.StatusID IN (2,1,10) THEN ''

ELSE s1.Statusname

END

,D.NACDate

Into #Downline

from downline d

inner jOIN [consultant].[uvw_ConsultantDownline] c On d.SponsorXID = c.XiD

LEFT OUTER JOIN shareddimension..DimConsultantTitle s ON d.CurrentLEvelXID = s.XID

LEFT OUTER JOIN shareddimension..DimConsultantStatus s1 ON d.StatusXID = s1.StatusID

Select * From #Downline

DECLARE @CONSULTANTIDS NVARCHAR(MAX)

SET @CONSULTANTIDS =

(SELECT DISTINCT RTRIM(ConsultantID) + '|'

FROM #DownLine

FOR XML PATH(''))

CREATE TABLE #Temp (

[ConsultantID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[EffectiveDate] [datetime] NULL,

CurrentLevelXID INT NULL,

NACDate Datetime NULL,

[CNSDate] [datetime] NULL,

[SRCFirstTimePromoDate] [datetime] NULL,

[SRCDate] [datetime] NULL,

[TLFirstTimePromoDate] [datetime] NULL,

[TLDate] [datetime] NULL,

[TMFirstTimePromoDate] [datetime] NULL,

[TMDate] [datetime] NULL,

[STMFirstTimePromoDate] [datetime] NULL,

[STMDate] [datetime] NULL,

[TMTFirstTimePromoDate] [datetime] NULL,

[TMTDate] [datetime] NULL,

[STMTFirstTimePromoDate] [datetime] NULL,

[STMTDate] [datetime] NULL,

[RepFlag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

SELECT @CONSULTANTIDS

INSERT INTO #Temp

EXEC [consultantreports].uspS_DownlineRepromotions3 @CONSULTANTIDS

Select * from #Temp

SELECT A.*

INTO #Temp2

FROM #temp AS A

INNER JOIN #Downline AS B ON A.[ConsultantID] = b.consultantid --collate Latin1_General_CI_AS

Select * from #Temp2

CREATE TABLE #DlFiltered(

[OrigConsID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrigConsName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ConsultantID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Downlinelevel] [int] NULL,

[ConsultantName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[EffectiveDate] [datetime] NULL,

[BumpupDate] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ConsultantXID] [int] NULL,

[CurrentLevelXID] [int] NULL,

[AchieveTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AchieveDate] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[QuarterToDate_total] [decimal](38, 2) NULL,

[MonthToDate_Total] [decimal](38, 2) NULL,

Mthly1stLinePORSales [decimal](38, 2) NULL,

Qtrly1stLinePORSales [decimal](38, 2) NULL,

Mthly2ndLinePORSales [decimal](38, 2) NULL,

Qtrly2ndLinePORSales [decimal](38, 2) NULL,

Mthly3rdLinePORSales [decimal](38, 2) NULL,

Qtrly3rdLinePORSales [decimal](38, 2) NULL,

[LastOrderDate] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToAddressLine1] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToAddressLine2] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToCity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToState] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToZip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BillToRegionXID] INT,

[HomePhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BusinessPhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OtherPhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[EmailAddress] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RepFlag] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SponsorXID] [int] NULL,

[Active] [bit] NULL,

[StatusID] int NULL,

StatusIDName Varchar(30) NULL,

[AgreementDate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

)

IF (@Status = 'All active')

BEGIN

SELECT *

FROM #DLFiltered

WHERE Active = 1

AND ((CurrentLevelXID = 10 AND @Level10 = 1)

OR (CurrentLevelXID = 20 AND @Level20 = 1)

OR (CurrentLevelXID = 30 AND @Level30 = 1)

OR (CurrentLevelXID = 40 AND @Level40 = 1)

OR (CurrentLevelXID = 50 AND @Level50 = 1)

OR (CurrentLevelXID = 60 AND @Level60 = 1)

OR (CurrentLevelXID = 70 AND @Level70 = 1)

OR (CurrentLevelXID = 80 AND @Level80 = 1))

AND MonthToDate_Total BETWEEN 0 AND 2000

OR ConsultantID IS NULL

END;

INSERT INTO #DlFiltered ( [OrigConsID]

,[OrigConsName]

,[ConsultantID]

,[Downlinelevel]

,[ConsultantName]

,[EffectiveDate]

,[BumpupDate]

,[ConsultantXID]

,[CurrentLevelXID]

,[AchieveTitle]

,[AchieveDate]

,[QuarterToDate_total]

,[MonthToDate_Total]

,Mthly1stLinePORSales

,Qtrly1stLinePORSales

,Mthly2ndLinePORSales

,Qtrly2ndLinePORSales

,Mthly3rdLinePORSales

,Qtrly3rdLinePORSales

,[LastOrderDate]

,[BillToAddressLine1]

,[BillToAddressLine2]

,[BillToCity]

,[BillToState]

,[BillToZip]

,BilltoRegionXID

,[HomePhone]

,[BusinessPhone]

,[Fax]

,[OtherPhone]

,[EmailAddress]

,[RepFlag]

,SponsorXID

,[Active]

,[StatusID]

,StatusIDNAme

,[AgreementDate]

)

Select

'OrigConsID' = @ConsultantID

,(Select Top 1 FirstName + ' ' + LastName FROM Consultant.uvw_ConsultantDownline d

WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'

,D.ConsultantID

,D.Downlinelevel

,D.ConsultantName

,D.EffectiveDate

,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate

,D.ConsultantXID

,D.CurrentLevelXID

,D.AchieveTitle

,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate

,ISNULL(v.QtrlyPORSalesLessCredits, 0) AS QuarterToDate_total

,ISNULL(v.MthlyPORSalesLessCredits, 0) AS MonthToDate_Total

,ISNULL(v.Mthly1stLinePORSales, 0) AS Mthly1stLinePORSales

,ISNULL(v.Qtrly1stLinePORSales,0) AS Qtrly1stLinePORSales

,ISNULL(v.Mthly2ndLinePORSales,0) AS Mthly2ndLinePORSales

,ISNULL(v.Qtrly2ndLinePORSales,0) AS Qtrly2ndLinePORSales

,ISNULL(v.Mthly3rdLinePORSales,0) AS Mthly3rdLinePORSales

,ISNULL(v.Qtrly3rdLinePORSales,0) AS Qtrly3rdLinePORSales

,CONVERT(Char(10),Max(u.OrderCreateDate),101) AS 'LastOrderDate'

,D.BillToAddressLine1

,D.BillToAddressLine2

,D.BillToCity

,D.BillToState

,D.BillToZip

,D.BilltoRegionXID

,D.HomePhone

,D.BusinessPhone

,D.Fax

,D.OtherPhone

,D.EmailAddress

,ISNULL(r.RepFlag,' ')AS RepFlag

,D.SponsorXID

,D.Active

,D.StatusID

,D.StatusIDName

,Convert(varchar(10),D.NACDate,101) AS AgreementDate

from #Downline D

LEFT OUTER JOIN #Orders O ON D.ConsultantID = O.ConsultantID

inner JOIN volume.volume V ON D.ConsultantID = V.ConsultantID --AND v.Enddate BETWEEN @MonthStartDt AND @MonthEndDt

LEFT OUTER JOIN uvw_DownlineOrder U ON D.ConsultantID = u.ConsultantID collate Latin1_General_CI_AS

LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID collate Latin1_General_CI_AS AND r.repflag = 'X'

WHERE (D.Active = 1)AND ((d.CurrentLevelXID = 10 AND @Level10 = 1)

OR (d.CurrentLevelXID = 20 AND @Level20 = 1)

OR (d.CurrentLevelXID = 30 AND @Level30 = 1)

OR (d.CurrentLevelXID = 40 AND @Level40 = 1)

OR (d.CurrentLevelXID = 50 AND @Level50 = 1)

OR (d.CurrentLevelXID = 60 AND @Level60 = 1)

OR (d.CurrentLevelXID = 70 AND @Level70 = 1)

OR (d.CurrentLevelXID = 80 AND @Level80 = 1)) AND (BillToState = @State) OR (@State IS NULL) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)

GROUP BY D.ConsultantID ,D.Downlinelevel

,D.ConsultantName

,D.ConsultantXID

,D.EffectiveDate

,D.AchieveTitle

,D.BumpupDate

,D.CurrentLevelAchieveDate

,D.EmailAddress

,D.ConsultantXID

,D.CurrentLevelXID

,D.Active

,D.BillToAddressLine1

,D.BillToAddressLine2

,D.BillToCity

,D.BillToState

,D.BillToZip

,D.BilltoRegionXID

,D.HomePhone

,D.BusinessPhone

,D.Fax

,D.OtherPhone

,r.RepFlag

,D.SponsorXID

,D.StatusID

,StatusIdName

,D.NACDate

,v.QtrlyPORSalesLessCredits

,v.MthlyPORSalesLessCredits

,v.Mthly1stLinePORSales

,v.Qtrly1stLinePORSales

,v.Mthly2ndLinePORSales

,v.Qtrly2ndLinePORSales

,v.Mthly3rdLinePORSales

,v.Qtrly3rdLinePORSales

ORDER BY 4

--

--IF (@Status = 'All active')

-- BEGIN

-- SELECT *

-- FROM #DLFiltered

-- WHERE Active = 1

-- AND ((CurrentLevelXID = 10 AND @Level10 = 1)

-- OR (CurrentLevelXID = 20 AND @Level20 = 1)

-- OR (CurrentLevelXID = 30 AND @Level30 = 1)

-- OR (CurrentLevelXID = 40 AND @Level40 = 1)

-- OR (CurrentLevelXID = 50 AND @Level50 = 1)

-- OR (CurrentLevelXID = 60 AND @Level60 = 1)

-- OR (CurrentLevelXID = 70 AND @Level70 = 1)

-- OR (CurrentLevelXID = 80 AND @Level80 = 1))

-- AND MonthToDate_Total BETWEEN 0 AND 2000

-- OR ConsultantID IS NULL

-- END;

Select * from #DlFiltered

INSERT INTO #DlFiltered (OrigConsID, OrigConsName)

Select consultantid, firstname + ' ' + Lastname as ConsultantName

FROM consultant Where ConsultantID = @COnsultantID

--

--Select * FROM #DlFiltered

--WHERE BillToRegionXID = @State OR (@State IS NULL)

-- Set up And clause for MTD Sales amount filter.

IF @MTDGreaterThan = -1

SET @MTDGreaterThan = NULL

IF @MTDLessThan = -1

SET @MTDLessThan = NULL

IF @QTDGreaterThan = -1

SET @QTDGreaterThan = NULL

IF @QTDLessThan = -1

SET @QTDLessThan = NULL

IF (@MTDGreaterThan IS NOT NULL) AND (@MTDLessThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)

SET @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@MTDGreaterThan) + ' '

ELSE IF (@MTDLessThan IS NOT NULL) AND (@MTDGreaterThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)

SET @And = ' AND MonthToDate_Total <= ' + CONVERT(VARCHAR(20),@MTDLessThan) + ' '

ELSE IF (@MTDGreaterThan IS NOT NULL) AND (@MTDLessThan IS NOT NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)

SET @And = ' AND MonthToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@MTDGreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@MTDLessThan) + ' '

ELSE IF (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)

SET @And = NULL

ELSE IF (@QTDGreaterThan IS NOT NULL) AND (@QTDLessThan IS NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)

SET @And = ' AND QuarterToDate_Total >= ' + CONVERT(VARCHAR(20),@QTDGreaterThan) + ' '

ELSE IF (@QTDLessThan IS NOT NULL) AND (@QTDGreaterThan IS NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)

SET @And = ' AND QuarterToDate_Total <= ' + CONVERT(VARCHAR(20),@QTDLessThan) + ' '

ELSE IF (@QTDGreaterThan IS NOT NULL) AND (@QTDLessThan IS NOT NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)

SET @And = ' AND QuarterToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@QTDGreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@QTDLessThan) + ' '

--Select @And

--

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

-- ALL Level Filters

IF @LineFilter = ('Lines 1, 2, 3 and 4')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'0' ) + ' AND ' + CONVERT(Varchar(2),'4' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

-- Only Line 1 Filter

IF @LineFilter = ('Only Line 1')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'1' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

-- Only Line 2 Filter

IF @LineFilter = ('Only Line 2')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'2' ) + ' AND ' + CONVERT(Varchar(2),'2' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

-- Only Line 3 Filter

IF @LineFilter = ('Only Line 3')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

-- Lines 1 and 2 Filter

IF @LineFilter = ('Lines 1 and 2')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'2' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

-- Lines 1, 2, and 3 Filter

IF @LineFilter = ('Lines 1, 2 and 3')

BEGIN

SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '

SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'3' ) + ' OR (DownlineLevel = ' + CONVERT(Varchar(2),'0' ) + ')'

END

--SELECT @SQLStr

-- All Active Filter

IF (@Status = 'All active')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

SET @SQLStr = @SQLStr + ' '+ @SQlLevel --+ ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

--Select @SQlstr

END

-- TIB Filter

IF (@Status = 'TIB')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' +' = ' + CONVERT(Varchar(2),'4' )--''TIB'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- Special TIB Filter

IF (@Status = 'Special TIB')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' + ' = '+ CONVERT(Varchar(2),'5' )--''Special TIB'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- All TIB Filter

IF (@Status = 'All TIB')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' + ' IN ('+ CONVERT(Varchar(2),'4' )+ ','+ CONVERT(Varchar(2),'5' )+')'--'Special TIB''' + 'OR StatusID' + ' = ''TIB'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- Grace Filter

IF (@Status = 'Grace')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' +' = '+ CONVERT(Varchar(2),'6' )--'Grace'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- New, Non Active Consultant Filter

IF (@Status = 'NAC Consultant')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' +' = '+ CONVERT(Varchar(2),'1' )--'NAC Consultant'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- In Reactivation 30 Day Filter

IF (@Status = 'In Reactivation 30 Day')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' +' = '+ CONVERT(Varchar(2),'8' )--'In Reactivation 30 Day'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- In Reactionation 1 Quarter Filter

IF (@Status = 'In Reactivation 1 Quarter')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' +' = '+ CONVERT(Varchar(2),'9' )--'In Reactivation 1 Quarter'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- All Reactivation Filter

IF (@Status = 'All in Reactivation')

BEGIN

SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

Set @SQLStr = @SQLstr + 'AND StatusID' + ' IN ('+ CONVERT(Varchar(2),'8' )+','+ CONVERT(Varchar(2),'9' )+')'--In Reactivation 1 Quarter''' + ' OR StatusID' + ' = ''In Reactivation 30 Day'''

SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

END

-- Execute Dynamic SQL

--SELECT @SQLStr

EXEC sp_executesql @SQLstr;

DROP TABLE #Downline

DROP TABLE #DLFiltered

Drop Table #Temp

Drop Table #Temp2

Drop table #Orders

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 5

alorenzini (8/1/2008)
Actually that last statement worked fine but in order to get it functional I need to add it into my WHERE Clause then I can just get rid of that section of Dynamic SQL. I tried it this way but it still returns all the levels. What am I doing wrong? THIS is the FROM clause
from #Downline D
--LEFT OUTER JOIN #Orders O ON D.ConsultantID = O.ConsultantID
inner JOIN volume.volume V ON D.ConsultantID = V.ConsultantID --AND v.Enddate BETWEEN @MonthStartDt AND @MonthEndDt
LEFT OUTER JOIN uvw_DownlineOrder U ON D.ConsultantID = u.ConsultantID collate Latin1_General_CI_AS
LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID collate Latin1_General_CI_AS AND r.repflag = 'X'
WHERE (D.Active = 1)AND ((d.CurrentLevelXID = 10 AND @Level10 = 1)
OR (d.CurrentLevelXID = 20 AND @Level20 = 1)
OR (d.CurrentLevelXID = 30 AND @Level30 = 1)
OR (d.CurrentLevelXID = 40 AND @Level40 = 1)
OR (d.CurrentLevelXID = 50 AND @Level50 = 1)
OR (d.CurrentLevelXID = 60 AND @Level60 = 1)
OR (d.CurrentLevelXID = 70 AND @Level70 = 1)
OR (d.CurrentLevelXID = 80 AND @Level80 = 1)) AND (BillToState = @State) OR (@State IS NULL) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
GROUP BY D.ConsultantID ,D.Downlinelevel

Let's take a look at the where clause again.

WHERE (D.Active = 1)

AND ((d.CurrentLevelXID = 10 AND @Level10 = 1)

OR (d.CurrentLevelXID = 20 AND @Level20 = 1)

OR (d.CurrentLevelXID = 30 AND @Level30 = 1)

OR (d.CurrentLevelXID = 40 AND @Level40 = 1)

OR (d.CurrentLevelXID = 50 AND @Level50 = 1)

OR (d.CurrentLevelXID = 60 AND @Level60 = 1)

OR (d.CurrentLevelXID = 70 AND @Level70 = 1)

OR (d.CurrentLevelXID = 80 AND @Level80 = 1))

AND (BillToState = @State)

OR (@State IS NULL)

OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)

In the above statement, if @State IS NULL - all rows will be returned.

If D.DeactivationDate is between @MonthStartDt AND @MonthEndDt - all rows will be returned.

So, what exactly do you want here. Do you want this?

WHERE D.Active = 1

AND ((d.CurrentLevelXID = 10 AND @Level10 = 1)

OR (d.CurrentLevelXID = 20 AND @Level20 = 1)

OR (d.CurrentLevelXID = 30 AND @Level30 = 1)

OR (d.CurrentLevelXID = 40 AND @Level40 = 1)

OR (d.CurrentLevelXID = 50 AND @Level50 = 1)

OR (d.CurrentLevelXID = 60 AND @Level60 = 1)

OR (d.CurrentLevelXID = 70 AND @Level70 = 1)

OR (d.CurrentLevelXID = 80 AND @Level80 = 1))

AND (BillToState = @State OR @State IS NULL)

AND (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)

Or, did you want something else? What are the requirements?

One thing, I only put in parantheses when I need them to make sure the evaluation happens the way I want it. Whenever you start mixing AND and OR - you definitely need to make sure they are placed correctly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs