Click here to Skip to main content
15,917,652 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
2 views are giving inventory
Inventorymgmt
and
Inventorymgmtout
	ALTER view [dbo].[Inventorymgmt] AS 
SELECT    
distinct (T0.ItemName),sum(T0.Qty)as qty
FROM [dbo].[MaterialIO] as T0

	group by T0.ItemName

select * from [dbo].[Inventorymgmt]

ItemName	qty
ACID	30
BRASS	50

select * from [dbo].[Inventorymgmtout]

ItemName	qty
ACID	5
BRASS	20


now I need balance quantity from 30-5 =25


how to incorporate in 1 view for above result.

What I have tried:

I have tried to group by but its notworking
Posted
Updated 3-Feb-19 6:47am

1 solution

At the first look, if both views return distinct items (values), you can achieve that this way:
SQL
SELECT m.ItemName, m.qty AS mgmt, mo.qty AS mgmtout, m.qty - mo.qty AS total
FROM [dbo].[Inventorymgmt] AS m INNER JOIN [dbo].[Inventorymgmtout] AS mo ON m.ItemName = mo.ItemName;
 
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