Search code examples
sqldatecountunique

DISTINCT COUNT OF IDS PRESENT IN TWO TIME PERIODS

I'd like to understand the distinct count of employees that sold a car in the previous year and in the current one year lookback. Dates in the example below end 2018-11-18. I can pull the counts when running with only one 'between and' clause but not meeting both conditions.

SELECT COUNT(UNIQUE(EMPLOYEE_ID))
FROM SALES_TABLE_123
WHERE SOLD_ITEM = 'CARS'
AND (SELL_DATE) BETWEEN ('2017-11-19') AND ('2018-11-19')
AND (SELL_DATE) BETWEEN ('2016-11-18') AND ('2017-11-18');
Solution 
  • SELECT COUNT(UNIQUE(LY.EMPLOYEE_ID))
FROM SALES_TABLE_123 LY
     inner join
     SALES_TABLE_123 TY
     on LY.EMPLOYEE_ID=TY.EMPLOYEE_ID
WHERE LY.SOLD_ITEM = 'CARS'
AND TY.SOLD_ITEM='CARS'
AND TY.SELL_DATE BETWEEN ('2017-11-19') AND ('2018-11-19')
AND LY.SELL_DATE BETWEEN ('2016-11-18') AND ('2017-11-18');