SQL Server 2014中具有多个条件的情况 [英] Case with multiple conditions in SQL Server 2014

问题描述

我有一个表'FinancialTrans'，其中有很多字段，但是其中只有3个字段与我有关.

I have a table 'FinancialTrans' which has many fields but only 3 of those fields concern me.

AcctID  TransTypeCode   DateOfTrans  Field 4  Field 5  Field 6....
123     TOLL            2016-06-06
123     TOLL            2016-06-02
123     TOLL            2016-04-28
123     PYMT            2016-03-11
123     TOLL            2015-12-22
123     TOLL            2015-12-22

我需要什么:
当过去两年没有TOLL或PYMT时，我需要打印一个标志.

What I need:
I need to print a Flag when there are no TOLL or PYMT in the last 2 years.

所以按照简单的逻辑:

(When TransTypeCode is 'TOLL' and the MAX(DateOfTrans) is more than 2 years ago)  AND  
(When TransTypeCode is 'PYMT' and the MAX(DateOfTrans) is more than 2 years ago)

到目前为止，我的代码是:

So my code so far is:

select *,
(case when max(case when FT.TransTypeCode in ('TOLL', 'PYMT') 
                    then FT.DateOfTrans 
                    end) 
      over (partition by FT.Acctid) >= dateadd(year, -2, getdate())
      then 0 else 1
      end) as MyFlag

from #temp_TableA A
INNER JOIN FinancialTrans FT ON A.AccountId = FT.AcctId

但是此代码合并了FinancialTrans表的所有其他字段，并将该帐号的每一行与上一个表连接在一起.因此，每个帐号可获得大约1200个重复项.

But this code brings along all the other fields of the FinancialTrans table and joins each row for that account number with previous table. Hence, I get about 1200 duplicates for each account number.

问题:
1.如何不为每个帐号重复1200次?
2.当满足以上两个条件时，如何获得给出标志的列.

Question:
1. How do I NOT get 1200 duplicates for each account number?
2. How do I get a column which gives a flag when the above two conditions are met.

推荐答案

使用GROUP BY，这样您将获得一个帐户行，而不是row_number那里为每一行计算内容.

Use GROUP BY, so you get a single row for account, instead of row_number where you calculate something for every row.

SELECT A.AcctID,
       MAX(case when FT.TransTypeCode in ('TOLL', 'PYMT') 
                 and FT.DateOfTrans >= dateadd(year, -2, getdate())
                then 1
                else 0
           end) as flag

FROM #temp_TableA A
INNER JOIN FinancialTrans FT ON A.AccountId = FT.AcctId
GROUP BY A.AcctID

这篇关于SQL Server 2014中具有多个条件的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！