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?