Finding active customers each year

Question

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)<2015
    AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)

But how do I do this for all years available?

Answer 1

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

Answer 2

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.

edit:

CREATE TABLE #dates (
    [cn] [int] NOT NULL,
    [sal] [date] NOT NULL,
    [can] [date] NULL
)

insert into #dates
VALUES
(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

Answer 3

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

SELECT
customernumber,
'2015'
FROM
customers
WHERE
Datepart(year, salesdate)<2015
AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)
UNION
SELECT
customernumber,
'2016'
FROM
customers
WHERE
Datepart(year, salesdate)<2016
AND (Datepart(year, CancelDate)>2016 or Datepart(year, CancelDate) IS NULL)
etc...