none
CASE RRS feed

  • Question

  • Hi,

    From the query below, if shipdate is null then I use the planneddate, else I use the shipdate to compare between from and to.  Not sure what I have wrong here, but it did not work for me.  Can you please help.-thanks

    declare @ID int
    declare @bp nvarchar(20)
    declare @From date
    declare @To date

    set @ID =99
    set @bp = '30'
    set @From ='10/21/2019'
    set @To ='10/21/2019'

    select  ID,orders,bp, plandedate,shipdate
    from  saleorders
    where [ID]=@ID
    and bp =@bp
    and 
    case when shipdate is null then  plandedate between CONVERT(date, @From,101) and CONVERT(date,@To,101)
      else   shipdate between CONVERT(date, @From,101) and CONVERT(date,@To,101)

    end

    Monday, October 21, 2019 6:54 PM

All replies

  • Please check if below works:

    DECLARE @ID INT
    DECLARE @bp NVARCHAR(20)
    DECLARE @From DATE
    DECLARE @To DATE
    
    SET @ID = 99
    SET @bp = '30'
    SET @From = '10/21/2019'
    SET @To = '10/21/2019'
    
    SELECT ID, orders, bp, plandedate, shipdate
    FROM saleorders
    WHERE [ID] = @ID
    	AND bp = @bp
    	AND coalesce(plandedate, shipdate) BETWEEN CONVERT(DATE, @From, 101)
    		AND CONVERT(DATE, @To, 101)


    Regards,
    Vaibhav

    Monday, October 21, 2019 7:24 PM
  • thank you very much !!
    Monday, October 21, 2019 7:32 PM
  • thank you very much !!

    Since you have got your answer, please develop your habit of closing the thread by marking useful reply as answer.

    It would be beneficial to others who access your threads.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 5:37 AM