CODEWITHSUNDEEP
sql-server-2008
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

Removing the rows from the select after processing

I have to a table like this

Year Month
2012  8
2012  7
2012  4
2012  3
2011  7
2011  3
2011  1
2010  10
2010  9
2010  8

This tables show which month and year is remaning, now let say I have completed 2012, 7 month. Now I want to have list like 

Year Month
    2012  8
    2012  4
    2012  3
    2011  7
    2011  3
    2011  1
    2010  10
    2010  9
    2010  8

I am using below query but this is not giving me the correct records

SELECT YEAR, MONTH from tablex
WHERE Year NOT IN (SELECT DISTINCT YEAR from OTHER_TABLE INNER JOIN Some_Other_Table)
AND  MONTH NOT IN (SELECT DISTINCT MONTH FROM OTHER_TABLE INNER JOIN Some_Other_Table)

When OTHER_TABLE is null then I am getting the currect count, but when Other table has year 2012 and month 7 I am getting no results.

P.S.: There is no joining columns available for tablex and OTHER_TABLE and Som_Other_Table

Upvotes: 0

Views: 35

Answers (2)

lc.
lc.

Reputation: 116458

This answer assumes you have a single row {Year = 2012, Month = 7} in OTHER_TABLE to designate the completed month.

This won't work because you're checking the year and month separately. Therefore you are not removing the row (2012, 7) from the results, you're removing all rows with Year = 2012 and all rows with Month = 7. Try this anti-join instead:

SELECT x.YEAR, x.MONTH
FROM tablex x
LEFT OUTER JOIN OTHER_TABLE y ON x.YEAR = y.YEAR AND x.MONTH = y.MONTH
WHERE y.YEAR IS NULL

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460108

You just have to use NOT EXISTS:

SELECT YEAR, MONTH 
from tablex t1 
WHERE NOT EXISTS
(
    SELECT 1 FROM OTHER_TABLE t2
    WHERE t2.Year = t1.Year AND t2.Month = t2.Month 
)

Upvotes: 2

Related Questions