需要生成材料的库存摘要 [英] need to generate stock summary for materials

问题描述

我有三张桌子





1. OpeningStock

2. Material_Supplied

3. MaterialPurchased





桌子开放包含所有500个项目的期初余额。

For例子

 ItemID ItemName余额
 ------------------ -------------- 
 1 ABC 50 
 2 Def 100 
 3 ghi 32 
 4 jkl 500 
 5 mno 0 
 6 pqr 55 
 7 stu 0 
 8 vwx 0 

表material_supplied包含已发布的总数量。

例如

 ItemID数量
 ---------- --------- 
 2 60 
 4 220 
 6 10 

表材料_Purchased包含Total购买数量。

例如

 ItemID数量
 ------- ------------ 
 2 50 
 5 200 
 3 150 

现在我想创建股票总结报告，例如

 ItemID OpeningBalance Material_Supplied MaterialPurchased ClosingBalance 
 1 50 0 0 50 
 2 100 60 50 90 
 3 32 0 150 182 
 4 500 220 0 280 
 5 0 0 200 200 
 6 55 10 0 45 
 7 0 0 0 0 
 8 0 0 80 80 

i创建了查询但它没有显示所有项目，因为它已加入使用'内部联接'

 选择 o.itemid，o。 opensStock，m.IssuedMaterial，p.quantity，（o.openingStock  -  m.IssuedMaterial + p.quantity） as  ClosingBalance  from  openingStock o  inner   join  materialSupplied m  on  o.itemid = m.itemid 
 内部  join  materialPurchased p  on  o.itemid = s.itemid 
  order   by  o.itemid 

它只提供一项商品ID 2的记录



所以，请帮助我们......

提前预计.....

解决方案

使用下面的左连接

  SELECT  o.itemid，o.OpeningStock，m.materialSupplied，p.quantity ，（[o]。[openingStock] -isnull（[m]。[materialSupplied]， 0 ）+ isnull（[p]。[quantity]， 0 ）） AS  ClosingBalance 
  FROM （ openingStock o  LEFT   JOIN  materialSupplied m  ON  o .ItemID = m.ItemID） LEFT   JOIN  materialPurchased p  ON  o.ItemID = p.ItemID 
  ORDER   BY  o.itemid; 

I have folowwing three tables


1. OpeningStock
2. Material_Supplied
3. Material_Purchased


table openingstock contains Opening Balance of all 500 Items.
For example

ItemID      ItemName     Balance
--------------------------------
1           ABC           50
2           Def           100
3           ghi           32
4           jkl           500
5           mno           0
6           pqr           55
7           stu           0
8           vwx           0

table material_supplied contains Total quantity issued .
For example

ItemID     Quantity
-------------------
2           60
4           220
6           10           

table material_Purchased contains Total quantity Purchased.
For example

ItemID     Quantity
-------------------
2           50
5           200
3           150

Now I want to create Stock summary report like

ItemID  OpeningBalance   Material_Supplied   MaterialPurchased   ClosingBalance
1                50                0                0                      50
2                100               60               50                     90
3                32                0                150                    182
4                500               220              0                      280
5                0                 0                200                    200
6                55                10               0                      45  
7                0                 0                0                      0
8                0                 0                80                     80         

i had created query but it does not shows all items as it has been joined using 'inner join'

select o.itemid,o.openingStock,m.IssuedMaterial ,p.quantity,(o.openingStock - m.IssuedMaterial + p.quantity) as ClosingBalance from openingStock o inner join materialSupplied m on o.itemid=m.itemid
 inner join materialPurchased p on o.itemid=s.itemid
 order by o.itemid

it gives only one record of item id 2

so please help me guys...
thanx in advance.....

解决方案

use left join like below

SELECT o.itemid, o.OpeningStock, m.materialSupplied, p.quantity, ([o].[openingStock]-isnull([m].[materialSupplied],0)+isnull([p].[quantity],0)) AS ClosingBalance
FROM (openingStock o LEFT JOIN materialSupplied m ON o.ItemID = m.ItemID) LEFT JOIN materialPurchased  p ON o.ItemID = p.ItemID
ORDER BY o.itemid;

这篇关于需要生成材料的库存摘要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！