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

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 help me with this query? Thanks in Advance
Posted
Updated 8-Apr-13 1:12am
v3
Comments
Maciej Los 8-Apr-13 2:10am    
Post the structure of your database and example data.
kunal jangade 8-Apr-13 7:03am    
i am improving my ques

1 solution

You can Group the sum of In,out by Date wise and item Wise
plz see this :
Select ItemCode,ItemName,TransactionDate, Sum(In) as INQty, Sum(Out) as OutQty, Sum(In)-Sum(Out) as Balance from
StockTable Group by ItemCode,ItemName,TransactionDate
 
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