Exclusion Query

Transact-SQL
1

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.

2

Hi

You will have to exclude those years

Example Data
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

Thanks

3 
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
4

Another way... I'm not sure which would perform better.

[sql]
select L2011.salesman_number
FROM dbo.list_of_salesmen_with_2011_sales L2011
where not exists (
select *
from dbo.sales_table ST
WHERE ST.salesman_number = L2011.salesman_number AND
ST.sales_date >= '20160101' AND
ST.sales_date < '20190101'
)[/sql]