count have condition in foxpro

Question

i have a table follow

id name date
1  a    08/09/2003
2  b    02/03/2003
3  c    10/08/2004
4  c    25/08/2007
5  a    01/01/2008

i want count database. with table below with result follow:

2003 = 2
2004 = 1
2007 = 0 because c has in 2004-year
2008 = 0 because a has in 2003-year

Answer 1

First, get the name and the earliest year it appears in:

select name, min(year(date)) as year from table
group by name into cursor temp

Then get the count from within that year:

select count(table.name) 
from table join temp on table.name = temp.name 
and year(table.date) = temp.year

Answer 2

I may be solving a different problem but this code gives the count in each year for names that have not appeared in previous years:

*-- Get the firstyear for each name
Select Name, Min(Year(Date)) As firstyear ;
  From table1;
  Group By Name Into Cursor temp1

*-- Get the year of the date for each entry
Select Id, Name, Year(Date) As yr From table1 Into Cursor temp2

*-- Identify those rows that appear for the first time
Select temp2.*, temp1.firstyear, Iif(temp2.yr = temp1.firstyear, 1, 0) As countme ;
  FROM temp2 INNER Join temp1 ;
  ON temp2.Name = temp1.Name Into Cursor temp3

*-- Add up the "CountMe" fields to get the sum.
Select yr, Sum(countme) From temp3 Group By yr

Answer 3

*/ First, get on a per name basis the first year they have a transaction */ for... in addition to the total transactions this person has regardless */ of the year.. ex: your two overlap of "a" and "c" persons

SELECT ;
        YT.Name,;
        MIN( YEAR( YT.DATE )) as FirstYear,;
        COUNT(*) as TotalPerName;
    FROM ;
        YourTable YT;
    GROUP BY ;
        1;
    INTO ;
        CURSOR C_ByNameTotals

*/ Now that you have totals based on the first year per person with their total */ entries regardless of the year, get the year and sum of totals that HAVE */ entries for a given year.... THEN UNION for all original year possibilities */ that were NOT IN the C_ByNameTotals result set. (hence your 2007 and 2008)

SELECT;
        FirstYear as FinalYear,;
        SUM( TotalPerName ) as YrCount;
    FROM ;
        C_ByNameTotals;
    GROUP BY ;
        1;
    INTO ;
        CURSOR C_FinalResults;
UNION;
SELECT DISTINCT;
        YEAR( Date ) as FinalYear,;
        0 as YrCount;
    FROM ;
        YourTable ;
    WHERE ;
        YEAR( Date ) NOT IN ;
            ( select FirstYear FROM C_ByNameTotals )