Showing records base on the years

[Benjamins via AccessMonster.com]

Hi,

I need some help in creating this query.

I have a table "Customer"
CustomerId CustomerName CustomerStartDate CustomerEndDate
====================================================
1 ; ABC ; 01/01/04 ; Null
2 ; DEF ; 01/01/04 ; 01/12/06

The customer had to pay an annual fee. Those end date which is null means
that the customer is working with the company, Those that have end date means
they have stop working with the company from that day on thus they will not
need to pay the annual fee the next year on.

After the query base on this year, the record will show
CustomerId CustomerName CustomerYear
=================================
1 ; ABC ; 2004
1 ; ABC ; 2005
1 ; ABC ; 2006
1 ; ABC ; 2007
1 ; ABC ; 2008
2 ; DEF ; 2004
2 ; DEF ; 2005
2 ; DEF ; 2006

How should i write the sql code?

 

[Allen Browne]

See:
Recurring events
at:
http://allenbrowne.com/AppRecur.html

The article contains a sample database that illustrates how to use a
cartesian product query to generate a record for each year.

It goes on to illustrate how to program exceptions as well (i.e. periods
where no fee is payable), but you probably don't need that, so you are done
when you get down to the 'Handling the exceptions' heading.