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):

    Datepart(year, salesdate)<2015
    AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)

But how do I do this for all years available?

  • Group By will let you aggregate counts on the year. You'll just need to parse your dates accordingly. Jan 25, 2017 at 15:31
  • So a customer ordering on 2015-12-30 and cancelling on 2016-01-01 should count as "active in 2015"?
    – til_b
    Jan 25, 2017 at 15:37
  • Yes, that is fine. Or else I can add a second where clause that there should be x number of days between the order and cancel date.
    – B.Doe12
    Jan 25, 2017 at 15:41
  • 1
    or Datepart(year, CancelDate) = NULL. Do not ever compare NULL to something using =. You have to use IS NULL or IS NOT NULL
    – Philᵀᴹ
    Jan 25, 2017 at 15:42
  • Which DBMS are you using?
    – user1822
    Jan 25, 2017 at 20:51

3 Answers 3


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).


You can do (TSQL for MS SQL Server, other DBMS syntax may vary):

select YEAR(salesdate),count(*) as c
from test_dates
where canceldate is null
group by YEAR(salesdate)

This will group by the year of the salesdate and ignore those where canceldate is set. This will only give customers grouped by year of first sale, so to get the active customers in all years from 2011 to 2016 here is a different solution. It creates a "years of interest" table and joins this to the dates/customers table using a join condition of "BETWEEN ... and ...", which is TSQL shorthand for a<=b<=c.


    [cn] [int] NOT NULL,
    [sal] [date] NOT NULL,
    [can] [date] NULL

insert into #dates
(1,try_parse('2016-01-01' as date),NULL),
(2,try_parse('2016-01-05' as date),NULL),
(3,try_parse('2015-02-02' as date),try_parse('2015-02-05' as date)),
(4,try_parse('2011-04-09' as date),NULL),
(5,try_parse('2013-01-10' as date),try_parse('2015-01-10' as date))

create table #years (y int NOT NULL)

insert into #years VALUES (2011),(2012),(2013),(2014),(2015),(2016)

select d.*, #years.y
into #cust_all_years
from #dates as d
inner join #years on #years.y BETWEEN YEAR(d.sal) and ISNULL(YEAR(d.can),9999)

The ISNULL corrects for the cases where the cancel date is not set.

select y,count(*) as c from #cust_all_years
group by y
  • Thank you. But this will just count all the customers that is currently a customer. I someone canceled in 2015, but became a customer in 2013, they still should be counted as a customer in 2014.
    – B.Doe12
    Jan 25, 2017 at 15:37

Thanks for all your answers. This is probably not the best solution, but this solutions does not require me to make a new table:

Datepart(year, salesdate)<2015
AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)
Datepart(year, salesdate)<2016
AND (Datepart(year, CancelDate)>2016 or Datepart(year, CancelDate) IS NULL)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.