How to make a CASE in the where clause

Question

I have this stored procedure

ALTER PROC rpt_ItemsSales_DayMonthYear_Week --'2022-01-01','2022-02-1',0
    @from datetime,
    @to datetime,
    @ByOpenShift bit
AS
    SET DATEFIRST 7
    ;WITH Weeks (WeeksNumber) AS
        (SELECT 1
         UNION ALL
         SELECT WeeksNumber + 1
         FROM Weeks
         WHERE WeeksNumber < 4)
    SELECT SalesPos_Dtls.ItemName,
           SUM(SalesPos_Dtls.Qty) AS SumQty,
           SUM(SalesPos_Dtls.TotalPrice) AS SumTotal,
           CAST(SalesPos.StartDate AS date) AS StartDate,
           N'الاسبوع ' + CAST(FLOOR((DATEPART(DAY, SalesPos.StartDate) - 2) / 7) + 1 AS varchar(10)) AS Week,
           [dbo].[StartOfWeek](SalesPos.StartDate) AS FirstDay,
           SalesPos_Dtls.ItemId
    FROM Weeks
         LEFT JOIN SalesPos ON CAST(FLOOR((DATEPART(DAY, SalesPos.StartDate) - 2) / 7) + 2 AS varchar(10)) = Weeks.WeeksNumber
                           AND (status = 'IsPosted')
         LEFT JOIN SalesPos_Dtls ON SalesPos.ID = SalesPos_Dtls.OrderId
         LEFT JOIN Shift_Open ON Shift_Open.Shift_Id = SalesPos.Shift_Id
    WHERE (@from <= SalesPos.StartDate
        OR @from IS NULL)
      AND (@to >= SalesPos.StartDate
        OR @to IS NULL)
    GROUP BY SalesPos_Dtls.ItemName,
             SalesPos_Dtls.ItemCode,
             Weeks.WeeksNumber,
             CAST(SalesPos.StartDate AS date),
             SalesPos.StartDate,
             SalesPos_Dtls.ItemId;

That I want it to if the @ByOpenShift is 0 then the @from and @to will be selected from SalesPos.StartDate else it wil be selected from Shift_Open.OpenDate

edit: after using ADN/OR logic it worked fine.

((((CAST(SalesPos.StartDate AS date)>= CAST(@from AS date)and CAST(SalesPos.StartDate AS date) <=CAST(@to AS date)) or(@from is null) and (@to is null) )
and (@ByOpenShift = 0 or @ByOpenShift is null))
or ((CAST(Shift_Open.OpenDate AS date)>= CAST(@from AS date)and CAST(SalesPos.StartDate AS date) <=CAST(@to AS date))
 or(@from is null) and (@to is null)))

Don't use a `CASE` in the `WHERE`, it won't be SARGable. Use `AND`/`OR` logic. — Thom A, Feb 01 '22 at 10:58
Side note, I suggest adding `RECOMPILE` to your `OPTION` clause in the above. What you have there is a "[catch-all query](https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/)", and therefore the query plans for when your parameters are `NULL` or not will be quite different. — Thom A, Feb 01 '22 at 11:00
((((CAST(SalesPos.StartDate AS date)>= CAST(@from AS date)and CAST(SalesPos.StartDate AS date) <=CAST(@to AS date)) or(@from is null) and (@to is null) ) and (@ByOpenShift = 0 or @ByOpenShift is null)) or ((CAST(Shift_Open.OpenDate AS date)>= CAST(@from AS date)and CAST(SalesPos.StartDate AS date) <=CAST(@to AS date)) or(@from is null) and (@to is null))) — Charon484, Feb 01 '22 at 11:16
Does this answer your question? [SQL Switch/Case in 'where' clause](https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause) — Bilal Bin Zia, Feb 01 '22 at 11:47
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Feb 10 '22 at 11:34

How to make a CASE in the where clause

0 Answers0