总和大小写 - 如果为 NULL 则不显示记录

Question

我使用以下语句:

    SELECT 
        Number As [ACCOUNT NUMBER],
        SUM(CASE WHEN [Paid_Pmt_Dt] >= '05/01/2014'
        AND[Paid_Pmt_Dt] <= '05/31/2014' AND [Total_Pmt_Amt] > '0' THEN Total_Pmt_Amt End) [MAY],
        SUM(CASE WHEN [Paid_Pmt_Dt] >= '06/01/2014'
        AND[Paid_Pmt_Dt] <= '06/30/2014' AND [Total_Pmt_Amt] > '0' THEN Total_Pmt_Amt End) [JUNE]
    FROM Gross_Pmt
    GROUP BY Number

在某些情况下，两个月中的任何一个月都没有付款-在这些情况下，我看到两个月都为空。是否有一种方法，当这种情况发生时，不显示记录，如果两个月是NULL?

Answer 1

SELECT 
    Number As [ACCOUNT NUMBER],
    SUM(CASE 
        WHEN 
            [Paid_Pmt_Dt] >= '05/01/2014' AND
            [Paid_Pmt_Dt] <= '05/31/2014' AND 
            [Total_Pmt_Amt] > '0' 
        THEN Total_Pmt_Amt 
        END) [MAY],
    SUM(CASE 
        WHEN 
            [Paid_Pmt_Dt] >= '06/01/2014' AND
            [Paid_Pmt_Dt] <= '06/30/2014' AND 
            [Total_Pmt_Amt] > '0' 
        THEN Total_Pmt_Amt 
        END) [JUNE]
FROM Gross_Pmt
WHERE Paid_Pmt_Dt >= '05/01/2014' AND Paid_Pmt_Dt <= '6/30/2014'
GROUP BY Number

Answer 2

添加月和年到组by子句

SELECT Number [ACCOUNT NUMBER],YEAR([Paid_Dmt_Dt]) YYYY,MONTH([Paid_Dmt_Dt]) MM,SUM(Total_Pmt_Amt) GrossTotal
FROM Gross_Pmt 
GROUP BY Number, YEAR([Paid_Dmt_Dt]),MONTH([Paid_Dmt_Dt])

默认情况下会消除空月份。您也不需要[Total_Pmt_Amt] > '0'。

这将返回每个月的一行，但我认为您可以使用它作为PIVOT的基础或使用此解决方案。

Answer 3

有三种方法可以解决这个问题:

1. 添加WHERE子句，只过滤两个月内的日期
2. 添加一个HAVING子句来过滤非null的sum
3. 将查询封装为内部查询，并在SUM结果列上使用WHERE子句指定它们不都为空

选项1。是更有效和最容易编码。将WHERE子句添加到查询中:

...
FROM ...
WHERE Paid_Pmt_Dt BETWEEN '05/01/2014' AND '06/30/2014'
GROUP BY ...

Answer 4

如果我理解正确的话，您只需要过滤两个计算列都返回NULL的行:

SELECT * 
FROM
  (
    SELECT 
        Number As [ACCOUNT NUMBER],
        SUM(CASE WHEN [Paid_Pmt_Dt] >= '05/01/2014'
        AND[Paid_Pmt_Dt] <= '05/31/2014' AND [Total_Pmt_Amt] > '0' THEN Total_Pmt_Amt End) [MAY],
        SUM(CASE WHEN [Paid_Pmt_Dt] >= '06/01/2014'
        AND[Paid_Pmt_Dt] <= '06/30/2014' AND [Total_Pmt_Amt] > '0' THEN Total_Pmt_Amt End) 
    FROM Gross_Pmt
    GROUP BY Number
  ) as dt
WHERE [MAY] IS NOT NULL OR [JUNE] IS NOT NULL