Неверная сумма при вычислении выражения SQL

Для отчета, который я создаю, мне нужно найти Shrink. Усадка в основном разница. Это входной вес (созданный пользователем), вычитаемый из СУММЫ общего веса (OriginalQuantity_Stk)

В настоящее время я использую выражение

Sum(@InputWeight - [IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]

Я также пытался использовать

 Sum(@InputWeight - [WEIGHT]) AS [Shrink]

Потому что общая сумма веса уже рассчитана:

Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]

Когда пользователь запускает отчет, он вводит некоторую информацию, и одна часть информации — это входной вес. Например, мы скажем, что они вводят 20 000. Если общий вес составляет 19 800, усадка должна равняться 200.

Пример вывода будет выглядеть так:

Входной вес: 20 000
Номер продукта | Вес | Уменьшить
1234 | 12 000 |
1235 | 6 500 |
1236 | 500 |
1237 | 800 |
--------------------------
ИТОГО |19 800 | 200

Дайте мне знать, если это имеет смысл или нет.

Я использую Microsoft SQL Server 2005.

Полный код для справки:

SET NOCOUNT ON; 
DECLARE @PurchaseCost Decimal(19,8);
DECLARE @InputWeight Decimal(19,8);

SET @PurchaseCost = 2.58;
SET @InputWeight = 20000;

SELECT DISTINCT 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) AS [Supplier]
   , [PC].ProductCode
   , [PC].Description1
   , Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
   , IC_ProductLots.UnitOfMeasure_Alt
   , Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost AS [Unit Cost]
   , Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
   , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
   , Sum(@InputWeight - [IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers [ARC] 
     ON [DCT].CustomerKey = [ARC].CustomerKey)
    INNER JOIN  IC_ProductLots 
     ON [DCT].LotKey = IC_ProductLots.LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    (IC_ProductLots.ProductionDate >= { ts '2015-06-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
AND ([ARC].CustomerCode = '         904') 
 GROUP BY 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))
   , [PC].ProductCode
   , [PC].Description1
   , IC_ProductLots.UnitOfMeasure_Alt
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost
   , IC_ProductLots.ProductionDate
   , [ARC].CustomerCode
 ORDER BY 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))

sql-server expression difference
person Austin    schedule 10.07.2015    source источник
comment
sum((1200-19800)+(6500-19800)+(500-19800)+(800-19800)) - это то, что вы делаете, я думаю.   -  person xQbert    schedule 10.07.2015
comment
@xQbert это имело бы смысл, вы знаете, как просто взять общую сумму и использовать ее?   -  person Austin    schedule 10.07.2015
comment
Возможно, это можно сделать с помощью оконной функции, например... @Inputweight - sum( [IC_ProductLots].[OriginalQuantity_Stk]) over (partition by CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) , [PC].ProductCode) определенно будет работать как CTE или подзапрос. Проблема в том, что вам нужна сумма, прежде чем вы сможете выполнить вычитание, но запрос должен обработать все записи, чтобы получить эту сумму. Таким образом, единственный способ получить сумму заранее - это подзапрос, cte или оконная функция (аналитическая).   -  person xQbert    schedule 10.07.2015
comment
@xQbert, когда я это делаю, я получаю сообщение об ошибке: оконные функции могут появляться только в предложениях SELECT или ORDER BY.   -  person Austin    schedule 10.07.2015
comment
ну, грязный ответ (и, возможно, единственный, который делает его менее грязным) - это CTE или подзапрос, тогда вам нужно получить результат суммы, прежде чем вы сможете выбрать из него.   -  person xQbert    schedule 10.07.2015

Ответы (3)


arrow_upward
3
arrow_downward

Почти уверен, что вы хотите взять сумму веса минус входное значение.

Sum([WEIGHT]) - @InputWeight AS [Shrink]

Или, может быть, это

