Search code examples
sqlt-sqlreporting-servicesparameterswhere-clause

Start&End Date filters and Searchbox Filter Logic Tsql


I am working on a ssrs report and the requirement for the report is ; Start & End date filters and Order number filter which will be searchbox. The report will show today's data but if they want to see previous days I have to have these 3 filters on my report.

My challenge is how to add the logic in where clause. I tried below logic but didn't work bc for some cases @ordernumber or date filters might be null.


and sh.CustomerOrderNbr  = (@OrderNumber)
and (Convert(date, cr.CreateDt) between @StartDate and @EndDate)

Expecting to add a logic that can return date range and ordernumber filters Or either @ordernumber or @StartDate&EndDate


Solution

  • and Convert(date, cr.CreateDt) between ISNULL(@StartDate,'2001-01-01') and isnull(@EndDate,'2099-01-01')
    and ( 
            (ISNULL(@OrderNumber,'') <> '' and sh.CustomerOrderNbr  = @OrderNumber)
            OR
            (ISNULL(@ordernumber,'') = '' And sh.CustomerOrderNbr <>'')
        )
    

    Came up with the solution.