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


> The below query does not quite put the data in the order I am looking for. I want the dates to end up in an order such as:
>
> Start End
> 20120502 20120515
> 20120418 20120501
> 20120404 20120417
> 20120321 20120403
> 20120307 20120320
> 20120222 20120306
> 20120208 20120221
> 20120125 20120207
> 20120111 20120124
> 20111228 20120110
> 20111214 20111227
>
> However, the dates with years starting with 2011 are the first to be listed.
>
>
> SELECT DTOS(CTOD(ben_start)) AS Ben_Start, DTOS(CTOD(ben_end)) AS Ben_End ;
> 	FROM AAG_BILLING ;
> 	WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
> 	GROUP BY ben_start, ben_end ;
> 	ORDER BY ben_start DESC, ben_end ;
> 	INTO CURSOR tmpPeriods
> 
> * Eliminated any records not having the current year's value ("2012") in the BEN_START field.
> SELECT * FROM tmpPeriods WHERE LEFT(ben_start,4)=lcYear INTO CURSOR tmpPeriods2
> 
> 

>
> What I then did was to get the current year, and store that value into a variable, such as lcYear, and then only selected the records having the current year. This worked for me, but I wonder if there is a way to accomplish the same thing without having to have two separate statements?
>
>
>
> Cecil Champenois, Jr.

you need having clause i suppose.

GROUP BY ben_start, ben_end ;
HAVING LEFT(ben_start,4)=lcYear ;
ORDER BY ben_start DESC, ben_end ;

However i m not able to understand what you wana achieve with this query!. :)
Anil


Accounting Software

Web Development

Accounting, Inventory, Hotel Management, Restaurant Kot Billing, MLM, Excise Accounting and much much more!.
My Blog

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