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. – Steve Mangiameli Jan 25 '17 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 '17 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 '17 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 '17 at 15:42
  • Which DBMS are you using? – a_horse_with_no_name Jan 25 '17 at 20:51

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 '17 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, privacy policy and cookie policy

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