Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: DEREK DODOO
  Where is DEREK DODOO?
 READING, UK
 United Kingdom
 DEREK DODOO
 Tags
Subject: RE: range counter not working well
Thread ID: 143149 Message ID: 143152 # Views: 1 # Ratings: 1
Version: Visual FoxPro 6 Category: General VFP Topics
Date: Saturday, August 25, 2007 11:46:02 AM         
   


Because not only your upper boundary expression is wrong but missing 59 as well. Luckily this is not the way to do counts:)

Create Cursor AGE_FIVE (AGE_FGRP C(8),Name C(5))

Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('0-4','004')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('5-9','009')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('10-14','014')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('15-19','019')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('20-24','024')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('25-29','029')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('30-34','034')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('35-39','039')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('40-44','044')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('45-49','049')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('50-54','054')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('55-59','059')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('60-64','064')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('65-69','069')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('70-74','074')
Insert Into AGE_FIVE (AGE_FGRP,Name) Values ('75-79','079')

SELECT ;
 FLOOR((Year(Date())-Year(DATEBTH))/5) * 5 as ageLow, ;
 FLOOR((Year(Date())-Year(DATEBTH))/5) * 5 + 4 as ageHigh, ;
 CNT(*) as 'Numb' ;
 FROM vPayMast ;
 GROUP BY 1,2 ;
 INTO CURSOR ageCounts

SELECT af.AGE_FGRP,af.Name,NVL(age.Numb,0) as 'Numb' ;
 FROM AGE_FIVE af ;
 LEFT JOIN ageCounts age ;
 ON af.Name == PADL(age.ageHigh,3,'0') ;
 ORDER BY 2 ;
 INTO CURSOR age_five


PS: Age_five only helps to get results for all age ranges even if their count is 0 in vpaymast. Otherwise you don't need such an intermediate cursor. ageLow was not needed for this sample and it could be done in a single SQL using VFP9 or VFPOLEDB.
Cetin Basoz

ENTIRE THREAD

range counter not working well Posted by DEREK DODOO @ 8/25/2007 10:59:35 AM
RE: range counter not working well Posted by Cetin Basoz @ 8/25/2007 11:46:02 AM
RE: range counter not working well Posted by Cetin Basoz @ 8/25/2007 12:00:10 PM