none
Comparing values in same column RRS feed

  • Question

  • Hi, 

    In sample table below, I would like to compare REF values (numeric only vs prefix CM) and indicate Y/N if their DATE column is same Month.

    REF DATE
    123 1/2/2016
    456 1/15/2016
    CM123 1/11/2016
    789 5/1/2016
    234 6/1/2016
    CM456 3/2/2016
    CM789 5/19/2016

    Resulting output from above table will be:

    REF1 REF2 SAME_MONTH_YN
    123 CM123 Y
    456 CM456 N
    789 CM789 Y

    Any help is greatly appreciated.


    • Edited by sednitrac Thursday, May 25, 2017 6:19 PM
    Thursday, May 25, 2017 6:18 PM

Answers

  • CREATE TABLE mytable(
       REF  VARCHAR(5) NOT NULL PRIMARY KEY
      ,DATE DATE  NOT NULL
    );
    INSERT INTO mytable(REF,DATE) VALUES ('123','1/2/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('456','1/15/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM123','1/11/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('789','5/1/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('234','6/1/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM456','3/2/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM789','5/19/2016');
    
    
    select a.REF REF1, b.REF REF2
    , case when datediff(month,a.DATE,b.DATE)=0 then 'Y' else 'N' END 	SAME_MONTH_YN
     from mytable a join mytable b on 'CM'+a.REF=b.REF
    
    drop table mytable

    • Marked as answer by sednitrac Thursday, May 25, 2017 6:38 PM
    Thursday, May 25, 2017 6:35 PM
    Moderator

All replies

  • CREATE TABLE mytable(
       REF  VARCHAR(5) NOT NULL PRIMARY KEY
      ,DATE DATE  NOT NULL
    );
    INSERT INTO mytable(REF,DATE) VALUES ('123','1/2/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('456','1/15/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM123','1/11/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('789','5/1/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('234','6/1/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM456','3/2/2016');
    INSERT INTO mytable(REF,DATE) VALUES ('CM789','5/19/2016');
    
    
    select a.REF REF1, b.REF REF2
    , case when datediff(month,a.DATE,b.DATE)=0 then 'Y' else 'N' END 	SAME_MONTH_YN
     from mytable a join mytable b on 'CM'+a.REF=b.REF
    
    drop table mytable

    • Marked as answer by sednitrac Thursday, May 25, 2017 6:38 PM
    Thursday, May 25, 2017 6:35 PM
    Moderator
  • Thank you for your help Jingyang Li
    Thursday, May 25, 2017 6:39 PM
  • ...is same Month

    And same year? Be careful how you phrase your requirement - especially when you "test" with such a limited range of sample data.

    Thursday, May 25, 2017 6:39 PM
  • Good point, Scott. Fortunately, raw data I was targeting were all same year but thanks for pointing that out.
    Thursday, May 25, 2017 9:15 PM
  • In the future, we you please follow forum etiquette and post DDL and insert statements? It's not a lot of fun to have to do all that work for you when we are trying to help you for free :(

    >> In sample table below, I would like to compare REF values (numeric only vs prefix CM) and indicate Y-N if their DATE column is same Month. <<

    CREATE TABLE Foobars
    (foobar_id VARCHAR (12) NOT NULL PRIMARY KEY
       CHECK (foobar_id LIKE '[0-9][0-9][0-9]'
                   OR foobar_id LIKE 'CM[0-9][0-9][0-9]'),
     foo_date DATE NOT NULL);

    By definition, a table must have a key. I'm going to guess it's your first column. But a better question is why do you have this problem? In the first place? If you would put constraint on the column, you could have prevented or required the "CM" prefix and kept your data consistent. What have you done to assure that this information comes in pairs? Looking at your sample data, since we don't have specs, it looks like you disregard them. Is that correct? 

    INSERT INTO Foobars
    VALUES
    ('123', '2016-01-02'),
    ('CM123', '2016-01-11'),
    ('234', '2016-06-01'), -- no CM match! 
    ('456', '2016-01-15'),
    ('CM456', '2016-03-02'),
    ('789', '2016-05-01'),
    ('CM789',  '2016-05-19');

    Here is one possible way using a CTEs.

    WITH CM_Foobars
    AS
    (SELECT foobar_id, MONTH( foo_date)  AS foo_month
      FROM Foobars
     WHERE LIKE 'CM[0-9][0-9][0-9]'),
    Plain_Foobars
    AS
    (SELECT foobar_id, MONTH( foo_date)  AS foo_month
      FROM Foobars
     WHERE LIKE '[0-9][0-9][0-9]'),

    SELECT P.foo_id AS matched_foo_id
      FROM CM_foobars AS C, Plain_Foobars AS P
     WHERE C.foo_month  =  P.foo_month ; 

    If you really need to use the flags for some reason, then just put it in a CASE expression. If you really want to handle unpaired rows, then do an outer join.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 25, 2017 9:19 PM
  • CELKO, I will do so, thank you!
    Thursday, May 25, 2017 11:46 PM