it seems like it's inserting a blank row for each of the Filtere statements that it passes through. So I end up with for empty rows instead of just one.

This almost works but it's ignoring the last ELSE:

ELSE Select Distinct OrigConsID, ORigConsName

FROM #DlFiltered WHERE ConsultantID IS NULL

I think I almost have this. Current code:

IF @LineFilter = ('Only Line 1')

BEGIN

SELECT DIstinct * from #DLFiltered WHERE Active = 1

AND DownlineLevel = 1

AND EffectiveDate BETWEEN @StartPeriodDate AND...

Thanks a lot that worked like a champ.

I recieve the following message:

Msg 144, Level 15, State 1, Line 13

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP...

Lets see if I can explain the what I am seeing ( I am now getting two records set back, one that is empty (expected) and one with all the...

It doing the insert regardles of a empty record set. I am a bit confused now, in my brain I would think it needs to look something like this:

IF @LineFilter...

Doing it this way how does it know what filter to use?

IF EXISTS (SELECT NULL FROM #DLFiltered

WHERE Active = 1

AND DownlineLevel = 1

...

What I expect to see would be that in the If Statement did not return any rows then a insert would happen into the table and then that row would...

This works great but I am having issue with my Filter statement:

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](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Downlinelevel] [int] NULL,

[ConsultantName] [nvarchar](101) COLLATE...

I created the Temp Table but how would I change my current code into the INSERT Statement:

Current COde:

Select 'OrigConsID' = @ConsultantID

,(Select Top 1 FirstName + ' ' + LastName...

This is the code that seems to make it work appropriately:

Select DISTINCT t.ConsultantID,

t.ConsultantName,

t.CurrentLevelXID,

MAX(t.EffectiveDate) AS EffectiveDate,

s.Title,

t.NACDate,

t.BillToState,

t.HomePhone,

t.SponsorXID,

t.SponsorID,

c.FirstName + ' ' + c.LastName AS SponsorName,

t.DeactivationDate,

t.Active

,t.ReactivateDt

from #Temp3 t

Left Outer Join Consultant c ON...

Not a problem. I will start doing that in the future.

Geez. Well it is Monday!!! Thanks a lot that worked.

I figured out that that one of the columns was typed wrong. After I corrct that everything started to work. Thanks.