-
[RESOLVED] 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?
Last edited by Moe1950; 08-11-2010 at 10:03 AM.
-
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.
Last edited by Moe1950; 08-11-2010 at 10:10 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|