.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Need a SQL Query using CTE concept for calculating stock

Posted By: lakshmi     Posted Date: October 15, 2011    Points:5   Category :Sql Server
Hi friends,

Am having two tables : table1 and table2

table1 columns : itemcode and stock
table2 columns : date,itemcode,refNo,qty,price

Table 1 values :

item1 50
item2 35
item3 35

Table2 values :

2011-06-28 | item1 | 1 | 15 | 40.50
2011-06-28 | item1 | 10 | 25 | 100
2011-06-27 | item1 | 5 | 78 | 50
2011-05-10 | item1 | 10 | 60 | 85
2011-05-16 | item2 | 12 | 20 | 70
2011-04-15 | item2 | 10 | 10 | 40
2011-05-15 | item2 | 15 | 35 | 35
2011-03-12 | item3 | 25 | 10 | 65
2011-04-12 | item3 | 5 | 20 | 46
2011-04-12 | item3 | 2 | 10 | 30

-- The condition is , need to calculate the price of the item in LIFO basis. ie. date and refNo in descending order.

Ex : for item1 , we should consider the date and refno in desc order and then calculate the stock against its qty and price...


item1 - 50
date|itemcode |refNo |qty | price
2011-06-28 | item1 | 10 | 25 | 100
2011-06-28 | item1 | 1 | 15 | 40.50
2011-06-27 | item1 | 5 | 78 | 50

--- second table arranged in desc order with date and refNo
-- calculating its price as follows :
-- (25 *100) + (15*40.50) + (10*50) =3607.5
So, item1 stock - 50 -- its value should be 3607.5

Likewise i need to calculate for all items which exists in table1 with regards to its value in table2.

Can anybody guide me with a query....
i don't want to use loop...but something new feature such as CTE..

Thanks.


Responses

No response found. Be the first to respond this post
Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend