Могу ли я объединить столбцы при использовании SQL Server UNION ALL?

У меня есть две таблицы в SQL Server.

  • Таблица 1: JBIN: Список ВСЕХ счетов-фактур.
  • Таблица 2: ARTH: Список ВСЕХ утвержденных счетов.

Я создал запрос, который отображает информацию о количестве дней, на которое просрочен счет.

Таблица результатов выглядит так:

+-----------------------+--------------+--------------+------+
|     OverdueRange      | OverdueCount | Total Amount | INVO |
+-----------------------+--------------+--------------+------+
| Due in 0-7 days       |            1 | 180215.61    |   -1 |
| Due in 31-45 days     |            1 | 153356.10    |  -32 |
| Overdue by 46+ days   |            1 | 125167.34    |   47 |
| Due in 0-7 days       |           25 | 2708613.25   |   -1 |
| Due in 31-45 days     |            3 | 62312.51     |  -32 |
| Overdue by 15-30 days |           12 | 295140.15    |   16 |
| Overdue by 1-7 days   |            1 | 111070.25    |    2 |
| Overdue by 31-45 days |            1 | 2150.50      |   32 |
| Overdue by 46+ days   |            8 | 546907.75    |   47 |
| Overdue by 8-14 days  |            1 | 156985.68    |    9 |
+-----------------------+--------------+--------------+------+

В этом запросе у меня есть инструкция UNION ALL.

Первые три строки таблицы взяты из JBIN, а остальные - из ARTH.

Возможно ли при использовании UNION ALL объединить данные. Например, вместо двух строк с названием «Срок погашения через 0–7 дней» у меня есть ОДНА единственная строка, которая суммирует поля OverdueCount и Total Amount.

E.G.

Вместо этого:

+-----------------+--------------+--------------+------+
|  OverdueRange   | OverdueCount | Total Amount | INVO |
+-----------------+--------------+--------------+------+
| Due in 0-7 days |            1 | 180215.61    |   -1 |
| Due in 0-7 days |           25 | 2708613.25   |   -1 |
+-----------------+--------------+--------------+------+

Я хочу этот:

+-----------------+--------------+--------------+------+
|  OverdueRange   | OverdueCount | Total Amount | INVO |
+-----------------+--------------+--------------+------+
| Due in 0-7 days |           26 | 2888828.86   |   -1 |
+-----------------+--------------+--------------+------+

К вашему сведению:

Это мой запрос:

WITH Temp ([InvDescription], [InvoiceAmount], [OverdueBy], [DatePaid])
AS 
(select InvDescription, InvTotal as InvoiceAmount, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy, NULL as DatePaid from JBIN
Where InvStatus <> 'I' and JBCo = 1 and InvTotal > 0 

),
Temp2 ([InvDescription], [InvoiceAmount], [OverdueBy], [DatePaid])
AS 
(select Description, SUM(Invoiced) as InvoiceAmount, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy, PayFullDate as DatePaid from ARTH
Where ARCo = 1 and Invoiced > 0 
Group By Description, DueDate, PayFullDate, Invoice
)
SELECT 
   CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
        WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
        WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
        WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
        WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
        WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
        WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
        WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
        WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
        WHEN OverdueBy <= -46 THEN 'Due in 46+ days'
        ELSE 'Less than that' 
   END AS OverdueRange,
   Count(*) as OverdueCount,
   Sum(Temp.InvoiceAmount) as [Total Amount], 
   CASE WHEN OverdueBy >= 46 THEN '47' 
        WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN '32'
        WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN '16'
        WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN '9'
        WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN '2'
        WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN '-1'
        WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN '-9'
        WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN '-16'
        WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN '-32'
        WHEN OverdueBy <= -46 THEN '-47'
        ELSE 'ERROR' 
   END AS [INVO]
From Temp
group by CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
            WHEN OverdueBy <= -46 THEN 'Due in 46+ days'

ELSE 'Less than that' END,
CASE WHEN OverdueBy >= 46 THEN '47' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN '32'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN '16'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN '9'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN '2'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN '-1'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN '-9'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN '-16'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN '-32'
            WHEN OverdueBy <= -46 THEN '-47'
ELSE 'ERROR' END
UNION ALL 

SELECT CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
            WHEN OverdueBy <= -46 THEN 'Due in 46+ days'
ELSE 'Less than that' END AS OverdueRange,
Count(*) as OverdueCount,
Sum(Temp2.InvoiceAmount) as [Total Amount], 
CASE WHEN OverdueBy >= 46 THEN '47' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN '32'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN '16'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN '9'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN '2'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN '-1'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN '-9'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN '-16'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN '-32'
            WHEN OverdueBy <= -46 THEN '-47'
ELSE 'ERROR' END AS [INVO]
From Temp2
Where Temp2.DatePaid is null

group by CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
            WHEN OverdueBy <= -46 THEN 'Due in 46+ days'

ELSE 'Less than that' END,
CASE WHEN OverdueBy >= 46 THEN '47' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN '32'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN '16'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN '9'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN '2'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN '-1'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN '-9'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN '-16'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN '-32'
            WHEN OverdueBy <= -46 THEN '-47'
ELSE 'ERROR' END

sql sql-server sum union-all
person pgunston    schedule 23.06.2014    source источник
comment
почему бы вам не добавить еще один CTE и назначить просроченные (... просроченные на 31-45 дней ...) значения в качестве нового столбца в запросе CTE   -  person NeedAnswers    schedule 24.06.2014
comment
Я считаю, что ваш вопрос не в том, как объединить столбцы, а в том, как объединить строки.   -  person J. Schei    schedule 18.09.2019

Ответы (1)


arrow_upward
6
arrow_downward

Вы можете создать производную таблицу из вашего запроса на объединение всех. Что-то вроде этого:

select somefields, sum(something) thesum
from (
union all query goes here
) derivedTable
group by somefields
person Dan Bracuk    schedule 23.06.2014
comment
Есть ли шанс быть более конкретным? Я пробовал это, но думаю, что делаю все неправильно. - person pgunston; 24.06.2014
comment
Запросы в вашем вопросе очень запутаны, возможно, даже больше, чем необходимо. Начните с чего-нибудь простого и наращивайте его. - person Dan Bracuk; 24.06.2014