If today is friday then extract data 7 to 9 days from today, if not 7 days

If today is Friday, I would like to get data 7 to 9 days from now, if not 7 days. I am using SQL Server.

Before:

id	class	startdate
1	English	2020-12-21 00:00:00.000
2	English	2020-12-22 00:00:00.000
3	Math	2020-12-21 00:00:00.000
4	English	2020-12-27 00:00:00.000
5	Math	2020-12-27 00:00:00.000

If today is 14/12/2020 – Monday, it will produce the result below:

id	class	startdate
1	English	2020-12-21 00:00:00.000
3	Math	2020-12-21 00:00:00.000

I tried the following, but it doesn’t work.

select id, class, startdate
from class
where case datepart(w, GETDATE())
    when 6 then startdate between DateAdd(dd,+9,GETDATE() ) and DateAdd(dd,+10,GETDATE()))
    else startdate between DateAdd(dd,+7,GETDATE() ) and DateAdd(dd,+8,GETDATE()))
end
order by startdate

Answer

You can’t use a CASE expression like this in SQL Server. Try this instead:

select id, class, startdate
from class
cross apply 
(
   select case datepart(dw, GETDATE())
           when 6 then 9
           else 7
         end
) t(days)
where startdate between dateadd(dd, t.days ,GETDATE()) an dateadd(dd,t.days+1,GETDATE())
order by startdate

Demo here

To answer to your comment:

select id, class, startdate
from class
cross apply 
(
select 
  case datepart(dw, GETDATE())
           when 6 then 9
           else 7
         end,
  case datepart(dw, GETDATE())
           when 6 then 11
           else 8
         end  
) t(days1, days2)
where startdate between dateadd(dd, t.days1 ,GETDATE()) and dateadd(dd,t.days2,GETDATE())
order by startdate