@InputWeight - Sum([WEIGHT]) AS [Shrink]
person Sean Lange    schedule 10.07.2015
comment
(при)входном весе - сумма весов. Но если я сделаю ваше второе выражение, я получаю сообщение об ошибке Невозможно использовать агрегат или подзапрос в выражении, используемом для группы по списку предложения GROUP BY. - person Austin; 10.07.2015
comment
Почему вы размещаете это в группе? Просто измените столбец и оставьте группу в покое. - person Sean Lange; 10.07.2015
comment
Я использую службу, которая не позволяет мне напрямую редактировать SQL. Я могу только вводить фрагменты кода или использовать существующие поля. Я могу сделать так, чтобы он не попадал в группу, используя выражение Sum((at)InputWeight - Sum([WEIGHT])) AS [Shrink], но я получаю сообщение об ошибке Невозможно выполнить агрегатную функцию для выражения, содержащего агрегат или подзапрос. ... Эта программа делает это очень сложно, но я должен использовать ее. - person Austin; 10.07.2015
comment
Фу какой кошмар. Не уверен, какой инструмент вы используете, но звучит ужасно. Я не знаю, как сказать вам исправить запрос, поскольку вы не можете исправить запрос. То, что я предложил, будет работать со стороны t-sql. Я не знаю, как заставить ваш инструмент или что-то еще соответствовать стандарту t-sql. - person Sean Lange; 10.07.2015

arrow_upward
0
arrow_downward

Я думаю, вам нужно вывести свою «логику» за пределы вашей «агрегации».

Например:

@InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]

ОБНОВЛЕНО: я запустил это локально:

SELECT *
FROM #Test

Полученные результаты:

Id      Weight
1234    12000
1235    6500
1236    500
1237    800

DECLARE @other INT = 1000;
SELECT
    Id,
    SUM([Weight])
FROM #Test
GROUP BY Id

Полученные результаты:

Id      (No column name)
1234    12000
1235    6500
1236    500
1237    800

SELECT
    @other - SUM([Weight]) AS [Shrink]
FROM #Test
GROUP BY Id

Полученные результаты:

Shrink
-11000
-5500
500
200
person Jason H    schedule 10.07.2015
comment
Если я это сделаю, я получаю сообщение об ошибке Невозможно использовать агрегат или подзапрос в выражении, используемом для группы по списку предложения GROUP BY. - person Austin; 10.07.2015
comment
в столбце Сокращение. Это выражение помещается в группу, и я думаю, что это не разрешено. - person Austin; 10.07.2015

arrow_upward
0
arrow_downward

Использование подзапроса должно работать. Я все еще думаю, что оконная функция должна работать, но мне придется поиграть с ней больше, чем я готов без образца данных/структуры.

SELECT supplier
   , productcode
   , description1
   , boxes
   , unitofMeasure_Alt
   , weight
   , unitofMeasure_stk
   , [unit cost]
   , [total sales]
   , [avg. box weight]
   ,  yield/@inputweight as yield
   , @inputweight-shrink as shrink 
FROM (
SELECT 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) AS [Supplier]
   , [PC].ProductCode
   , [PC].Description1
   , Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
   , IC_ProductLots.UnitOfMeasure_Alt
   , Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost AS [Unit Cost]
   , Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
   , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk] ) AS [Yield]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers [ARC] 
     ON [DCT].CustomerKey = [ARC].CustomerKey)
    INNER JOIN  IC_ProductLots 
     ON [DCT].LotKey = IC_ProductLots.LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    (IC_ProductLots.ProductionDate >= { ts '2015-06-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
AND ([ARC].CustomerCode = '         904') 
 GROUP BY 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))
   , [PC].ProductCode
   , [PC].Description1
   , IC_ProductLots.UnitOfMeasure_Alt
   , IC_ProductLots.UnitOfMeasure_Stk
   , [ICP].UnitCost
   , IC_ProductLots.ProductionDate
   , [ARC].CustomerCode)) B
 ORDER BY 
     Supplier
person xQbert    schedule 10.07.2015