CODEWITHSUNDEEP
sqlsql-servert-sql
poppp
poppp

Reputation: 341

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

Upvotes: 1

Views: 105

Answers (4)

openball
openball

Reputation: 21

This has probably been made more complicated than it should. Create two parameters @start and @end If today is Friday set parameters as appropriate else if it is not set as needed. Use parameters in your query

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

OR usually prevents index usage anyway, so you can do:

where datediff(day, getdate(), startdate)
          between 7 and
                  (case when datepart(weekday, GETDATE()) = 6 then 9 else 7 end)

If you are interested in performance, then use two separate queries:

select id, class, startdate
from class
where datepart(weekday, getdate()) = 6 and
      startdate >= dateadd(day, 7, convert(date, getdate())) and
      startdate <= dateadd(day, 9, convert(date, getdate())) 
union all
select id, class, startdate
from class
where datepart(weekday, getdate()) <> 6 and
      startdate = dateadd(day, 7, convert(date, getdate()));

SQL Server should find this easier to optimize.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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

Upvotes: 1

gofr1
gofr1

Reputation: 15987

If you have datetime with actual hh:mi:ss in it - just change it to date and and it would be much easier

select id, 
       class, 
       startdate
from class
where (datepart(w, GETDATE()) = 6 and
    startdate = CAST(DateAdd(dd,+9,GETDATE()) as date) 
) 
    or (datepart(w, GETDATE()) != 6 and
    startdate = CAST(DateAdd(dd,+7,GETDATE()) as date) 
       )
order by startdate

If it is not an option try this:

select id, 
       class, 
       startdate
from class
where (datepart(w, GETDATE()) = 6 and
    startdate between DateAdd(dd,+9,CAST(GETDATE() as date)) 
                  and DATEADD(second,-1,datediff(dd,0,DateAdd(dd,+9,CAST(GETDATE() as date)))+1)
    ) 
    or (datepart(w, GETDATE()) != 6 and
    startdate between DateAdd(dd,+7,CAST(GETDATE() as date)) 
                   -- to get YYYY-MM-DD 00:00:00
                  and DATEADD(second,-1,datediff(dd,0,DateAdd(dd,+7,CAST(GETDATE() as date)))+1)
                  -- to get YYYY-MM-DD 23:59:59
       )
order by startdate

Upvotes: 1

Related Questions