• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 97 views
  • Users 0 members are here
Related

Trouble with a Where Clause

Ashley Elliott (Past Member)
$organization

** 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

Parents
  • $organization

    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 )

Reply
  • $organization

    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 )

Children
No Data