Click here to Skip to main content
14,664,481 members
Rate this:
Please Sign up or sign in to vote.
Hi,
I have the below table values from the SQL group by Query.
I need to find out the Available Quantity.

For Example,
Total Qty = 10 for the 6715 (Groupby) Identification
Total Sum Qty = 2 + 2 +2 = 6 for the 6715 (Groupby) Identification.
I want the result  Total Qty – TotalSumQty  10 – 6 = 4 (Available Qty).

Just I want to get the Available Qty. Please Help.
See my Query:
select 
		unit,		
		Number,
		L.identification as identification,
		RequiredQty,
		sum(RequiredQty) as TotalSumQty,
		I.TotalQty,
		(I.TotalQty – I.RequiredQty) as AvailableQty,
		convert(varchar,AvailabilityDate,101) as AvailabilityDate
	from 
		tbl1 L
  INNER JOIN 
		Tbl2 I
	on 
		L. identification = I. identification
	WHERE gi.ident='6715'
	group by Number, unit,L. identification,requiredQty,I.TotalQty,AvailabilityDate



unit	Number	Identification	RequiredQty	TotalSumQty	TotalQty	AvailableQty
1-GD-B-08225	AB-00009-S10B-1	6715	1	2	10	?
2-GD-B-08275	AB-00009-S10B-1	6715	1	2		?
3-GD-B-08324	AB-00009-S10B-1	6715	1	2		?
Posted
Updated 6-Sep-11 23:09pm
v2

1 solution

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

Solution 1

Try using

sum(I.TotalQty – I.RequiredQty) as AvailableQty,


instead of

(I.TotalQty – I.RequiredQty) as AvailableQty,


if this does not solve your issue then please post the table structure of tbl1 & tbl2.
   
v2

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