查询以填写缺少的匹配。

Question

我从一个拨号程序中获得了以下的配置列表：

CREATE TABLE DiallerOutcomes
    ([DiallerOutcome] varchar(25))
;

INSERT INTO DiallerOutcomes
    ([DiallerOutcome])
VALUES
    ('Application (A)'),
    ('Answer Machine (A)'),
    ('Answering Machine (A)'),
    ('Call Back Interested (A)'),
    ('Call Back Unavailable (A)'),
    ('Could Not Help Debt (A)'),
    ('No Answer (A)'),
    ('Older Customer (A)'),
    ('Answer Machine (D)'),
    ('No Answer (D)')
;

+---------------------------+
|      DiallerOutcome       |
+---------------------------+
| Application (A)           |
| Answer Machine (A)        |
| Answering Machine (A)     |
| Call Back Interested (A)  |
| Call Back Unavailable (A) |
| Could Not Help Debt (A)   |
| No Answer (A)             |
| Older Customer (A)        |
| Answer Machine (D)        |
| No Answer (D)             |
+---------------------------+

和下列数据：

CREATE TABLE AffiliateLeads
    ([Affiliate] varchar(10), [DiallerOutcome] varchar(24))
;

INSERT INTO AffiliateLeads
    ([Affiliate], [DiallerOutcome])
VALUES
    ('affiliate1', 'No Answer (D)'),
    ('affiliate1', 'Application (A)'),
    ('affiliate1', 'Customer Hung Up (A)'),
    ('affiliate2', 'No Answer (A)'),
    ('affiliate3', 'Application (A)'),
    ('affiliate4', 'No Answer (D)'),
    ('affiliate4', 'Could Not Help (A)'),
    ('affiliate3', 'No Answer (D)'),
    ('affiliate1', 'Customer Hung Up (A)'),
    ('affiliate3', 'No PBA (A)'),
    ('affiliate3', 'Dead Line (A)'),
    ('affiliate3', 'Answer Machine (A)'),
    ('affiliate3', 'Customer Hung Up (A)'),
    ('affiliate3', 'Answer Machine (A)'),
    ('affiliate3', 'Application (A)'),
    ('affiliate3', 'Dead Line (D)'),
    ('affiliate1', 'Application (A)'),
    ('affiliate3', 'Could Not Help (A)'),
    ('affiliate2', 'Application (A)'),
    ('affiliate2', 'Call Back Interested (A)'),
    ('affiliate2', 'Customer Hung Up (A)'),
    ('affiliate1', 'Call Back Interested (A)'),
    ('affiliate1', 'Answer Machine (A)'),
    ('affiliate1', 'No Answer (A)')
;

+------------+--------------------------+
| Affiliate  |      DiallerOutcome      |
+------------+--------------------------+
| affiliate1 | No Answer (D)            |
| affiliate1 | Application (A)          |
| affiliate1 | Customer Hung Up (A)     |
| affiliate2 | No Answer (A)            |
| affiliate3 | Application (A)          |
| affiliate4 | No Answer (D)            |
| affiliate4 | Could Not Help (A)       |
| affiliate3 | No Answer (D)            |
| affiliate1 | Customer Hung Up (A)     |
| affiliate3 | No PBA (A)               |
| affiliate3 | Dead Line (A)            |
| affiliate3 | Answer Machine (A)       |
| affiliate3 | Customer Hung Up (A)     |
| affiliate3 | Answer Machine (A)       |
| affiliate3 | Application (A)          |
| affiliate3 | Dead Line (D)            |
| affiliate1 | Application (A)          |
| affiliate3 | Could Not Help (A)       |
| affiliate2 | Application (A)          |
| affiliate2 | Call Back Interested (A) |
| affiliate2 | Customer Hung Up (A)     |
| affiliate1 | Call Back Interested (A) |
| affiliate1 | Answer Machine (A)       |
| affiliate1 | No Answer (A)            |
+------------+--------------------------+

我需要分组每个附属机构的处置数量，但仍然显示的结果，没有匹配。预期成果：

+------------+---------------------------+--------------+
| Affiliate  |          Outcome          | No. of Leads |
+------------+---------------------------+--------------+
| affiliate1 | Application (A)           |            2 |
| affiliate1 | Answer Machine (A)        |            1 |
| affiliate1 | Answering Machine (A)     |            0 |
| affiliate1 | Call Back Interested (A)  |            1 |
| affiliate1 | Call Back Unavailable (A) |            0 |
| affiliate1 | Could Not Help Debt (A)   |            0 |
| affiliate1 | No Answer (A)             |            1 |
| affiliate1 | Older Customer (A)        |            0 |
| affiliate1 | Answer Machine (D)        |            0 |
| affiliate1 | No Answer (D)             |            1 |
| affiliate2 | Application (A)           |            1 |
| affiliate2 | Answer Machine (A)        |            0 |
| affiliate2 | Answering Machine (A)     |            0 |
| affiliate2 | Call Back Interested (A)  |            1 |
| affiliate2 | Call Back Unavailable (A) |            0 |
| affiliate2 | Could Not Help Debt (A)   |            0 |
| affiliate2 | No Answer (A)             |            1 |
| affiliate2 | Older Customer (A)        |            0 |
| affiliate2 | Answer Machine (D)        |            0 |
| affiliate2 | No Answer (D)             |            0 |
| affiliate3 | Application (A)           |            2 |
| affiliate3 | Answer Machine (A)        |            2 |
| affiliate3 | Answering Machine (A)     |            0 |
| affiliate3 | Call Back Interested (A)  |            0 |
| affiliate3 | Call Back Unavailable (A) |            0 |
| affiliate3 | Could Not Help Debt (A)   |            0 |
| affiliate3 | No Answer (A)             |            0 |
| affiliate3 | Older Customer (A)        |            0 |
| affiliate3 | Answer Machine (D)        |            0 |
| affiliate3 | No Answer (D)             |            1 |
| affiliate4 | Application (A)           |            0 |
| affiliate4 | Answer Machine (A)        |            0 |
| affiliate4 | Answering Machine (A)     |            0 |
| affiliate4 | Call Back Interested (A)  |            0 |
| affiliate4 | Call Back Unavailable (A) |            0 |
| affiliate4 | Could Not Help Debt (A)   |            0 |
| affiliate4 | No Answer (A)             |            0 |
| affiliate4 | Older Customer (A)        |            0 |
| affiliate4 | Answer Machine (D)        |            0 |
| affiliate4 | No Answer (D)             |            1 |
+------------+---------------------------+--------------+

在寻找解决方案之前，我总是环顾四周，但什么也没有出现，我已经考虑了几个小时，最后才认输并寻求帮助。我知道这可能会是一些花哨的CROSS APPLY。任何事先得到的帮助都会受到感谢。

Answer 1

首先，您需要在所有附属公司和可能的结果之间提供笛卡儿产品：

SELECT  A.Affiliate,
        B.DiallerOutcome,
        ISNULL(COUNT(C.DiallerOutcome),0) [No. of Leads]
FROM (  SELECT DISTINCT Affiliate
        FROM dbo.AffiliateLeads) A
CROSS JOIN dbo.DiallerOutcomes B
LEFT JOIN dbo.AffiliateLeads C
    ON A.Affiliate = C.Affiliate
    AND B.DiallerOutcome = C.DiallerOutcome
GROUP BY A.Affiliate,
         B.DiallerOutcome
ORDER BY A.Affiliate,
         B.DiallerOutcome;