Multiple statements in a where clause

Question

I'm trying to create a where clause that has 2 statements that work together and then a separate statement that works by itself if it meets a certain criteria

I've tried CASE statements, AND's, OR's and still nothing works

WHERE HB.CreatedByDate = @ReportDateN AND HB.Date = @ReportDateN - 1

So this code is my current where clause that get's me results that have the CreatedByDate equal to the variable @ReportDateN but also have the regular date equal to the variable @ReportDateN except it minuses one day.

This by itself does what I want it to do but I'd like to add some more.

I want to make it so if a variable called @WeekDay = 'Monday' then to subtract 3 from @ReportDateN in the second part instead of just 1.

Any help will be appreciated, thanks!

Answer 1

   WHERE HB.CreatedByDate = @ReportDateN
     AND HB.DATE = CASE 
        WHEN @Weekday = 'Monday'
            THEN (@ReportDateN - 3)
        ELSE (@ReportDateN - 1)
         END

This can also be done by combining OR Statements

WHERE HB.CreatedByDate = @ReportDateN AND 
      (
       @Weekday = 'Monday' AND HB.Date = (@ReportDateN - 3) 
      )
   OR
      (
        @Weekday != 'Monday' AND HB.Date = (@ReportDateN - 1) 
      )

Just for Reference, note that the following will not work:

 WHERE CASE @Weekday = 'Monday' THEN .... ELSE ..... END 

EDIT: Per op's comment below:

WHERE HB.CreatedByDate = @ReportDateN AND 
      (
       @Weekday = 'Monday' AND (
                                HB.Date = (@ReportDateN - 3) OR
                                HB.Date = (@ReportDateN - 2) OR
                                HB.Date = (@ReportDateN - 1) 
                               )  
      )
   OR
      (
        @Weekday != 'Monday' AND HB.Date = (@ReportDateN - 1) 
      )