如何在SQL Server中获得无值的行

Question

我有一个持有每个帐户财务交易数据的表。

**FinTransMaster table:**
------------------------------------
|AcctID  |FinTrnsCode|BusinessDay  |
------------------------------------
|1234567 |INTRST     |2017-09-09   |
------------------------------------
|1234567 |CHARGE     |2017-09-08   |
------------------------------------
|1234567 |PYMNT      |2017-09-01   |
------------------------------------
|1234567 |INTRST     |2017-08-19   |
------------------------------------
|1234567 |INTRST     |2017-08-09   |
------------------------------------
|1234567 |CHARGE     |2017-08-04   |
------------------------------------
|1234567 |PYMNT      |2017-08-01   |
------------------------------------
|1234567 |INTRST     |2017-07-19   |
------------------------------------

我想选择上次在每个帐户上付款。

所以我的代码如下：

SELECT AcctID
      ,[FinTransCode]
      --,MAX([BusinessDay])  --Tried this line, doesn't work
      --,ISNULL(MAX([BusinessDay]), 'Never Paid') Last_Paid --Tried this line too, doesn't work
      ,CASE WHEN ISNULL(MAX([BusinessDay]), 0) = 0 THEN 'Never Paid'
            ELSE MAX([BusinessDay])
       END Last_Paid
  FROM [GEB_DWH].[dbo].[FinTransMaster]
  WHERE AcctID = '1234567'
    AND FinTransTypeCode = 'PYMT'
  GROUP BY AcctID,[FinTransTypeCode]

此代码不起作用，因为有些帐户从未付过。因此，有些帐户在该领域根本没有" PYMNT"代码。他们在该字段中具有其他代码，但该领域没有PYMNT代码。因此，当我为该acctid运行该代码时，我只会得到标题，没有结果。

我如何在该领域为该acctid显示"永不付款"，以便我可以加入另一个结果表？

与ACCTID一起加入：

-----------------------------------------------------------------
|AcctID  |ContactName|Phone Number |Email Address |Last Payment |
-----------------------------------------------------------------
|1234567 |John Doe   |123-123-1234 |[e-mail]  |Never Paid   |
-----------------------------------------------------------------

Answer 1

请在下面尝试：

--Summary Stats by transaction type
    SELECT
    AcctID,
    FinTrnsCode,
    lastActivityDt = max(BusinessDay)
    into #temp
    FROM FinTransMaster 
    group by AcctID,FinTrnsCode
    --Find out all customers with Pmts
    SELECT 
    distinct
    ACCTID,
    LastPmtDate = LastActivityDt
    into #temp2
    FROM #temp where FinTrnsCode = 'PYMNT'
    SELECT ACCTID,
    LastPmtDate = cast(LastPmtDate as varchar(50))  FROM #temp2
    union
    --Get all customers with no pmt records
    SELECT distinct ACCTID, 'Never Paid' FROM #Temp where ACCTID not in (SELECT distinct ACCTID FROM #temp2)  

Answer 2

只需将有条件的条件从 where移动到 select：

SELECT AcctID,
       MAX(CASE WHEN FinTransCode = 'PYMT' THEN BusinessDay END) as LastPayment
FROM [GEB_DWH].[dbo].[FinTransMaster]
 GROUP BY AcctID;

如果某人没有付款，则将值设置为NULL。如果需要一个字符串，则需要将日期转换为字符串，以使类型兼容。