Click here to Skip to main content
15,942,934 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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).

SQL
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?
Posted
Updated 2-Apr-13 22:07pm
v2

1 solution

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.
 
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