SQL 运行减法 [英] SQL Running Subtraction
问题描述
我的结果集如下:
Item ExpectedQty ReceivedQty Short
Item01 30 45 5
Item01 20 45 5
Item02 40 38 2
item03 50 90 10
item03 30 90 10
item03 20 90 10
查询是:
select a.Item, a.ExpectedQty,b.ReceivedQty, b.Short
from a join b on a.Item = b.Item
我需要获得第二张图表中的结果.基本上我在每一行都有收到的总数量,我需要根据预期数量显示收到的数量,如果有任何短缺,我需要在最后一行显示.
I need to get result as in second chart. Basically I have a total of received quantity in each line and I need to show received quantity against Expected Quantity, if there is any shortage I need to show in last line.
预期:
Item ExpectedQty ReceivedQty Short
item01 30 30 0
item01 20 15 5
item02 40 38 2
item03 50 50 0
item03 30 30 0
item03 20 10 10
提前致谢.
已编辑,02期;
-- Just a brief of business scenario is table has been created for a good receipt.
-- So here we have good expected line with PurchaseOrder(PO) in first few line.
-- And then we receive each expected line physically and that time these
-- quantity may be different
-- due to business case like quantity may damage and short quantity like that.
-- So we maintain a status for that eg: OK, Damage, also we have to calculate
-- short quantity
-- based on total of expected quantity of each item and total of received line.
if object_id('DEV..Temp','U') is not null
drop table Temp
CREATE TABLE Temp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Item VARCHAR(32),
PO VARCHAR(32) NULL,
ExpectedQty INT NULL,
ReceivedQty INT NULL,
[STATUS] VARCHAR(32) NULL,
BoxName VARCHAR(32) NULL
)
-- Please see first few line with PO data will be the expected lines,
-- and then rest line will be received line
INSERT INTO TEMP (Item,PO,ExpectedQty,ReceivedQty,[STATUS],BoxName)
SELECT 'ITEM01','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01','PO-02','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM02','PO-01','40',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-01','50',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-02','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-03','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM04','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01',NULL,NULL,'20','OK','box01' UNION ALL
SELECT 'ITEM01',NULL,NULL,'25','OK','box02' UNION ALL
SELECT 'ITEM01',NULL,NULL,'5','DAMAGE','box03' UNION ALL
SELECT 'ITEM02',NULL,NULL,'38','OK','box04' UNION ALL
SELECT 'ITEM02',NULL,NULL,'2','DAMAGE','box05' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box06' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box07' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box08' UNION ALL
SELECT 'ITEM03',NULL,NULL,'10','DAMAGE','box09' UNION ALL
SELECT 'ITEM04',NULL,NULL,'25','OK','box10'
-- Below Table is my expected result based on above data.
-- I need to show those data following way.
-- So I appreciate if you can give me an appropriate query for it.
-- Note: first row is blank and it is actually my table header. :)
SELECT ''as'ITEM', ''as'PO#', ''as'ExpectedQty',''as'ReceivedQty',
''as'DamageQty' ,''as'ShortQty' UNION ALL
SELECT 'ITEM01','PO-01','30','30','0' ,'0' UNION ALL
SELECT 'ITEM01','PO-02','20','15','5' ,'0' UNION ALL
SELECT 'ITEM02','PO-01','40','38','2' ,'0' UNION ALL
SELECT 'ITEM03','PO-01','50','50','0' ,'0' UNION ALL
SELECT 'ITEM03','PO-02','30','30','0' ,'0' UNION ALL
SELECT 'ITEM03','PO-03','20','10','10','0' UNION ALL
SELECT 'ITEM04','PO-01','30','25','0' ,'5'
推荐答案
问题的一部分是获取预期项目数量的运行总数.为此,您需要一种方法来区分具有相同物品的行,以及用于排放相同物品数量的顺序的规则.
One part of the problem is to get the running totals of expected item qunatities. For that you'd need a way to distinguish rows with same items from each other and a rule for the order of discharging same item quantities.
为了尝试解决您的问题,我将假设有一个时间戳列,其值提供排放顺序并且在同一项目组中是唯一的.
For the purpose of my attempt at solving your problem I'm going to assume there's a timestamp column whose values provide the order of discharge and are unique within same item groups.
这是我在测试解决方案时使用的示例数据定义:
Here's the sample data definition I was testing my solution on:
CREATE TABLE TableA (Item varchar(50), ExpectedQty int, Timestamp int);
INSERT INTO TableA
SELECT 'Item01', 30, 1 UNION ALL
SELECT 'Item01', 20, 2 UNION ALL
SELECT 'Item02', 40, 1 UNION ALL
SELECT 'item03', 50, 1 UNION ALL
SELECT 'item03', 30, 2 UNION ALL
SELECT 'item03', 20, 3;
CREATE TABLE TableB (Item varchar(50), ReceivedQty int);
INSERT INTO TableB
SELECT 'Item01', 45 UNION ALL
SELECT 'Item02', 38 UNION ALL
SELECT 'item03', 90;
这是我的解决方案:
SELECT
Item,
ExpectedQty,
ReceivedQty = CASE
WHEN RemainderQty >= 0 THEN ExpectedQty
WHEN RemainderQty < -ExpectedQty THEN 0
ELSE RemainderQty + ExpectedQty
END,
Short = CASE
WHEN RemainderQty >= 0 THEN 0
WHEN RemainderQty < -ExpectedQty THEN ExpectedQty
ELSE ABS(RemainderQty)
END
FROM (
SELECT
a.Item,
a.ExpectedQty,
RemainderQty = b.ReceivedQty - a.RunningTotalQty
FROM (
SELECT
a.Item,
a.Timestamp,
a.ExpectedQty,
RunningTotalQty = SUM(a2.ExpectedQty)
FROM TableA a
INNER JOIN TableA a AS a2 ON a.Item = a2.Item AND a.Timestamp >= a2.Timestamp
GROUP BY
a.Item,
a.Timestamp,
a.ExpectedQty
) a
INNER JOIN TableB b ON a.Item = b.Item
) s
这篇关于SQL 运行减法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!