Filtering data according to a reference category – The Venn-Diagrams logic

Let’s say you have data of purchases by date (year and month) made by consumers, with the following data entries:

Table name: purchases

You might have noticed that consumers have made purchases in several months in some years. 

Assuming that 2015 is a reference year in your business, you might want to filter data according to 2015. For example, you might want to explore consumers based on their purchase dates:

All of these options are very confusing when addressed only in writing, so for convince, they are also graphically presented in Venn-Diagrams:

•  All consumers that made a purchase on all dates;

• All consumers that made a purchase only in 2015;

• All consumers that made a purchase, excluding those that made a purchase only in 2015;

• All consumers that made a purchase only in years other than 2015;

• All consumers that made a purchase, excluding consumers that made a purchase only in years other than 2015;

• All consumers that made a purchase only in both 2015 and other years;

• All consumers that made a purchase, excluding consumers that made a purchase only in both 2015 and other years.

Here is the MS SQL syntax to get the right answers for all of the options:

Hope you enjoyed this article,

Nir