Return Specific Records NOT Matching Input Year

I have a field in my table which contains a date. Example: 06/30/2010, 09/30/2003, 03/31/2005, 06/30/2011, etc – this table also contains a field for company code.

I need to get a list of all company codes that do not have an entry for an input year; example: 2010

I can do:

Code:

---

select companycode, orderyear
from mytable
where year(orderyear) <> ‘2010’

---

And it will give me a list of ALL records in the table that do not contain a date for 2010, but what I need are JUST the records that do not contain an entry for 2010.

For example, I do not want to see every record in the table for companycode 12345 that doesn't have an orderyear date in 2010. I just want to see the one record for companycode 12345 where the orderyear field is blank or NULL.

How would I refine that query to return such a result?

Moe1950. I am not sure exactly what you want as what you say you don't want and what you want are very similar.

There isn't much to go on from you posting but something with this format may get you what you want (it is a generic format and is not syntactically correct):

Code:

---

SELECT col-list
FROM mytable A
WHERE NOT EXISTS(SELECT *
                FROM mytable B
                WHERE orderyear eqauls 2000
                  AND A.key-col = B.key-col
                )

---

SELECT companycode
FROM mytable
EXCEPT --exclude companies that have an entry in 2010
SELECT companycode
from mytable
where orderyear >= '20100101'
AND orderyear < '20110101'

--HTH--

Here is an example what I mean:

Code:

---

ComCode  OrderYear
001      12/31/2008
001      12/31/2009
001      12/31/2010
001      12/31/2011
002      06/30/2007
002      06/30/2008
002      06/30/2009
003      06/30/2208
003      06/30/2009
003      06/30/2010

---

I'm looking for the company code for anyone that does not have a date in 2010. In the above case, ONLY companycode 002 should be returned.

select distinct comcode
from table
where comcode not in
(select comcode from table
where year(orderdate)='2010')

Thank you skhanal...that works!

Although, I'm not a big fan of SELECT DISTINCT and only use that when I absolutely have to (and I can't remember that last time I absolutely had to)

I will go with the slightly modified version of

Code:

---

select comcode
from table
where comcode not in
(select comcode from table
where year(orderdate)='2010')
group by comcode

---

Using the Group By is cleaner that using the Distinct - just my opinion. :D