如何检查具有多个条件的SQL CASE？

I have two tables. Accounts ACC and FinancialTrans FT

我有两张桌子。账户ACC和FinancialTrans FT

The FinancialTrans table is as follows:

FinancialTrans表如下：

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

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

要求是：当任何账户在过去2年内没有'TOLL'或'PYMT'时打印'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...

4 个解决方案

#1

try this one:

试试这个：

SELECT
    acc.*,
    CASE WHEN f.acctid IS NULL THEN 'flag' ELSE '' END AS flag_noTollOrPmt
FROM
    accounts acc LEFT OUTER JOIN
    (SELECT 
        AcctID, 
        MAX(DateOfTrans) AS max_dateOfTrans_TollOrPmt
    FROM 
        FinanceTrans 
    WHERE 
        DateOfTrans >= DATEADD(YEAR, -2, GETDATE()) AND
        TransTypeCode IN( 'TOLL' , 'PYMT') 
    GROUP BY 
        AcctID) f ON
    acc.acctid = f.acctid

4 个解决方案

#1

更多相关文章

随机推荐