Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 To: Anil Sharma
  Where is Anil Sharma?
 ludhiana
 India
 Anil Sharma
 Tags
Subject: RE: Putting dates in a certain order
Thread ID: 345035 Message ID: 345049 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, May 17, 2012 3:51:22 PM         
   


> >
> > When I used the following statement, which uses the field as a CHARACTER data type formatted as mm/dd/yyyy, I still do not get what I desire which is to have the latest year's data show up at the top.
> >
> > SELECT ben_start, ben_end ;
> >  	FROM AAG_BILLING ;
> >  	WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
> >  	GROUP BY 1, 2 ;
> >  	ORDER BY 1 DESC, 2 ;
> >  	INTO CURSOR tmpPeriods
> > 

> > What I get with the above statement is the following,
> > 12/28/2011 01/10/2012
> > 12/14/2011 12/27/2011
> > 11/30/2011 12/13/2011
> > 11/16/2011 11/29/2011
> > 11/02/2011 11/15/2011
> > 05/02/2012 05/15/2012
> > 04/18/2012 05/01/2012
> > 04/04/2012 04/17/2012
> > and so on,...
> > So, the rows with the current year do not come out on top as I desire.
> >
> >
> > Cecil Champenois, Jr.
>
>
> Cecil may be this works 4 u.
>
> SELECT MAX(ben_start) AS BEN_START, Max(ben_end) AS BEN_END ;
> FROM AAG_BILLING ;
> WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
> GROUP BY 1, 2 ;
> ORDER BY 1 DESC, 2 ;
> INTO CURSOR tmpPeriods
>
>
> Anil
>
>
> Accounting Software
>
> Web Development
>
> Accounting, Inventory, Hotel Management, Restaurant Kot Billing, MLM, Excise Accounting and much much more!.
> My Blog

Doing as you suggested gives me an error that states, "Cannot group by aggregate field".

And, when I remove the GROUP BY 1, 2 from the statement, I end up with the following result, which is in the year 2011:

Ben_Start Ben_End
12/28/2011 12/27/2011

The above is a problem for two reasons, one of which I can eliminate if the ending date is less than the starting date. And, perhaps I can use the HAVING clause to eliminate anything but the year, "2012". Back to the drawing board. I thank you for your suggestions. Perhaps I can tweak them a bit and get something to work properly.

Cecil Champenois, Jr.

ENTIRE THREAD

Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 2:40:29 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 2:52:24 PM
RE: Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 3:03:34 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 3:24:43 PM
RE: Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 3:51:22 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 8:06:37 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 3:32:13 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 3:02:49 PM
RE: Putting dates in a certain order Posted by Anders Altberg @ 5/17/2012 3:04:08 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 3:34:23 PM
RE: Putting dates in a certain order Posted by Tamar Granor @ 5/17/2012 10:23:55 PM
RE: Putting dates in a certain order Posted by Anders Altberg @ 5/18/2012 12:53:05 AM