Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Vivek Deodhar
  Where is Vivek Deodhar?
 SANGLI
 India
 Vivek Deodhar
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Closing Stock Value
Thread ID: 267600 Message ID: 268544 # Views: 54 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, July 13, 2010 7:50:47 AM         
   


> >
> > && opening stock table
> > create  cursor open_stock (dates d(8),codes c(4),products c(15),qty n(3),rate n(6,2),amount n(6,2))
> > insert into open_stock values({^2009-07-01},'1401','Coke 250ml',2,14.45,0)
> > insert into open_stock values({^2009-07-01},'1402','Coke 500lm',3,28.90,0)
> > replace all amount with qty*rate
> > 
> > && purchase table
> > create  cursor purchase (dates d(8),codes c(4),products c(15),qty n(3),rate n(6,2),amount n(6,2))
> > insert into purchase values({^2009-07-02},'1401','Coke 250ml',2,15.30,0)
> > insert into purchase values({^2009-07-04},'1401','Coke 250ml',4,16.80,0)
> > insert into purchase values({^2009-07-03},'1401','Coke 250ml',6,13.25,0)
> > insert into purchase values({^2009-07-03},'1402','Coke 500lm',2,32.75,0)
> > insert into purchase values({^2009-07-05},'1402','Coke 500lm',2,24.25,0)
> > insert into purchase values({^2009-07-07},'1402','Coke 500lm',1,33.00,0)
> > replace all amount with qty*rate
> > 
> > && sale table
> > create  cursor sales (dates d(8),codes c(4),products c(15),qty n(3),rate n(6,2),amount n(6,2))
> > insert into sales values({^2009-07-03},'1401','Coke 250ml',1,18.25,0)
> > insert into sales values({^2009-07-05},'1401','Coke 250ml',4,19.50,0)
> > insert into sales values({^2009-07-07},'1401','Coke 250ml',3,16.75,0)
> > insert into sales values({^2009-07-07},'1402','Coke 500lm',1,36.00,0)
> > insert into sales values({^2009-07-08},'1402','Coke 500lm',1,28.25,0)
> > insert into sales values({^2009-07-10},'1402','Coke 500lm',2,35.50,0)
> > replace all amount with qty*rate
> > 

> > && Closing Stock
> >
> > Sir I need results as in image
> > closing stock value is specailly required
> >
> >
> >
> >
>
>
>
Select opening.codes,opening.products,;
> 	openingQty, openingAmount, ;
> 	purchaseQty, purchaseAmount, ;
> 	CAST(openingQty+purchaseQty As Int) As totalQty, ;
> 	CAST(openingAmount + purchaseAmount As Y) As totalAmount, ;
> 	soldQty, soldAmount, ;
> 	CAST(openingQty + purchaseQty - soldQty As Int) As closingQty, ;
> 	CAST(openingAmount + purchaseAmount - soldAmount As Y) As closingAmount ;
> 	FROM ;
> 	(Select codes, products, ;
> 	sum(qty) As openingQty, Sum(amount) As openingAmount ;
> 	FROM open_stock Group By 1,2) opening ;
> 	LEFT Join ;
> 	(Select codes, Sum(qty) As purchaseQty, Sum(amount) As purchaseAmount ;
> 	FROM purchase Group By 1) purchase On opening.codes = purchase.codes ;
> 	LEFT Join ;
> 	(Select codes, Sum(qty) As soldQty, Sum(amount) As soldAmount ;
> 	FROM sales Group By 1) sales On opening.codes = sales.codes

>
> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> Blog (main)
> Blog (mirror)

Hi Cetin,

In this query, will it give correct results if there is purchase of an item which is not present in opening stock?

Vivek

Vivek

ENTIRE THREAD

Closing Stock Value Posted by Tariq mehmood @ 7/5/2010 8:41:07 PM
RE: Closing Stock Value Posted by Anders Altberg @ 7/6/2010 10:42:00 AM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/6/2010 6:30:03 PM
RE: Closing Stock Value Posted by kulwant singh @ 7/6/2010 5:25:15 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/6/2010 6:33:58 PM
RE: Closing Stock Value Posted by Anders Altberg @ 7/7/2010 4:37:23 PM
RE: Closing Stock Value Posted by kulwant singh @ 7/7/2010 5:28:33 PM
RE: Closing Stock Value Posted by anand kulkarni @ 7/6/2010 5:45:49 PM
RE: Closing Stock Value Posted by Cetin Basoz @ 7/7/2010 1:49:30 PM
RE: Closing Stock Value Posted by Anil Sharma @ 7/7/2010 3:40:29 PM
RE: Closing Stock Value Posted by Cetin Basoz @ 7/7/2010 4:06:36 PM
RE: Closing Stock Value Posted by Anil Sharma @ 7/7/2010 4:32:13 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/8/2010 7:04:08 AM
RE: Closing Stock Value Posted by Cetin Basoz @ 7/8/2010 12:26:03 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/8/2010 5:26:18 PM
RE: Closing Stock Value Posted by kulwant singh @ 7/8/2010 12:31:55 PM
RE: Closing Stock Value Posted by kulwant singh @ 7/7/2010 5:01:03 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/8/2010 5:29:48 PM
RE: Closing Stock Value Posted by Vivek Deodhar @ 7/13/2010 7:50:47 AM
RE: Closing Stock Value Posted by Cetin Basoz @ 7/13/2010 1:04:29 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/14/2010 8:37:08 AM
RE: Closing Stock Value Posted by alwy ali @ 7/8/2010 6:17:58 PM
RE: Closing Stock Value Posted by Anil Sharma @ 7/8/2010 8:35:14 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/8/2010 10:14:54 PM
RE: Closing Stock Value Posted by tushar @ 7/11/2010 7:55:52 AM
RE: Closing Stock Value Posted by Tariq mehmood @ 7/12/2010 5:39:46 PM
RE: Closing Stock Value Posted by Tariq mehmood @ 12/2/2010 5:47:07 PM
RE: Closing Stock Value Posted by tushar @ 12/4/2010 8:27:12 AM
RE: Closing Stock Value Posted by Anders Altberg @ 7/15/2010 2:54:21 PM
RE: Closing Stock Value Posted by Mohammad Iqbal @ 7/15/2010 7:15:20 PM
RE: Closing Stock Value Posted by Anders Altberg @ 7/15/2010 7:26:49 PM