如何检查具有多个条件的SQL CASE？ [英] How to check a SQL CASE with multiple conditions?

问题描述

我有两个桌子。帐户ACC和FinancialTrans FT

I have two tables. Accounts ACC and FinancialTrans FT

FinancialTrans表如下：

The FinancialTrans table is as follows:

AcctID  TransTypeCode   DateOfTrans
123     TOLL            2016-06-06 00:00:00.000
123     TOLL            2016-06-02 00:00:00.000
123     TOLL            2016-04-28 00:00:00.000
123     PYMT            2016-03-11 00:00:00.000
123     TOLL            2015-12-22 00:00:00.000
123     TOLL            2015-12-22 00:00:00.000

要求为：

当任何帐户没有 TOLL或过去两年中的PYMT''打印'Flag'

The requirement is:
When any Accounts have NO 'TOLL' or 'PYMT' in the last 2 years print 'Flag'

SELECT ACC.Field1
      ,ACC.Field2
      ,ACC.Field3
      ,ACC.Field4
      ,CASE WHEN  
        (SELECT Max(DateOfTrans) FROM FinanceTrans FT
         WHERE ACC.AccountID = FT.AcctID
               AND (TransTypeCode = 'TOLL' AND DateOfTrans >= DATEADD(year, -2, GETDATE()))
               AND (TransTypeCode = 'PYMT' AND DateOfTrans >= DATEADD(year, -2, GETDATE()))
             GROUP BY AcctID, TransTypeCode) IS NULL
        THEN 'Flag'
        ELSE ''
       AND AS NoNo_Flag

FROM Accounts ACC

WHERE Condition 1, Condition 2...

推荐答案

这就是我解决此问题的方式：

So this is how I resolved this issue:

我为

然后我使用条件从临时表中提取数据以创建标志。

I created a separate column for each, first and then stored those details in a temporary table.
Then I pulled data from the temporary table using conditions to create the flag.

我的代码如下：

SELECT ACC.Field1
      ,ACC.Field2
      ,ACC.Field3
      ,ACC.Field4
      ,(SELECT Max(DateOfTrans) FROM FinanceTrans FT
        WHERE ACC.AccountID = FT.AcctID
          AND TransTypeCode = 'TOLL'
        GROUP BY AcctID, TransTypeCode) LastTollDate
      ,(SELECT Max(DateOfTrans) FROM FinanceTrans FT
        WHERE ACC.AccountID = FT.AcctID
          AND TransTypeCode = 'PYMT'
        GROUP BY AcctID, TransTypeCode) LastPymtDate

INTO #Temp_Data

FROM Accounts ACC

WHERE Condition 1, Condition 2...



SELECT ACC.Field1
      ,ACC.Field2
      ,ACC.Field3
      ,ACC.Field4
      ,CASE WHEN LastTollDate >= DATEADD(year, -2, GETDATE())
        AND LastPymtDate >= DATEADD(year, -2, GETDATE())
        THEN 'Flag'
        ELSE ''
       END AS Flag

FROM #Temp_Data

这篇关于如何检查具有多个条件的SQL CASE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！