I have a list of salesmen with annual sales figures from 2011. I need to build a SQL Server query to show those who haven't sold in the years 2016, 2017 and 2018. How is this done? Thanks.
You will have to exclude those years
ID Year TotalSales
1 2001 2000
2 2002 5000
3 2003 3000
4 2004 4000
if want to exclude 2001 and 2003 years
select totalsales from table
where year NOT IN (2001,2003)
if you need more help
please come online lets chat
SELECT L2011.salesman_number FROM dbo.list_of_salesmen_with_2011_sales L2011 OUTER APPLY ( SELECT SUM(ST.sales) AS sales FROM dbo.sales_table ST WHERE ST.salesman_number = L2011.salesman_number AND ST.sales_date >= '20160101' AND ST.sales_date < '20190101' ) AS oa1 WHERE oa1.sales IS NULL
Another way... I'm not sure which would perform better.
FROM dbo.list_of_salesmen_with_2011_sales L2011
where not exists (
from dbo.sales_table ST
WHERE ST.salesman_number = L2011.salesman_number AND
ST.sales_date >= '20160101' AND
ST.sales_date < '20190101'