Click here to Skip to main content
15,936,349 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
select s.part_no,s.part_name,s.uom,i.Invoice_Quantity,r.Return_Quantity,p.Issue_Quantity,sr.Quantity,rp.Return_Quantity,er.Quantity,
((isnull(i.Invoice_Quantity,0)+ isnull(rp.Return_Quantity,0) + isnull(er.Quantity,0)) - (isnull(r.Return_Quantity,0) +isnull(p.Issue_Quantity,0) + isnull(sr.Quantity,0))) as balance
from dbo.dri_Stock as s 
left join 
     (select part_no,sum(Invoice_Quantity) as Invoice_Quantity from dbo.dri_InwardEntry where month(_date)=6 and year(_date)=2012
      group by part_no ) as i on s.part_no=i.part_no 
left join
     (select part_no,sum(Return_Quantity) as Return_Quantity from dbo.dri_ReturnToCustomer where month(date)=6 and year(date)=2012
      group by part_no)as  r on s.part_no=r.part_no 
left join 
     (select part_no,sum(Issue_Quantity) as Issue_Quantity from dbo.dri_IssueToProd where month(date)=6 and year(date)=2012
      group by part_no)as p on s.part_no=p.part_no 
left join 
     (select part_no,sum(Quantity) as Quantity from dbo.dri_ShortReceipt where month(date)=6 and year(date)=2012
      group by part_no)as sr on s.part_no=sr.part_no 
left join 
     (select part_no,sum(Return_Quantity) as Return_Quantity  from dbo.dri_ReceiptFromProd where month(date)=6 and year(date)=2012
      group by part_no)as  rp on s.part_no=rp.part_no 
left join 
     (select part_no,sum(Quantity) as Quantity from dbo.dri_ExcessReceipt where month(date)=6 and year(date)=2012
      group by part_no )as er on s.part_no=er.part_no 
where i.invoice_quantity>0


I want add two coluns in the output i.e Openingbalance and closingBalance
how can i generate these two columns values in my output
Thank you.
Posted
Comments
Herman<T>.Instance 6-Jul-12 10:44am    
How do you calculate these values?
hit91 7-Jul-12 0:25am    
I added a column named StockOnEntry in dbo.dri_InwardEntry table .when the user will insert the data in dbo.dri_InwardEntry table,at the time of this insertion in StockOnEntry column current stock balance will go.i want,Values of StockOnEntry column should shown for particular part_no,which last inserted in dbo.dri_InwardEntry table.last inserted because it will be the closingbalance.
For that i build a query :

select part_no,stock_OnEntryDate from dri_InwardEntry where entry_date in(select max(entry_date) from dri_InwardEntry group by part_no);

But i dont know where i should add this query in my query.

1 solution

If you have Openingbalance and closingBalance
value in the resultant than you can get that value by
adding the select query for the resultant

eg: SELECT Temp.Column1,Temp.Column2 FROM( your Sql Query ) AS Temp

Temp is the alias name for the resultant
Column1 is Openingbalance
Column2 is closingBalance
 
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