Как создать сводку с помощью sql
Я новичок в SQL, у меня есть такая таблица.
Ниже запрос:
select
gc.GC_Name, dt.GC_SectorType, dt.ageing,
sum(cast(dt.[Brokerage Debtors] as numeric)) as Brokerage_Amt,
dt.divisionalofficename
from
[AR].[Fact_Brokerage_Debt] dt
inner join
AUM.DIM_BUSINESS_TYPE BT on BT.Business_Type_WId_PK = dt.BusinessType_WID
inner join
aum.Dim_GroupCompany gc on dt.insurer_Wid = gc.GC_WID
where
bt.Business_Type_Wid in (4, 8, 10)
and dt.ageing <> '<30'
and cast(dt.[Brokerage Debtors] as numeric) > 0
and gc.GC_SectorType = 'psu'
group by
gc.GC_Name, dt.GC_SectorType, dt.ageing, dt.divisionalofficename
[sql_table]
И мне сказали получить такие данные
[запрошенный_формат]
Grandtotalосновано на общем количестве brockrage_amt.
Я понимаю, что мне нужно использовать функцию PIVOT. Но не могу понять это ясно. Было бы здорово, если бы кто-нибудь мог объяснить это в приведенном выше случае. (Или любые альтернативы, если таковые имеются)
Ответы
Я сделал предположение, что общая сумма основана на названии страховки и коде DO, а общая сумма - это сумма, а не подсчет. Также есть некоторые расхождения между именами полей в вашем запросе, sql_table и request_format. Приведенный ниже пример кода необходимо будет адаптировать к вашей конкретной ситуации, но это основная структура и формат, которые вы просите.
Кроме того, вы не получите точный request_format, потому что результаты запроса не будут иметь цвета, форматирования и т. Д.
Вот рабочий пример с составленными образцами данных:
DECLARE @testdata TABLE
(
[Insurance_Name] VARCHAR(100)
, [DO_Code] VARCHAR(100)
, [ageing] VARCHAR(10)
, [Brokerage_Amt] INT
);
INSERT INTO @testdata (
[Insurance_Name]
, [DO_Code]
, [ageing]
, [Brokerage_Amt]
)
VALUES ( 'Insurance Company 1', '123', '31-60', 100 )
, ( 'Insurance Company 1', '123', '91-120', 200 )
, ( 'Insurance Company 1', '123', '>=365', 300 )
, ( 'Insurance Company 1', '234', '61-90', 300 )
, ( 'Insurance Company 1', '234', '61-90', 300 )
, ( 'Insurance Company 1', '234', '121-180', 300 )
, ( 'Insurance Company 1', '234', '181-364', 200 )
, ( 'Insurance Company 2', '789', '61-90', 50 )
, ( 'Insurance Company 2', '789', '121-180', 25 )
, ( 'Insurance Company 2', '789', '181-364', 9 );
SELECT [pvt].[Insurance_Name]
, [pvt].[DO_Code]
, [31-60]
, [61-90]
, [91-120]
, [121-180]
, [181-364]
, [>=365]
, [pvt].[GrandTotal]
FROM (
SELECT [Insurance_Name]
, [DO_Code]
, [ageing]
, [Brokerage_Amt]
, SUM([Brokerage_Amt]) OVER ( PARTITION BY [Insurance_Name]
, [DO_Code]
) AS [GrandTotal] --here we determine that grand total based on the Insurance_Name and DO_Code
FROM @testdata
) AS [ins]
PIVOT (
SUM([Brokerage_Amt]) --aggregate and pivot this column
FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
) AS [pvt];
Предоставляя вам результаты:
Insurance_Name DO_Code 31-60 61-90 91-120 121-180 181-364 >=365 GrandTotal
------------------------ ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Insurance Company 1 123 100 NULL 200 NULL NULL 300 600
Insurance Company 1 234 NULL 600 NULL 300 200 NULL 1100
Insurance Company 2 789 NULL 50 NULL 25 9 NULL 84
Примерных данных нет, поэтому я предполагаю, что попытка ретро-соответствия вашему запросу будет примерно такой:
SELECT [pvt].[Insurance_Name]
, [pvt].[DO_Code]
, [31-60]
, [61-90]
, [91-120]
, [121-180]
, [181-364]
, [>=365]
, [pvt].[GrandTotal]
FROM (
SELECT [gc].[GC_Name] AS [Insurance_Name]
, [dt].[GC_SectorType] AS [DO_Code]
, [dt].[ageing]
--, SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) AS [Brokerage_Amt]
, CAST([dt].[Brokerage Debtors] AS NUMERIC) AS [Brokerage_Amt]
, SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) OVER (PARTITION BY [gc].[GC_Name], [dt].[GC_SectorType]) AS GrandTotal
, [dt].[divisionalofficename]
FROM [AR].[Fact_Brokerage_Debt] [dt]
INNER JOIN [AUM].[DIM_BUSINESS_TYPE] [BT]
ON [BT].[Business_Type_WId_PK] = [dt].[BusinessType_WID]
INNER JOIN [aum].[Dim_GroupCompany] [gc]
ON [dt].[insurer_Wid] = [gc].[GC_WID]
WHERE [BT].[Business_Type_Wid] IN ( 4, 8, 10 )
AND [dt].[ageing] <> '<30'
AND CAST([dt].[Brokerage Debtors] AS NUMERIC) > 0
AND [gc].[GC_SectorType] = 'psu'
--I guess you would not need the sum and group by, sum should be hanlded in the pivot, but above we add a sum partioning by [gc].[GC_Name], [dt].[GC_SectorType] for the grand total
--GROUP BY [gc].[GC_Name]
-- , [dt].[GC_SectorType]
-- , [dt].[ageing]
-- , [dt].[divisionalofficename];
) AS [ins]
PIVOT (
SUM([Brokerage_Amt]) --aggregate and pivot this column
FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
) AS [pvt];
Я предполагаю, что потребуются изменения, поскольку не было предоставлено никаких выборочных данных и определений таблиц. Поскольку я не могу выполнить запрос, возможны опечатки.