Problem fetching pending quantity and pending amount

See more:

SQL

SQL-Server

database

, +

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

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
----------------------------------------------------------------------------------------

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.