Finding active customers each year I have a database with the following:

|Customer number| |Sales date| |Cancel date|


From this I want to count the number of active customers each year for lets say 10 years back.
If I want to do this for a single year I can do this (I think):

SELECT
    customernumber
FROM
    customers
WHERE
    Datepart(year, salesdate)&lt;2015
    AND (Datepart(year, CancelDate)&gt;2015 or Datepart(year, CancelDate) IS NULL)


But how do I do this for all years available?
You need a tally table or calendar table that includes all years (that you want to report on).  Call the table Allyears or something like that.  As a field, you could put something like yearstart datetime and populate it with January 1 of each year (consider yearend as well).  Then simply join the Allyears table to TestDates and display the years you want.  Note, you have to decide if they were a customer on January 1, do you count them for the year?  Or did they have to be a customer on Dec 31st to be counted.  That will determine how you should create the table and join it.

Apparently, I'm the only one who has trouble posting Sql code, the site or my company's security doesn't allow the edits when they contain code (sometimes).