That worked thanks. But I would like to here more about how to do it faster. Maybe with the derived table that was mentioned.

So how would I fix it?

ConsultantID and the SponsorID are in the same record.

I am at a bit of a loss here. I think I understand what you are saying but not sure how to go about writing it.

Thank you very much that worked out great!!!:w00t:

The @perioddate is a parameter being passed in. This version of the code is for testing but if I change the @perioddate to a date say from last month the...

I think you miss read the code

IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NULL)

SELECT @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@GreaterThan) + ' '

ELSE IF (@LessThan...

so all my filters would be come a union?

based on the inpout from everybody I think I need tro find another way to do this. What I need is to have two record set returned. One will have...

I am being duuhhhhhh! The whole issue resolved itself when I lengthen the @status variable from 10 to 20. The blessed variable wasn't long enough. Thanks for everybodys...

I think I see what is happening, if the StatusID does not having a matching record in the table it is returning all Actives instead of an expected NULL recordset.

I changed the filter to look like this:

IF (@Status = 'Grace')

BEGIN

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

Set @SQLStr = @SQLstr + 'AND StatusID' +' =...

Upon further investigation, I don't think the IF statements are working or the string concatenation is not doing it job:

Code:

IF (@Status = 'Grace')

Select @SQLStr

--SET @SQLStr = ' AND StatusID= '...

This is the entire code:

USE [TSDataWarehouse]

GO

/****** Object: StoredProcedure [consultantreports].[uspS_DownlineCoaching] Script Date: 01/17/2008 09:09:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*-------------------------------------------------------------------------------------------------

STORED PROC: [consultantreports].[uspS_DownlineCoaching]

DESCRIPTION:returns the downline coaching...

@And is being assigned above the filter block;

IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NULL)

SELECT @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@GreaterThan) + ' '

ELSE IF...

This is being done within a Data Flow task, so I am not sure what you are asking.