Click here to Skip to main content
14,665,609 members
Rate this:
Please Sign up or sign in to vote.
See more: , +
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.
Posted
Updated 23-Aug-18 20:36pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100