带群的条件计数

Question

需要帮助的条件计数，我不能显示数据，我需要我的报告。我正在使用sql server 2008，sap晶体报告。

我的密码

    SELECT c.Clients_Name
    ,COUNT(CASE 
            WHEN b.FI_result = 'Passed'
                AND Date_applied >= @DS
                AND Date_applied <= @DE
                THEN 1
            END) AS [Jan]
    ,COUNT(CASE 
            WHEN b.FI_result = 'Passed'
                AND Date_applied >= @DS
                AND Date_applied <= @DE
                THEN 1
            END) AS [Feb]
    ,COUNT(CASE 
            WHEN b.FI_result = 'Passed'
                AND Date_applied >= @DS
                AND Date_applied <= @DE
                THEN 1
            END) AS [Mar]
FROM Applicant_Operators AS a
INNER JOIN Applicant_IS_History AS b ON a.Applicant_ID = b.Applicant_ID
INNER JOIN Clients AS c ON b.Client_Name = c.Client_ID
WHERE Date_applied >= @DS
    AND Date_applied <= @DE
GROUP BY c.Clients_Name

代码显示

Clients     Jan Feb Mar
DENSO       14  14  14
NPI         7   7   7
MICROTOP    48  48  48

我想展示的东西

Clients     Jan Feb Mar
DENSO       5   3   6
NPI         2   1   4
MICROTOP    10  20  18

任何帮助都是非常感谢的。

Answer 1

你需要一些东西来确认这个月。也许这能做你想做的事：

SELECT c.Clients_Name,
       SUM(CASE WHEN b.FI_result = 'Passed' AND
                     Date_applied >= @DS AND Date_applied <= @DE AND
                     MONTH(Date_applied) = 1
                THEN 1 ELSE 0
           END) AS [Jan],
       . . .

我用一个count()代替了sum()，这是我个人对条件聚合的偏好。