Полное внешнее объединение, объединение и группировка по (Oh My!)

Question

Я собираюсь спросить об этом в двух частях, потому что моя логика может быть далеко, и если так, синтаксис на самом деле не имеет значения.

1. У меня 10 запросов. Каждый запрос возвращает месяц, поставщика и количество (некоторые показатели). В запросах используются различные таблицы, объединения и т. Д. Не все комбинации месяц / поставщик существуют в выходных данных для каждого запроса. Я хотел бы объединить их в один набор данных, который можно экспортировать и поворачивать в Excel.

Я хотел бы, чтобы результат выглядел так:

Month | Supplier | Metric1 |Metric2 |..| Metric 10
2018-01 | Supp1 | _value_ | _value_ |...| _value_ |
2018-01 | Supp2 | NULL | _value_ |...| NULL

Какой самый лучший / самый простой / самый эффективный способ сделать это?

2. Я пробовал различные методы для достижения вышеизложенного, но я не могу понять синтаксис совершенно правильно. Я хотел сделать очень простой тестовый пример и опираться на него, но у меня есть только привилегии выбора для БД, поэтому я не могу его протестировать. Мне удалось создать запрос, который по крайней мере не приводит к появлению каких-либо кривых красных линий ошибок, но применение той же логики к большей проблеме не работает.

Вот что у меня есть:

 create table test1(name varchar(20),credit int);
 insert into test1 (name, credit) values ('Ed',1),('Ann',1),('Jim',1),('Ed',1),('Ann',1);
 create table test2 (name varchar(10), debit int);
 insert into test2 (name, debit) values ('Ann',1),('Sue',1),('Sue',1),('Sue',1);

 select
    coalesce(a.name, b.name) as name,
    cred,
    deb
 from
    (select name, count(credit) as cred
        from test1 
        group by name) a
    full outer join 
    (select name, count(debit) as deb
        from test2
        group by name) b  on 
     a.name =b.name;

Я направился по правильному пути?

ОБНОВЛЕНИЕ: Основываясь на входе Гордона, я попробовал это на первых двух запросах:

select Month, Supp,
    sum(case when which = 1 then metric end) as Exceptions,
    sum(case when which = 2 then metric end) as BackOrders
from (
        (
         select Month, Supp, metric, 1 as which
         from (
                select (convert(char(4),E.PostDateTime,120)+'-'+convert(char(2),E.PostDateTime,101)) as Month, E.TradingPartner as Supp, count(distinct(E.excNum)) as metric
                from db..TrexcMangr E
                where (E.DSHERep in ('AVR','BTB') OR E.ReleasedBy in ('AVR','BTB')) AND year(E.PostDateTime) >= '2018'
              ) a
         )

         union all

        (
         select Month, Supp, metric, 2 as which
         from (
                select (convert(char(4),T.UpdatedDateTime,120)+'-'+convert(char(2),T.UpdatedDateTime,101)) as Month, P.Supplier as Supp, count(*) as metric
                from db1..trordertext T
                    inner join mdid_Tran..trOrderPO P on P.PONum = T.RefNum
                where T.TextType = 'BO' AND (T.CreatedBy in ('AVR','BTB') OR T.UpdatedBy in ('AVR','BTB')) AND year(UpdatedDateTime) >=2018
              ) b
        )
     ) q
group by Month, Supp

... но я получаю группу по ошибке.

Answer 1

Один метод использует union all а также group by:

select month, supplier,
       sum(case when which = 1 then metric end) as metric_01,
       sum(case when which = 2 then metric end) as metric_02,
       . . .
from ((select Month, Supplier, Metric, 1 as which
       from (<query1>) q
       . . .
      ) union all
      (select Month, Supplier, Metric, 2 as which
       from (<query2>) q
       . . .
      ) union all
      . . .
     ) q
group by month, supplier;

Answer 2

SELECT
    CalendarMonthStart,
    Supp,
    SUM(CASE WHEN metric_id = 1 THEN metric END) as Exceptions,
    SUM(CASE WHEN metric_id = 2 THEN metric END) as BackOrders
FROM
(
    SELECT
        DATEADD(month, DATEDIFF(month, 0, E.PostDateTime), 0)   AS CalendarMonthStart,
        E.TradingPartner            AS Supp,
        COUNT(DISTINCT(E.excNum))   AS metric,
        1                           AS metric_id
    FROM
        db..TrexcMangr E
    WHERE
        (      E.DSHERep    in ('AVR','BTB')
            OR E.ReleasedBy in ('AVR','BTB')
        )
        AND E.PostDateTime >= '2018-01-01'
    GROUP BY
        1, 2

    UNION ALL

    SELECT
        DATEADD(month, DATEDIFF(month, 0, T.UpdatedDateTime), 0)   AS CalendarMonthStart,
        T.UpdatedDateTime,
        P.Supplier                  AS Supp,
        COUNT(*)                    AS metric,
        2                           AS metric_id
    FROM
        db1..trordertext T
    INNER JOIN
        mdid_Tran..trOrderPO P
            ON  P.PONum = T.RefNum
    WHERE
        (      T.CreatedBy in ('AVR','BTB')
            OR T.UpdatedBy in ('AVR','BTB')
        )
        AND T.TextType = 'BO'
        AND T.UpdatedDateTime >= '2018-01-01'
    GROUP BY
        1, 2
)
   combined
GROUP BY
    CalendarMonthStart,
    Supp