提取待处理数量和待处理金额时出现问题 [英] Problem fetching pending quantity and pending amount
问题描述
大家好...我在交易表中有数据。表结构是
hi all... i am having data in transaction table. the table structure is
trans_Id, Order_Id, Item_No, Order_Qty,Supply_Qty,Trans_Type,amnt_Climed_Type,amnt_Climed_Prcntage
一般我们我们正在接收客户的订单，并根据我们提供材料的订单，在供应时间本身我们正在提高发票。发票上市条件基于订单条款和条件。条件是这样的。
1）总金额与总金额
2）部分数量与金额
3）总量与部分金额（如供应量的10％）
4）部分金额的偏好数量（如供应量的30％和供应量的50％）..
交易表包含这样的数据。
Generally we are getting orders from client and based on the orders we are supplying material and at the
time of supply itself we are raising invoice. The invoice climing conditions are based on the order terms and conditions. the conditions are like this.
1) total quantity with total amount
2) partial quantity with toal amount
3) total quantity with partial amount ( like 10% on supply)
4) partiall quantity with partial amount (like 30% of supply with 50% of amount on supply)..
the transaction table contains data like this.
--------------------------------------------------------------------------------------
trans_Id|Order_Id|Item_No|Order_Qty|Supply_Qty|Trans_Type|Climed_Type|Climed_Prcntage
--------------------------------------------------------------------------------------
1 ordxyz 1 500 NULL O NULL NULL
2 ordxyz 2 1000 NULL O NULL NULL
3 ordxyz 3 100 NULL O NULL NULL
4 ordxyz 4 700 NULL O NULL NULL
5 ordxyz 5 600 NULL O NULL NULL
6 ordxyz 1 NULL 500 I F 100
7 ordxyz 2 NULL 300 I F 100
8 ordxyz 2 NULL 700 I P 30
9 ordxyz 4 NULL 500 I F 100
10 ordxyz 5 NULL 200 I P 70
11 ordxyz 5 NULL 150 I P 40
12 ordxyz 5 NULL 200 I P 30
13 ordxyz 5 NULL 120 I F 100
----------------------------------------------------------------------------------------在上面的列中，Trans_Type表示---订单或供应（o-订单）（I-供应/发票）
Climed_Type-- - 全额保险或部分保险（F-FULL 100％CLIMED，P-Partial amount climed。）
In the above Columns Trans_Type means --- order or supply (o- for order) (I- for supply/Invoice)
Climed_Type---- full amount climed or partially climed ( F-FULL 100% CLIMED , P-Partial amount climed.)
--------------------------------------------------------------------------------------------------------------------
in the above data total we have 5 orders.. on that,
FOR item_No.1(row 1) full quantity supplyed(in row 6) and rised invoice 100% so item_No. 1 is completed.
FOR item_No.2(row 2) 300 quantity supplyed(in row 7) and rised invoice 100% so item_no.2 300 quantity completed. balance is 700 quantity
FOR item_No.2(row 2) 700 quantity supplyed(in row 8) and rised invoice 30% so item_no.2 700 quantity completed. but againg we have to clime same quantity for 70% (pending)
FOR item_No.3(row 3) 100 quantity not supplyed so item_no.3 (pending)
FOR item_No.4 (row 4) 500 Quantity supplyed (in row 9) and risied invoice for 100%. so 200 quantity is pending (pending)
FOR item_No.5 (row 5) we climed 200 quantity for 70% (in row 10) and again climed 200 quanty for 30% (in row 12) so this is also completed.
FOR item_No.5 (row 5) we climed 150 quantity for 40% (in row 11) and remaining 60% not climed (pending)
FOR item_No.5 (row 5) we climed 120 quantity for 100% and this is completed.
FOR item_No.5 (row 5) total 130 quantity has to clime for 100% and 150 quantity has to clime for 60% (pending)
now i want the output like only pending orders and pending amount orders for Invoice Generation. i.e
---------------------------------------------------------------------------------
Order_Id | Item_No | Order_Qty | pending_Supply_Qty | pending_Prcntage
-------------------------------------------------------------------------------------
ordxyz 2 1000 700 70
ordxyz 3 100 100 100
ordxyz 4 700 200 100
ordxyz 5 600 150 60
ordxyz 5 600 130 100
-------------------------------------------------------------------------------------
我尝试过：
What I have tried:
SELECT Item_No
,SUM(Climed_Prcntage) prct_clm
,MAX(Suply_Qty) qty
FROM T010_WO_Trans
WHERE (Order_Id = ordxyz) --AND (Item_No = 412)
AND Climed_Type = 'P'
AND Climed_Prcntage IS NOT NULL
GROUP BY Item_No,Suply_Qty
UNION
SELECT Item_No
,MAX(Climed_Prcntage) prct_clm
,SUM(Suply_Qty) qty
FROM T010_WO_Trans
WHERE (Order_Id = ordxyz) --AND (Item_No = 412)
AND Climed_Type = 'F'
AND Climed_Prcntage IS NOT NULL
GROUP BY Item_No;
请帮助我......提前致谢。
Please help me... Thanks in advance.
推荐答案
CREATE TABLE #orders(trans_Id int identity(1,1),Order_Id varchar(50),
Item_No int,Order_Qty int,Supply_Qty int,Trans_Type char(2),
Climed_Type char(2),Climed_Prcntage int
);
INSERT INTO #orders
VALUES
('ordxyz',1 ,500,NULL,'O',NULL ,NULL),
('ordxyz',2 ,1000,NULL,'O',NULL, NULL),
('ordxyz',3 ,100,NULL,'O',NULL ,NULL),
('ordxyz',4 ,700,NULL,'O',NULL ,NULL),
('ordxyz',5 ,600,NULL,'O', NULL ,NULL),
('ordxyz',1 ,NULL,500,'I','F',100 ),
('ordxyz',2 ,NULL,300,'I','F',100 ),
('ordxyz',2 ,NULL,700,'I','P',30 ),
('ordxyz',4 ,NULL,500,'I','F',100 ),
('ordxyz',5 ,NULL,200,'I','P',70 ),
('ordxyz',5 ,NULL,150,'I','P',40 ),
('ordxyz',5 ,NULL,200,'I','P',30 ),
('ordxyz',5 ,NULL,120,'I','F',100 );
;WITH CTE AS(
select Item_No
,Order_Id
,SUM(Supply_Qty) AS Supply_Qty
,(SELECT SUM(Order_Qty) FROM #orders) AS TotAmount
from #orders where Climed_Type IN('P','F') GROUP BY Item_No,Order_Id
)
SELECT ord.Order_Id,
ord.Item_No,
ord.Order_Qty,
CTE.Supply_Qty AS Supply_Qty,
(ord.Order_Qty-ISNULL(CTE.Supply_Qty,0)) AS Pendings,
CASE WHEN SIGN((ord.Order_Qty-ISNULL(CTE.Supply_Qty,0)))=0 THEN 'F'
WHEN SIGN((ord.Order_Qty-ISNULL(CTE.Supply_Qty,0)))=1 THEN 'P' ELSE 'OF'
END Climed_Type,
((ISNULL(CTE.Supply_Qty,0)*100.0)/(ord.Order_Qty))-100.0 AS Pendingpercent
FROM #orders ord LEFT join CTE
ON(cte.Item_No=ord.Item_No AND CTE.Order_Id=ord.Order_Id)
WHERE ORD.Trans_Type='O';
OUTPUT:-
---------------------------------------------------------------------------------------
Order_Id Item_No Order_Qty Supply_Qty Pendings Climed_Type Pendingpercent
--------------------------------------------------------------------------------------
ordxyz 1 500 500 0 F 0.000000000000
ordxyz 2 1000 1000 0 F 0.000000000000
ordxyz 3 100 NULL 100 P -100.000000000000
ordxyz 4 700 500 200 P -28.571428571429
ordxyz 5 600 670 -70 OF 11.666666666666
这篇关于提取待处理数量和待处理金额时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
