Stock Balance in Sql Server

See more:

SQL-Server

synchronize

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