Click here to Skip to main content
15,885,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I don't want to see a stock of products that are empty.
 I can't figure out my mistake " where condition".
Thanks in advance for the solution.


What I have tried:

select a.Company_Name as Company, 
           a.Product_name as product,
          stock =ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0)
    from   (
            select Company_Name, Product_name, qty = sum(Qty)
            from   tbl_purchase
             group by Company_Name, Product_name
           ) a
           left join 
           (select Company_Name, Product_name,qty = sum(qty)
             from  tbl_sales
    		   group by Company_Name, Product_name
           ) b     on  a.Company_Name = b.Company_name
                 and a.Product_name = b.Product_name
   
	left join
	(
	select Company_Name,Product_Name,Qty=sum(Damage_Qty)
	from tbl_damage
	group by Company_Name, Product_name
	) c
	on a.Company_Name=c.Company_Name
	and a.Product_Name=c.Product_Name
	    
	<pre>	where stcok <> 0
Posted
Updated 15-Aug-21 23:53pm

Regardless of the spelling error, the field 'stock' doesn't exist yet when where stcok <> 0 is evaluated.
Try
SQL
WHERE ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0) <> 0
instead
 
Share this answer
 
Comments
jewel serniabad 16-Aug-21 10:47am    
Thanks
SQL
stock =ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0)
	    
...

where stcok <> 0 /* You have a spelling error here. */
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900