** We are self hosted**
I made a custom version of a Tessitura standard report (on account tracking) and added a parameter to it (customer_no). Then I added that field to the where clause of my local procedure but I can’t get my combination of “and’s” and “or’s” right.
Here are the parameters:
The only required parameter is On Account Type. I need the report to be able to run with any combination of that and the other three.
The original stored procedure is RP_ON_ACCOUNT_TRACKING_RPT. I copied that, renamed it, added the parameter, and edited the where clause. I attached the SQL to this email.
Here is the where clause at it stands:
Does anyone have any input on how I can get this correct?
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
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!