none
SQl: Case Statement in Where clause RRS feed

  • Question

  • Hi guys - Please see below. I am pulling the data based on Invoice Date with the parameters (@rpt_month_begin and @rpt_month_end). 

    I want to modify the where clause saying: "If the Invoice Number is like PEX, then pull the data based on Posting date if not Invoice Date".


    • Edited by kkran Thursday, March 7, 2019 7:40 PM removed screenshot
    Wednesday, March 6, 2019 2:47 PM

Answers

  • Hi,

    Use this :

    (CASE WHEN pd.InvoiceNumber LIKE 'PEX' THEN pd.PostingDate ELSE pd.InvoiceDate END)>=@rpt_month_begin

    AND (CASE WHEN pd.InvoiceNumber LIKE 'PEX' THEN pd.PostingDate ELSE pd.InvoiceDate END)<@rpt_month_end


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Wednesday, March 6, 2019 3:00 PM
  • ...
    where 1=1
    and CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN postingDate ELSE invoceDate END >= @rpt_month_begin
    and CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN postingDate ELSE invoceDate END >= @rpt_month_end
    ...

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Wednesday, March 6, 2019 3:04 PM
  • Hi kkran,

    I think the above two replies need to be combined. In the first reply, it lost the fuzzy query keyword '%', and in the second query I think the table alias should be added before the column. Because it might be that in the table you're joining, there might be two columns 'postingDate' and 'invoceDate.'

     

    Please try to change above script into following one.

     
    …
    -----where 1=1
    -----and pd.invoceDate>= @rpt_month_begin--'1/1/2019'
    -----and pd.invoceDate  >= @rpt_month_end --'2/1/2019'
    
    where 1=1
    and (CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN pd.postingDate ELSE pd.invoceDate END) >= @rpt_month_begin
    and (CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN pd.postingDate ELSE pd.invoceDate END) >= @rpt_month_end
    ...
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Thursday, March 7, 2019 3:11 AM

All replies

  • Hi,

    Use this :

    (CASE WHEN pd.InvoiceNumber LIKE 'PEX' THEN pd.PostingDate ELSE pd.InvoiceDate END)>=@rpt_month_begin

    AND (CASE WHEN pd.InvoiceNumber LIKE 'PEX' THEN pd.PostingDate ELSE pd.InvoiceDate END)<@rpt_month_end


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Wednesday, March 6, 2019 3:00 PM
  • ...
    where 1=1
    and CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN postingDate ELSE invoceDate END >= @rpt_month_begin
    and CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN postingDate ELSE invoceDate END >= @rpt_month_end
    ...

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Wednesday, March 6, 2019 3:04 PM
  • Hi kkran,

    I think the above two replies need to be combined. In the first reply, it lost the fuzzy query keyword '%', and in the second query I think the table alias should be added before the column. Because it might be that in the table you're joining, there might be two columns 'postingDate' and 'invoceDate.'

     

    Please try to change above script into following one.

     
    …
    -----where 1=1
    -----and pd.invoceDate>= @rpt_month_begin--'1/1/2019'
    -----and pd.invoceDate  >= @rpt_month_end --'2/1/2019'
    
    where 1=1
    and (CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN pd.postingDate ELSE pd.invoceDate END) >= @rpt_month_begin
    and (CASE WHEN pd.invoiceNumber LIKE 'PEX%' THEN pd.postingDate ELSE pd.invoceDate END) >= @rpt_month_end
    ...
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    • Marked as answer by kkran Thursday, March 7, 2019 1:41 PM
    Thursday, March 7, 2019 3:11 AM