我如何使用case来确定要使用哪个where子句 [英] How do i use case when to determine the which where clause to be used

问题描述

我目前正在尝试使用Where子句中的Case When来确定应用哪个where子句。

我一周中的每一天我想用PostDt选择记录

I'm currently trying to use the Case When in the Where clause to determine which where clause is applied.
for every day of the week I would like to select records using PostDt

SELECT  
	PostDt,	
	Trandate,	
	AC_NUM 
	[TEXT]	,
	TRAN_AMOUNT	,
	BALANCE	,
	ACCOUNTREFERENCE 															
FROM  
	Transact.dbo.Pmts

WHERE  
Case WHEN  DATENAME(dw,getdate()) = 'Monday'
      THEN 
	   PostDt = CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -28, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Tuesday' 
	 THEN 
	   PostDt = CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -14, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Wednesday'
      THEN 
	 PostDt = CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -7, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Thursday' 
	 THEN 
	   PostDt = CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -1, GETDATE())), 0),111)

< br $> b $ b

我尝试了什么：



我试过用过我试图创建一个存储过程，必须确定它是哪一天，所以我可以选择适当的谓词，但无济于事

What I have tried:

I have tried using I've tried to create a stored procedure to have to determine which day of the week it is so I can select the appropriate predicate but to no avail

推荐答案

案例仅用于指定值。如果你在case子句中执行比较，它将返回一个布尔值（位），但在你结束这个案例之前它不会在子句中使用它。



试试这个：

Case is used only to specify values. If you perform a comparison in a case clause, it will return a boolean (bit), but it won't use that in the clause until you "end" the case.

Try this:

WHERE PostDt =  
Case WHEN  DATENAME(dw,getdate()) = 'Monday'
      THEN 
	   CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -28, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Tuesday' 
	 THEN 
	   CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -14, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Wednesday'
      THEN 
	 CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -7, GETDATE())), 0),111)
	 WHEN  DATENAME(dw,getdate()) = 'Thursday' 
	 THEN 
	   CONVERT(Varchar(12),DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, -1, GETDATE())), 0),111)

见？现在我将Case的返回值与PostDt进行比较



希望有帮助^ _ ^

Andy

See? Now I am comparing the returned value of the Case with PostDt

Hope that helps ^_^
Andy

这篇关于我如何使用case来确定要使用哪个where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！