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