显示每天库存中可用库存的余额 [英] Showing balance in available stocks in inventory for every day

问题描述

您好，

我遇到了一个问题，我希望显示每个日期的项目数量，数量和余额，并希望按日期查看数据，即如果项目很少那么在2013年1月1日（年/月/日）物品表（进出，平衡等）......，然后在02/01/13（年/月/日）物品表（ in，out，balance等）。

Hello,
I have come across a problem, i want to display number of items in, out and balance for every date and want to see the data datewise, i.e. if there are few items then on 01/01/13(dd/mm/yy) the table of items(in,out,balance,etc.) ..., then on 02/01/13(dd/mm/yy) the table of items(in,out,balance,etc).

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 give an insight on this?

推荐答案

作为rega如果O，P和I中存在记录，我无法看到为什么它不会返回值。



我建议打破查询和测试加入O和P.然后将结果与I结合并检查结果等等。



要包括所有日期，即使不在我中，您还需要添加另一个日期带有日期的表格，并加入到返回0作为结果。



希望这有帮助。

Hi,

As regards the inwards quantity I cannot see why it will not return values if a record exists in O,P and I.

What I suggest is to break down the query and test joining O and P. Then join the result with I and check your results and so on.

To include all dates even when not in I you need to add another table with dates and join to that returning 0 as your results.

Hope this helps.

这篇关于显示每天库存中可用库存的余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！