Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Mohammad Iqbal
  Where is Mohammad Iqbal?
 Lahore
 Pakistan
 Mohammad Iqbal
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Closing Stock Value
Thread ID: 267600 Message ID: 268979 # Views: 50 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, July 15, 2010 7:15:20 PM         
   


> You can use SQL to do calculate the aggregated value of each item in the remaining stock.
> This code works on the same principle as Anil's and Tushar's codes in that it sums up value of the remaining items in reverse date order, and ignores the items that in principle have been ticked off as sold if you do that in FIFO order.
>
>
> && 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
> GO TOP IN Open_stock
> BROWSE LAST NOWAIT 
> && 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
> GO TOP IN Purchase
> BROWSE LAST NOWAIT 
> && 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
> GO TOP IN Sales
> BROWSE LAST NOWAIT 
> 

>
> * Purchases + Open_stock
>  SELECT codes, qty, rate,dates FROM Open_stock ;
> UNION ALL SELECT codes, qty, rate, dates FROM Purchase ;
> INTO CURSOR Q1
> browse  LAST NOWAIT 
> 
> 
> SELECT COUNT(Q1.dates) AS cnt,Q1.codes, Q1.dates, ; 
> MAX(Q1.qty)  as qty, MAX(Q1.rate) as rate, SUM(Q2.qty)  ;
> FROM Q1 JOIN Q1 AS Q2 ON Q1.codes=Q2.codes ;
> AND Q1.dates<=Q2.dates ;
> GROUP BY  2,3 ;
> ORDER BY 2, 3 DESC ;
> INTO CURSOR Q2   
> BROWSE LAST NOWAIT 
> 
> * remaining stock
> SELECT P1.codes, SUM(P1.qty)  AS sum_qty ;
> FROM (SELECT codes, qty FROM Q1 ;
> UNION ALL SELECT codes, -qty FROM Sales) AS P1 ;
> GROUP BY P1.codes ;
> INTO CURSOR Q3  
> BROWSE LAST NOWAIT 
> 
> SELECT Q2.codes,;
> MAX(Q3.sum_qty) AS stock, ;
>  SUM(Q2.rate*Q2.qty) + MAX(Q4.rate)*(MAX(Q3.sum_qty)-MAX(Q2.sum_qty)) As sum_stock;
> FROM Q2 As Q2 ;
>  JOIN Q2 AS Q4 ON Q2.codes=Q4.codes AND Q2.cnt+1=Q4.cnt ;
> JOIN Q3 ON Q2.codes=Q3.codes AND Q2.sum_qty <= Q3.sum_qty ;
> GROUP BY Q2.codes ;
> ORDER BY Q2.codes ;
> INTO CURSOR Q5
> 

>
> Result:

> Codes Stock Sum_stock
> 1401 6 93,70
> 1402 4 114,25
>

>
> One could add a lot of columns to the last query as you picture shows.
>
> -Anders

I try to add a column in your last query as as

the first line was

SELECT Q2.codes,Q1.QTY AS open_qty,;


I replaced it as

SELECT Q2.codes,Q1.QTY AS open_qty,;
MAX(Q3.sum_qty) AS stock, ;
 SUM(Q2.rate*Q2.qty) + MAX(Q4.rate)*(MAX(Q3.sum_qty)-MAX(Q2.sum_qty)) As sum_stock;
FROM Q2 As Q2 ;
 JOIN Q2 AS Q4 ON Q2.codes=Q4.codes AND Q2.cnt+1=Q4.cnt ;
JOIN Q3 ON Q2.codes=Q3.codes AND Q2.sum_qty <= Q3.sum_qty ;
GROUP BY Q2.codes ;
ORDER BY Q2.codes ;
INTO CURSOR Q5


But it displays wrong data as
open_qty
2
2

instead of

2
3

what is wrong?
How to add more columns as shown in attached picture?

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