Trouble with a Where Clause

Ashley,

The simplest solution given that would be to simply change your "OR" to and "AND" and to replicate what is done with the list parameter for the customer parameter.  So the line would become something like this:

and (Coalesce(@customer_no,0) = 0 OR
    a.customer_no = @customer_no)

You do not need a select statement there since the customer number is a constant for that person; otherwise it would be something like:

exists (Select * from dbo.T_CUSTOMER where customer_no = a.customer_no AND customer_no = @customer_no)

But that is effectively the same thing.  Unless, I suppose you are worried about someone entering a non-valid customer_no.  In which case, it would return no results anyway.

Let me know if that does not work.

John

Thank you for this suggestion! I ended up going with this (assuming my coworker approves):

Where a.pmt_dt <= Coalesce(@end_dt, ‘12/31/2999’)

and (@customer_no is null or ISNULL(Datalength(ltrim(@customer_no)),0) = 0 or

              charindex(',' + convert(varchar, a.customer_no) + ',' , ',' + @customer_no + ',') > 0)

and Case When Coalesce(@list_no, 0) = 0 Then 1

  When a.customer_no in (Select customer_no From [dbo].t_list_contents Where list_no = @list_no) Then 1

       Else 0 End = 1

My only problem with my current solution is that the combination of On Account Type + List + Customer_no doesn't work. However, in theory the user could just add the extra customer_no to the list. We will see how my coworker feels about that.

Ashley

Glad you were able to come up with a solution!  (Hopefully it is approved)

Without looking at the report in full, it is hard to say, but it certainly seems odd that someone would want to run against both a list and a specific customer_no at the same time.  It seems like the customer_no sort of cancels out the list parameter.  But hey, sometimes people ask for things that are a little odd, and we just go with it.

Right!

To keep things clean you may want to consider handling the parameters prior to the actual query. This what I generally do:

    Declare
    @rStartOpenDate             date,
    @rEndOpenDate            datetime,
    @rStartPostDate             date ,
    @rEndPostDate            datetime,
    @rStartCloseDate             date ,
    @rEndCloseDate            datetime

    Set @rStartCloseDate = isnull(@StartCloseDate, getdate())
    Set @rStartPostDate = isnull(@StartPostDate,getdate())
    Set @rStartOpenDate = isnull(@StartOpenDate,dateadd(Day,-30,getdate()))

    Set @rEndCloseDate = dbo.LFN_MBA_EOD (@EndCloseDate )
    Set @rEndPostDate = dbo.LFN_MBA_EOD (@EndPostDate )
    Set @rEndOpenDate = dbo.LFN_MBA_EOD (@EndOpenDate )

    Set @BatchNo = isnull(@BatchNo,0)
    Set @PostNo = isnull(@PostNo,0)

Then in the query I can do this allowing for the different combinations of results:

    where
    (@StartCloseDate is null and @EndCloseDate is null
    and @StartPostDate is null and @EndPostDate is null
    and @StartOpenDate is null and @EndOpenDate is null
    and b.create_dt between @rStartOpenDate and @rEndOpenDate  and @BatchNo = 0 and @PostNo = 0)
    or (@BatchNo = 0 and @PostNo = 0 and @StartCloseDate is not null and @EndCloseDate is not null
        and b.close_dt between @rStartCloseDate and @rEndCloseDate )
    or (@BatchNo = 0 and @PostNo = 0 and @StartPostDate is not null and @EndPostDate is not null
        and b.posted_dt between @rStartPostDate and @rEndPostDate )
    or (@BatchNo = 0 and @PostNo = 0 and @StartOpenDate is not null and @EndOpenDate is not null
        and b.create_dt between @rStartOpenDate and @rEndOpenDate )
    or (@BatchNo = 0 and b.post_no = @PostNo )
    or (@PostNo = 0 and b.batch_no = @BatchNo )