none
How to omit records based on a status code

    Question

  • I am trying to select records based on a status 'N' or 'A' by a certain date as long as by the chosen date there is not another status given to the same course like 'D' or 'X'.

    Here are some sample data...

    CREATE TABLE #TEST
    (ID VARCHAR(7),
    COURSE VARCHAR(10),
    CURRENT_STATUS VARCHAR(1),
    STATUS_DATE DATETIME)
    
    INSERT INTO #TEST
    (ID, COURSE, CURRENT_STATUS, STATUS_DATE)
    VALUES
    ('0020541','AAAA-1000H', 'X', '2013-08-27 00:00:00.000'),
    ('0020541','AAAA-1000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','BBBB-2000H', 'D', '2013-11-04 00:00:00.000'),
    ('0020541','BBBB-2000H', 'N', '2013-08-27 00:00:00.000'),
    ('0020541','CCCC-3000H', 'D', '2013-09-11 00:00:00.000'),
    ('0020541','CCCC-3000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','DDDD-4000H', 'A', '2013-08-25 00:00:00.000'),
    ('0020541','EEEE-5000H', 'N', '2013-08-26 00:00:00.000') SELECT * FROM #TEST DROP TABLE #TEST

    If the query is to determine how many records with a status of N or A up to and incl August 26, 2013, I should see 4 records... AAAA-1000H, CCCC-3000H, DDDD-4000H, EEEE-5000H

    If the query is to determine how many records with a status of N or A up to and incl October 1, 2013, I should see 3 records... BBBB-2000H,  DDDD-4000H, EEEE-5000H

    ...and so on.

    Any help is appreciated.


    Tuesday, May 06, 2014 5:32 PM

Answers

  • ; WITH CTE AS (
        SELECT ID, COURSE, CURRENT_STATUS, STATUS_DATE,
               row_number() OVER (PARTITION BY ID, COURSE
                                  ORDER BY STATUS_DATE DESC) AS rowno
        FROM   #TEST
        WHERE  STATUS_DATE <= '2013-08-26'
    )
    SELECT ID, COURSE, CURRENT_STATUS, STATUS_DATE
    FROM   CTE
    WHERE  rowno = 1
      AND  CURRENT_STATUS IN ('A', 'N')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 06, 2014 10:15 PM

