SQL哪里取决于星期的哪一天

I have a requirement to check records up to differing dates, depending on which day of the week it is currently.

我有一个要求检查记录到不同的日期，这取决于它当前的哪天。

On a Friday I need for it to look at the entire next week, until Sunday after next. On any other day it should check the current week, up until the coming Sunday.

在一个星期五，我需要看下一个星期，直到下个星期天。在其他任何一天，它都应该查看当前的一周，直到下个周日。

I have the below currently but it's not working due to syntax error. Is it possible to do a CASE WHEN inside a WHERE clause?

我现在有下面的代码，但是由于语法错误，它不能工作。在WHERE子句中可以做一个CASE吗?

WHERE
    T0.[Status] IN ('R','P') 
    AND
        CASE 
           WHEN DATEPART(weekday,GETDATE()) = '5' 
              THEN T0.[DueDate] >= GETDATE() AND <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())
           WHEN DATEPART(weekday, GETDATE()) != '5' 
              THEN T0.[DueDate] >= GETDATE() AND <= DATEADD(DAY ,8- DATEPART(weekday, GETDATE()), GETDATE())
        END

2 个解决方案

#1

It's much easier to create this logic with a series of logical or and and operators:

用一系列逻辑或和运算符来创建这种逻辑要容易得多:

WHERE
T0.[Status] IN ('R','P') AND
((DATEPART(weekday,GETDATE()) = '5' AND 
  T0.[DueDate] >= GETDATE() AND 
  T0.[DueDate] <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())) OR
 (DATEPART(weekday,GETDATE()) != '5' AND 
  T0.[DueDate] >= GETDATE() AND 
  T0.[DueDate] <= DATEADD(DAY ,8- DATEPART(weekday,GETDATE()),GETDATE())
)

#2

Your syntax is wrong, you are using a condition evaluation in the THEN clause instead of an assignemnet

语法错误，您使用的是THEN子句中的条件评估，而不是assignemnet

  WHEN DATEPART(weekday,GETDATE()) = '5' THEN  Your_column1  ELSE your_column2 END
  ......

or a inner case

或内部情况

  CASE
    WHEN DATEPART(weekday,GETDATE()) = '5' THEN 
            CASE  WHEN T0.[DueDate] >= GETDATE() 
                    AND <= DATEADD(day, 15 - DATEPART(weekday, GetDate()), GetDate())  
                    THEN Your_column1  ELSE your_column2 
            END 
 END
   ......

#1