> >&& 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-06},'1401','Coke 250ml',6,13.25,0) >insert into purchase values({^2009-07-03},'1402','Coke 500lm',1,32.75,0) >insert into purchase values({^2009-07-05},'1402','Coke 500lm',5,24.25,0) >insert into purchase values({^2009-07-07},'1402','Coke 500lm',9,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-05},'1402','Coke 500lm',1,36.00,0) >insert into sales values({^2009-07-08},'1402','Coke 500lm',2,28.25,0) >insert into sales values({^2009-07-10},'1402','Coke 500lm',4,35.50,0) >replace all amount with qty*rate > >>Sir, I need Closing Stock value as shown in image
&& 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-06},'1401','Coke 250ml',6,13.25,0) insert into purchase values({^2009-07-03},'1402','Coke 500lm',1,32.75,0) insert into purchase values({^2009-07-05},'1402','Coke 500lm',5,24.25,0) insert into purchase values({^2009-07-07},'1402','Coke 500lm',9,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-05},'1402','Coke 500lm',1,36.00,0) insert into sales values({^2009-07-08},'1402','Coke 500lm',2,28.25,0) insert into sales values({^2009-07-10},'1402','Coke 500lm',4,35.50,0) replace all amount with qty*rate SELECT O.Codes as [Code], O.Products, ; OpeningQty as [OpeningQty], ; PurchaseQty as [PurchaseQty], ; PurchaseAmount as [PurchaseAmount], ; OpeningQty + NVL(PurchaseQty,0) as [TotalQty], ; OpeningAmount + NVL(PurchaseAmount,0) as [TotalAmount], ; NVL(SoldQty,0) as [SoldQty], ; NVL(SoldAmount,0) as [SoldAmount], ; OpeningQty + NVL(PurchaseQty,0) - NVL(SoldQty,0) as [ClosingQty], ; OpeningAmount + NVL(PurchaseAmount,0) - NVL(SoldAmount,0) as [ClosingAmount] ; FROM (select Codes, Products, SUM(qty) as OpeningQty, ; SUM(Amount) as OpeningAmount from Open_Stock group by Codes, Products) O ; LEFT JOIN (select Codes, Products, ; SUM(qty) as PurchaseQty, SUM(Amount) as PurchaseAmount ; from Purchase group by Codes, Products) P ; ON O.Codes = P.Codes ; LEFT JOIN (select Codes, Products, ; SUM(qty) as SoldQty, SUM(Amount) as SoldAmount ; from Sales group by Codes, Products) S ; ON O.Codes = S.CodesUnfortunately, VFP doesn't allow spaces in field names (it seems). If you can have purchases without having an open stock for the item, we need to use FULL OUTER JOIN instead and modify this query a bit.