none
Between Date parameter is not working.

    Question

  • Hi All,

    This is my Dataset query. Other filters are working fine. I am getting out put when i select date range. But Its not working when i select Null Option (That time i should get all the data).  but Its not working.

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) and ([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null) 

    Can anyone correct this query or suggest any alternatives

    Thanks in Advance

    Thursday, April 03, 2014 3:57 AM

Answers

  • I am reposting the same select statement that I posted earlier. If you have not tried then you may give a try.

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) and (([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null))

    This is not what I suggested

    please use it as I suggested

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) 
    and [Accreditation Name] in (@AccreditationName) 
    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null)
    and ([Accreditation Start Date] <= @Eligibility_Enddate @Eligibility_Enddate is null)

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, April 03, 2014 6:50 AM

All replies

  • Replace Between with >= and <=. Try once.

    SELECT DISTINCT 
    PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
    CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
    [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    and ([Accreditation Start Date] >= @Eligibility_StartDate and [Accreditation Start Date] <= @Eligibility_Enddate) 
    or (@Eligibility_StartDate is null and @Eligibility_Enddate is null) 


    Regards, RSingh

    Thursday, April 03, 2014 4:37 AM
  • it should be this

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null)
    and ([Accreditation Start Date] < @Eligibility_Enddate+1 or @Eligibility_Enddate is null) 

    Also if @Level,@BU etc are multivlued parameters then you cant use IN as used above. you need to make conditions like this

    ...
    where  ',' + @BU + ',' LIKE '%,' + BU + ',%'
    and ',' + @Level + ',' LIKE '%,' + Level + ',%'
    and ',' + @AccreditationName + ',' LIKE '%,' + [Accreditation Name] + ',%'
    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null)
    and ([Accreditation Start Date] < @Eligibility_Enddate+1 or @Eligibility_Enddate is null) 

    or use a string parsing UDF as below

    http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

    and write it as below

    ... where BU IN (SELECT Val FROM dbo.ParseValues(@BU,','))
    AND Level IN (SELECT Val FROM dbo.ParseValues(@Level,','))
    AND [Accreditation Name] IN (SELECT Val FROM dbo.ParseValues(@AccreditationName,','))

    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null) and ([Accreditation Start Date] < @Eligibility_Enddate+1 or @Eligibility_Enddate is null)




    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, April 03, 2014 5:20 AM
  • try below

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) and (([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null))
    
    

    Thursday, April 03, 2014 5:20 AM
  • Thanks for reply. Actually with the code i have pasted i am able to get output for Between date. but the problem is I have 5 Filters . when i select null. Its not considering the filters. Instead of that it is considering all data.

    PFB screenshot for filter.. Please help to rectify this

    Thursday, April 03, 2014 6:05 AM
  • Thanks for reply. Actually with the code i have pasted i am able to get output for Between date. but the problem is I have 5 Filters . when i select null. Its not considering the filters. Instead of that it is considering all data.

    PFB screenshot for filter.. Please help to rectify this

    See the way I wrote WHERE clause. If you write like that it will still apply the other filters even when date is NULL

    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null)
    and ([Accreditation Start Date] < @Eligibility_Enddate+1 or @Eligibility_Enddate is null) 


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Thursday, April 03, 2014 6:08 AM
    Thursday, April 03, 2014 6:07 AM
  • Go to REPORT tab and select "report parameters".
    Select the parameter Eligibility Start Date parameter and unchecked "Allow Null Value" option.
    Repeat for the other parameter.

    Regards, RSingh

    Thursday, April 03, 2014 6:11 AM
  • That is what i done. 

    If i select date parameters as null. It is giving all the data irrespective of BU,Level and other filters. 

    I hope you got what i mean to say.

    Selecting between range is not a problem. Problem is when i select null . Then is should give data according to other filters

    Thursday, April 03, 2014 6:17 AM
  • Visakh,

    I am getting syntax error for this. But not able figure out. Can you refine?

    Thursday, April 03, 2014 6:25 AM
  • I am reposting the same select statement that I posted earlier. If you have not tried then you may give a try.

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) and (([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null))

    Thursday, April 03, 2014 6:36 AM
  • Shenoy ,

    Tried .. But no luck :(

    Thursday, April 03, 2014 6:44 AM
  • This can be achieved in 3 ways

    1. I did not change anything in your statement just added extra parenthesis to separate date condition execute and check it

    SELECT DISTINCT 
    PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
    CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date],  [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM  P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    and (([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null) )

    2. When you really want to see if there is any date with null then 

    SELECT DISTINCT 
    PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
    CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date],  [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM  P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    AND CASE WHEN @Eligibility_StartDate IS NOT NULL AND @Eligibility_Enddate IS NOT NULL THEN 
    [Accreditation Start Date] BETWEEN @Eligibility_StartDate and @Eligibility_Enddate
    ELSE @Eligibility_StartDate is null and @Eligibility_Enddate is NULL end

    3. If you are trying to say if it you select NULL then query should not use this condition then it is as below


    SELECT DISTINCT 
    PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
    CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
    [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM  P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) 
    AND CASE WHEN @Eligibility_StartDate IS NOT NULL AND @Eligibility_Enddate IS NOT NULL THEN 
    [Accreditation Start Date] BETWEEN @Eligibility_StartDate and @Eligibility_Enddate end

    I did not check the execution, Please try to execute them let us know which one worked for you. also Mark it as answer if it helps you.

     
    Thursday, April 03, 2014 6:48 AM
  • I am reposting the same select statement that I posted earlier. If you have not tried then you may give a try.

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) and [Accreditation Name] in (@AccreditationName) and (([Accreditation Start Date] Between @Eligibility_StartDate and @Eligibility_Enddate) or (@Eligibility_StartDate is null and @Eligibility_Enddate is null))

    This is not what I suggested

    please use it as I suggested

    SELECT DISTINCT 
                             PartnerId, [Partner Name], [Partner Site], [Organization/Country Code], [Accreditation Name], [Accreditation Status], [Accreditation Review Date], 
                             CONVERT(varchar(90), [Accreditation End Date], 105) AS [Accreditation End Date], CONVERT(varchar(90), [Accreditation Start Date], 105) AS [Accreditation Start Date], 
                             [Accreditation Code], [Specialization Code], Name, Inheritance, Description, Territory, Level
    FROM            P1_Addition_Report
    where BU in (@BU) and Level In (@Level) 
    and [Accreditation Name] in (@AccreditationName) 
    and ([Accreditation Start Date] >= @Eligibility_StartDate or @Eligibility_StartDate is null)
    and ([Accreditation Start Date] <= @Eligibility_Enddate @Eligibility_Enddate is null)

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, April 03, 2014 6:50 AM