sql server 2005 query

Question

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 6-Jul-12 4:41am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bopannavb · Answer 1 · 2012-07-06T05:04:00

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