Sql Server中的库存余额 [英] Stock Balance in Sql Server
问题描述
我想从4个表中检索数据,以显示每个日期的项目余额。我的表结构如下:
表:Resource_Master
ResourceCode主键,ResourceName,UnitCode,TenderCode,ResourceRate,Status,Dt,Resource_H_Code
表:UnitMaster
UnitCode主键,
UnitName,
UnitSymbol ,
UserId,
日期时间,
状态
表:Outwards_Master
ChallanNo主键,
日期,
Vehicleno,
Nameofitem,
单位,
数量,
价格,
旁白,
Dt,
招标代码,
Subcont
表:Outwards_Master
ChallanNo主键,
日期,
Vehicleno,
Nameofitem,
单位,
数量,
汇率,
旁白,
Dt,
Tendercode,
Subcont
我希望将输出显示为
日期ItemName Rate InQty OutQty Balance ItemUnit
2013-01-04 00:00:00.000 Sand 0.000 10 5 5 NONE
2013-01-04 00:00:00.000反铲装载机650.000 20 2 18 Hr
2013-01-04 00:00:00.000 Tractor Troley 150.000 10 0 10 Hr
2013-02- 04 00:00:00.000 Sand 0.000 0 5 0 NONE
2013-02-04 00:00:00.000反铲装载机650.000 5 2 21 Hr
2013-02-04 00:00:00.000 Tractor Troley 150.000 10 30 -10 Hr
我为此目的编写了以下查询但没有得到所需的输出
SELECT I. 日期 as 日期,
P.ResourceName as ItemName,
P.ResourceRate as Rate,
COALESCE (i.TotalInQty, 0 ) as InwardsQuantity,
COALESCE (s.TotalOutQty, 0 ) as OutwardsQuantity,
COALESCE (i.TotalInQty, 0 ) - COALESCE(S.TotalOutQty, 0 ) as 余额,
O.UnitSymbol 作为 ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
ON O.UnitCode = P.Unitcode
LEFT JOIN ( select sum(i.Qty) AS TotalInQty,Nameofitem,Min(I。日期) AS 日期 来自 Inwards_Master我其中 Tendercode = 1 group by Nameofitem)I ON I.Nameofitem = P.ResourceName
LEFT JOIN (选择 sum(s.qty) AS TotalOutQty,Nameofitem,Min(S Date ) AS 日期 来自 Outwards_Master S 其中 Tende rcode = 1 group by s.Nameofitem)S ON I.Date = S. 日期 AND I.Nameofitem = S.Nameofitem
WHERE P.Status = 1 和 P.TenderCode = 1
任何人都可以帮我解决这个问题吗?在预先感谢
您可以按明日和项目明智分组In,out $
请参阅:
选择ItemCode,ItemName,TransactionDate,Sum(In)作为INQty,Sum(Out)作为OutQty,Sum(In)-Sum(Out)作为余额来自
StockTable Group by ItemCode ,ITEMNAME,TransactionDate
I want to retrieve data from 4 Tables to show balance of items for every date. My tables structure are as:
TABLE: Resource_Master
ResourceCode primary key, ResourceName, UnitCode, TenderCode, ResourceRate, Status, Dt, Resource_H_Code
TABLE: UnitMaster
UnitCode primary key,
UnitName,
UnitSymbol,
UserId,
Datetime,
Status
TABLE: Outwards_Master
ChallanNo primary key,
Date,
Vehicleno,
Nameofitem,
unit,
Qty,
Rate,
Narration,
Dt,
Tendercode,
Subcont
TABLE: Outwards_Master
ChallanNo primary key,
Date,
Vehicleno,
Nameofitem,
unit,
Qty,
Rate,
Narration,
Dt,
Tendercode,
Subcont
I want show the output as
Date ItemName Rate InQty OutQty Balance ItemUnit
2013-01-04 00:00:00.000 Sand 0.000 10 5 5 NONE
2013-01-04 00:00:00.000 Backhoe Loader 650.000 20 2 18 Hr
2013-01-04 00:00:00.000 Tractor Troley 150.000 10 0 10 Hr
2013-02-04 00:00:00.000 Sand 0.000 0 5 0 NONE
2013-02-04 00:00:00.000 Backhoe Loader 650.000 5 2 21 Hr
2013-02-04 00:00:00.000 Tractor Troley 150.000 10 30 -10 Hr
I have written the following query for the purpose but did not get the desired output
SELECT I.Date as Date,
P.ResourceName as ItemName,
P.ResourceRate as Rate,
COALESCE(i.TotalInQty,0) as InwardsQuantity,
COALESCE(s.TotalOutQty,0) as OutwardsQuantity,
COALESCE(i.TotalInQty,0)-COALESCE(S.TotalOutQty,0) as Balance,
O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
ON O.UnitCode = P.Unitcode
LEFT JOIN(select sum(i.Qty) AS TotalInQty,Nameofitem,Min(I.Date) AS Date from Inwards_Master I where Tendercode=1 group by Nameofitem) I ON I.Nameofitem= P.ResourceName
LEFT JOIN(select sum(s.qty) AS TotalOutQty,Nameofitem, Min(S.Date) AS Date from Outwards_Master S where Tendercode=1 group by s.Nameofitem) S ON I.Date=S.Date AND I.Nameofitem =S.Nameofitem
WHERE P.Status=1 and P.TenderCode= 1
Can any body help me with this query? Thanks in Advance
You can Group the sum of In,out by Date wise and item Wise
plz see this :
Select ItemCode,ItemName,TransactionDate, Sum(In) as INQty, Sum(Out) as OutQty, Sum(In)-Sum(Out) as Balance from
StockTable Group by ItemCode,ItemName,TransactionDate
这篇关于Sql Server中的库存余额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!