Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 Tags
Subject: RE: Closing Stock Value
Thread ID: 267600 Message ID: 268940 # Views: 62 # Ratings: 2
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, July 15, 2010 2:54:21 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

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