Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 To: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 Tags
Subject: RE: Opening + Closing Balance
Thread ID: 345287 Message ID: 345295 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, May 22, 2012 10:16:59 AM         
   


> > Dear Experts
> >
> > I have following data
> >
> >
> >
> > CREATE CURSOR stock(date d(8),open_1 n(4),open_2 n(4),arrival_1 n(4),arrival_2 n(4),sold_1 n(4),sold_2 n(4),closing_1 n(4),closing_2 n(4))
> > 
> > INSERT INTO stock value({^2012-07-01},0,0,0,0,0,0,15,30)
> > INSERT INTO stock value({^2012-09-09},0,0,10,0,0,0,0,0)
> > INSERT INTO stock value({^2012-09-10},0,0,10,0,0,0,0,0)
> > INSERT INTO stock value({^2012-09-11},0,0,0,15,0,0,0,0)
> > INSERT INTO stock value({^2012-09-21},0,0,0,0,30,60,0,0)
> > 

> >
> >
> > Please help
>
> Not well tested:
>
> CREATE CURSOR stock(date     d(8),;
>                     open1    n(4),;
>                     open2    n(4),;
>                     arrival1 n(4),;
>                     arrival2 n(4),;
>                     sold1    n(4),;
>                     sold2    n(4),;
>                     closing1 n(4),;
>                     closing2 n(4))
> 
> INSERT INTO stock VALUES ({^2012-07-01},0,0,0,0,0,0,15,30)
> INSERT INTO stock VALUES ({^2012-09-09},0,0,10,0,0,0,0,0)
> INSERT INTO stock VALUES ({^2012-09-10},0,0,10,0,0,0,0,0)
> INSERT INTO stock VALUES ({^2012-09-11},0,0,0,15,0,0,0,0)
> INSERT INTO stock VALUES ({^2012-09-21},0,0,0,0,30,60,0,0)
> 
> SELECT Stock.Date,;
>        SUM(IIF(Tbl1.Date == Stock.Date,000000, Tbl1.Closing1+Tbl1.Arrival1-Tbl1.Sold1))  AS Open1,;
>        SUM(IIF(Tbl1.Date == Stock.Date,000000, Tbl1.Closing2+Tbl1.Arrival2-Tbl1.Sold2))  AS Open2,;
>        MAX(Stock.Arrival1) AS Arrival1,;
>        MAX(Stock.Arrival2) AS Arrival2,;
>        MAX(Stock.Sold1)    AS Sold1,;
>        MAX(Stock.Sold2)    AS Sold2,;
>        SUM(Tbl1.Closing1+Tbl1.Arrival1-Tbl1.Sold1) AS Closing1,;
>        SUM(Tbl1.Closing2+Tbl1.Arrival2-Tbl1.Sold2) AS Closing2;
> FROM Stock;
> LEFT JOIN Stock Tbl1 ON Stock.Date >= Tbl1.Date;
> GROUP BY Stock.Date;
> ORDER BY Stock.Date
> 

>
>
> -----------------
> Borislav Borissov
>
> Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
> The only thing normal about database guys is their tables.

Dear Sir,

I do not want to use this line

GROUP BY Stock.Date;


If a date appears more than one time then it must appear more than one time.

Suppose I have following codes

INSERT INTO stock VALUES ({^2012-07-01},0,0,0,0,0,0,15,30)
INSERT INTO stock VALUES ({^2012-09-09},0,0,10,0,0,0,0,0)
INSERT INTO stock VALUES ({^2012-09-09},0,0,15,0,0,0,0,0)
INSERT INTO stock VALUES ({^2012-09-10},0,0,10,0,0,0,0,0)
INSERT INTO stock VALUES ({^2012-09-11},0,0,0,15,0,0,0,0)
INSERT INTO stock VALUES ({^2012-09-21},0,0,0,0,30,60,0,0)


Date ({^2012-09-09} is more than one time. So it must appear as it.
I do not want to Sum QTY in date.


Please help again

ENTIRE THREAD

Opening + Closing Balance Posted by Tariq mehmood @ 5/22/2012 9:08:15 AM
RE: Opening + Closing Balance Posted by Borislav Borissov @ 5/22/2012 10:06:04 AM
RE: Opening + Closing Balance Posted by Tariq mehmood @ 5/22/2012 10:16:59 AM
RE: Opening + Closing Balance Posted by tushar @ 5/22/2012 10:30:42 AM
RE: Opening + Closing Balance Posted by Tariq mehmood @ 5/22/2012 10:39:58 AM
RE: Opening + Closing Balance Posted by Borislav Borissov @ 5/22/2012 11:18:16 AM
RE: Opening + Closing Balance Posted by Tariq mehmood @ 5/22/2012 11:24:33 AM
RE: Opening + Closing Balance Posted by Borislav Borissov @ 5/22/2012 11:40:32 AM