title: How to identify changes to a list of customers over time
body: <p>I have a cohort of customers that changes fairly regularly. Each time the cohort changes, a complete dump of the new customers is appended to the table and given a new List_Name. Alpha, Beta, Gamma, etc. Each list generally add some customers, removes some customers, and keeps some customers. I'm trying to create a simple waterfall chart to show the changes from list to list. Typically I would just FULL OUTER JOIN Alpha&lt;->Beta, then FULL OUTER JOIN Beta&lt;->Gamma, etc etc to get the customers that are staying, leaving, and being added. But the List_Versions are growing quite a bit and I am wondering if there is an easier way to do this calc instead of having to edit it every time a new list is appended. I am not able to change this process since it supports legacy systems and is controlled by another department. Thoughts?</p>
  
  <p>EDIT: Sql Server 2016 SP2.</p>
  
  <pre><code>CREATE TABLE #customers(cust_id int, list_name varchar(10), create_dt date)
  INSERT INTO #customers values (1,'Alpha','2019-01-01')
      ,(2,'Alpha','2019-01-01')
      ,(3,'Alpha','2019-01-01')
      ,(4,'Alpha','2019-01-01')
      ,(5,'Alpha','2019-01-01')
      ,(2,'Beta','2019-03-01')
      ,(3,'Beta','2019-03-01')
      ,(4,'Beta','2019-03-01')
      ,(5,'Beta','2019-03-01')
      ,(6,'Beta','2019-03-01')
      ,(7,'Beta','2019-03-01')
      ,(1,'Gamma','2019-05-05')
      ,(6,'Gamma','2019-05-05')
      ,(7,'Gamma','2019-05-05')
      ,(9,'Gamma','2019-05-05')
  
  --Desired Output (long way that needs to be edited every time there is a new list)
  SELECT List_Name, 'Starting' Descrip, count(*) Custs FROM #customers WHERE list_name = 'alpha' group by list_name
  UNION ALL
  SELECT List_Name, 'Add', count(*) FROM #customers a WHERE list_name = 'Beta' AND not exists(SELECT * FROM #customers x WHERE List_Name = 'Alpha' AND a.cust_id = x.cust_id) GROUP BY List_Name
  UNION ALL
  SELECT 'Beta', 'Remove', -count(*) FROM #customers a WHERE list_name = 'Alpha' AND not exists(SELECT * FROM #customers x WHERE List_Name = 'Beta' AND a.cust_id = x.cust_id) GROUP BY List_Name
  UNION ALL
  SELECT List_Name, 'Add', count(*) FROM #customers a WHERE list_name = 'Gamma' AND not exists(SELECT * FROM #customers x WHERE List_Name = 'Beta' AND a.cust_id = x.cust_id) GROUP BY List_Name
  UNION ALL
  SELECT 'Gamma', 'Remove', -count(*) FROM #customers a WHERE list_name = 'Beta' AND not exists(SELECT * FROM #customers x WHERE List_Name = 'Gamma' AND a.cust_id = x.cust_id) GROUP BY List_Name
  </code></pre>