All replies

  • I am trying to select records based on a status 'N' or 'A' by a certain date as long as by the chosen date there is not another status given to the same course like 'D' or 'X'.

    Here are some sample data...

    CREATE TABLE #TEST
    (ID VARCHAR(7),
    COURSE VARCHAR(10),
    CURRENT_STATUS VARCHAR(1),
    STATUS_DATE DATETIME)
    
    INSERT INTO #TEST
    (ID, COURSE, CURRENT_STATUS, STATUS_DATE)
    VALUES
    ('0020541','AAAA-1000H', 'X', '2013-08-27 00:00:00.000'),
    ('0020541','AAAA-1000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','BBBB-2000H', 'D', '2013-11-04 00:00:00.000'),
    ('0020541','BBBB-2000H', 'N', '2013-08-27 00:00:00.000'),
    ('0020541','CCCC-3000H', 'D', '2013-09-11 00:00:00.000'),
    ('0020541','CCCC-3000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','DDDD-4000H', 'A', '2013-08-25 00:00:00.000'),
    ('0020541','EEEE-5000H', 'N', '2013-08-26 00:00:00.000') SELECT * FROM #TEST DROP TABLE #TEST

    If the query is to determine how many records with a status of N or A up to and incl August 26, 2013, I should see 4 records... AAAA-1000H, CCCC-3000H, DDDD-4000H, EEEE-5000H

    If the query is to determine how many records with a status of N or A up to and incl October 1, 2013, I should see 3 records... BBBB-2000H,  DDDD-4000H, EEEE-5000H

    ...and so on.

    Any help is appreciated.


    I guess I didn't understand your problem. Not sure if this might help you.

    SELECT * FROM #TEST
    where CURRENT_STATUS IN ('N','A') AND STATUS_DATE<='2013-08-26'
    AND STATUS_DATE NOT IN(Select STATUS_DATE from #test
    where CURRENT_STATUS IN ('D','X'))
    
    SELECT * FROM #TEST
    where CURRENT_STATUS IN ('N','A')
     AND STATUS_DATE<='2013-10-01'
     AND STATUS_DATE NOT IN(Select STATUS_DATE from #test
    where CURRENT_STATUS IN ('D','X'))
    


    - please mark correct answers

    • Marked as answer by lewk3000 Tuesday, May 06, 2014 5:51 PM
    • Unmarked as answer by lewk3000 Tuesday, May 06, 2014 6:53 PM
    Tuesday, May 06, 2014 5:46 PM
  • Do you want a count or a selection?

    DECLARE @date date = '20130826';
    
    CREATE TABLE #TEST
    (ID VARCHAR(7),
    COURSE VARCHAR(10),
    CURRENT_STATUS VARCHAR(1),
    STATUS_DATE DATETIME)
    
    INSERT INTO #TEST
    (ID, COURSE, CURRENT_STATUS, STATUS_DATE)
    VALUES
    ('0020541','AAAA-1000H', 'X', '2013-08-27 00:00:00.000'),
    ('0020541','AAAA-1000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','BBBB-2000H', 'D', '2013-11-04 00:00:00.000'),
    ('0020541','BBBB-2000H', 'N', '2013-08-27 00:00:00.000'),
    ('0020541','CCCC-3000H', 'D', '2013-09-11 00:00:00.000'),
    ('0020541','CCCC-3000H', 'N', '2013-08-26 00:00:00.000'),
    ('0020541','DDDD-4000H', 'A', '2013-08-25 00:00:00.000'),
    ('0020541','EEEE-5000H', 'N', '2013-08-26 00:00:00.000')
    
    SELECT * FROM #TEST
    where Status_Date < @date
    and Current_Status in ('A','N');
    
    DROP TABLE #TEST


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, May 06, 2014 5:46 PM
  • Sorry Murali.  I was a little quick on to mark it as the right answer.

    What I want the query to do is pick only the records that still have an A or N by a certain date.  If there is an X or D by the date for a course that once had an A or N then it should not be picked up.

    Does that help?



    • Edited by lewk3000 Tuesday, May 06, 2014 7:01 PM
    Tuesday, May 06, 2014 5:51 PM
  • SELECT ID,COURSE,CURRENT_STATUS,STATUS_DATE
    FROM
    (
    SELECT *,SUM(CASE WHEN CURRENT_STATUS IN ('D','X') THEN 1 ELSE 0 END) OVER (PARTITION BY COURSE) AS Occ
    FROM #Test
    WHERE STATUS_DATE <= '20130826'
    AND CURRENT_STATUS IN ('N','A')
    )t
    WHERE Occ = 0 


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, May 06, 2014 5:52 PM
  • Thank you for trying to post DDL. So many people here are so damn rude. But you got it wrong. Seriously, awful bad wrong! No keys, not ISO-11179, etc. Get a book at data modeling, so you can do it right. 

    >> I am trying to select records [sic] based on a status 'N' or 'A' by a certain date as long as by the chosen date there is not another status given to the same course_code like 'D' or 'X'.<<

    A status is a “state of being”; a state of being has a duration; a duration is (start_date, end_date). This is basic data modeling!! 

    No key, why is there no key??? 

    You have a magical generic “id”, think about how stupid “VARCHAR(1)” is, etc. 

    What you have is a deck of punch cards, not RDBMS. Here is my guises: 


    CREATE TABLE Foobar_Courses
    (test_id CHAR(7) NOT NULL, 
     course_code CHAR(10) NOT NULL, 
     foobar_status CHAR(1) NOT NULL
      CHECK (foobar_status IN ('X', 'N', 'D', 'A')) , 
    PRIMARY KEY (test_id, course_code, foobar_status)
     status_start_date DATE NOT NULL, 
     status_end_date DATE, 
      CHECK (status_start_date <= status_end_date) );

    >> If the query is to determine how many records [sic] with a foobar_status of N or A up to and incl August 26, 2013, I should see 4 records [sic]... AAAA-1000H, CCCC-3000H, DDDD-4000H, EEEE-5000H <<

    Pretty easy to write now, unh?

    --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

    Tuesday, May 06, 2014 6:53 PM
  • ; WITH CTE AS (
        SELECT ID, COURSE, CURRENT_STATUS, STATUS_DATE,
               row_number() OVER (PARTITION BY ID, COURSE
                                  ORDER BY STATUS_DATE DESC) AS rowno
        FROM   #TEST
        WHERE  STATUS_DATE <= '2013-08-26'
    )
    SELECT ID, COURSE, CURRENT_STATUS, STATUS_DATE
    FROM   CTE
    WHERE  rowno = 1
      AND  CURRENT_STATUS IN ('A', 'N')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 06, 2014 10:15 PM
  • ... I want the query to do is pick only the records that still have an A or N by a certain date.
        If there is an X or D by the date for a course that once had an A or N then it should not be picked up. ...

    Try

    -- code #1 v2
    declare @DateUpto datetime;
    set @DateUpto= Convert(datetime, '2013-8-26', 120);
    
    SELECT ID, COURSE
      from #TEST as T1
      where STATUS_DATE <= @DateUpto
    and CURRENT_STATUS in ('A','N') and not exists (SELECT * from #TEST as T2 where T2.STATUS_DATE <= @DateUpto and T2.ID=T1.ID and T2.Course=T1.Course and T2.CURRENT_STATUS in ('X','D'));
     

    or

    -- code #2 v2
    declare @DateUpto datetime;
    set @DateUpto= Convert(datetime, '2013-8-26', 120); 
    -- adjust date
    set @DateUpto= DateAdd(day, +1, @DateUpto);
            
    --
    SELECT ID, COURSE from #TEST as T1 where STATUS_DATE < @DateUpto
    and CURRENT_STATUS in ('A','N') and not exists (SELECT * from #TEST as T2 where T2.STATUS_DATE < @DateUpto and T2.ID=T1.ID and and T2.Course=T1.Course and T2.CURRENT_STATUS in ('X','D'));



        José Diz     Belo Horizonte, MG - Brasil


    • Edited by Jose.Diz Wednesday, May 07, 2014 2:16 PM (T2.ID=T1.ID)
    Wednesday, May 07, 2014 2:06 PM
  • All responses were helpful and correct but this is the one I chose because it is the one that I tried but before hand and only required a few tweaks found here.

    Thanks to all for responding.

    Wednesday, May 07, 2014 2:11 PM