Results 1 to 6 of 6

Thread: Return Specific Records NOT Matching Input Year

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    Thumbs up [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.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    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
                    )

  3. #3
    Join Date
    Sep 2005
    Posts
    168
    SELECT companycode
    FROM mytable
    EXCEPT --exclude companies that have an entry in 2010
    SELECT companycode
    from mytable
    where orderyear >= '20100101'
    AND orderyear < '20110101'

    --HTH--

  4. #4
    Join Date
    Dec 2009
    Posts
    79
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    select distinct comcode
    from table
    where comcode not in
    (select comcode from table
    where year(orderdate)='2010')

  6. #6
    Join Date
    Dec 2009
    Posts
    79
    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
  •