Get record if previous answer was no and current answer is yes 3

[niebs2]

I am trying to code to get the responses from a questionaire. I have to find people who based on previous questions who said "no" and and now on more recent questionaire said yes to same Question)Id. I have the tables matched to what they need but i am having problems with how to check the dates and see if it was no on first question_id and now with latest questionaire they said yes. Hope i explained this well enough.

Select * from table_a A
join table_q Q on q.quest_key = a.quest_key
join table_mbr M on m.ases_key = a.ases_key
join table_rspn R on r.mbr_rsp_key = m.mbr_rsp_key and r.ases_quest_key = q.ases_quest_key
WHERE r.rsp_txt = 'No' and to_date(datefield) ---this is where i am not sure what to do for comparing old date with no answer to yes with latest date?????????
and q.quest_id IN (22 quest_ids)

ANy help would be appreciated. Thanks.

 

[SantaMufasa]

Niebs2,

I am positive that we can help you resolve your need. But creating a representative data set on our end that simulates your environment makes it difficult for us to help you. Furthermore, the joins to "other" tables add unnecessary complexity to our creating sample data.

So, could you please post a minimal "CREATE TABLE <Niebs_table>..." statement and several INSERT INTO <Niebs_table> statements (perhaps taken from your original multi-table query, above) that can give us a sample data set to code a resolution for you?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[niebs2]

Mufasa,
That would be very hard to do since the data is not related very well and design of it is bad. I am the guy who has to deal with it as is. If you have any idea on the Date issue and how to handle that. That would help me a lot. Havent had to do date field comparisons that much and very rusty with it.

 

[SantaMufasa]

Since "the data is not related very well and design of it is bad", how about if I post a minimal "CREATE TABLE Niebs..." statement and then you compose several INSERT INTO <Niebs_table> statements (since that's what I would have to do to create a sample solution for you)?

Here's the CREATE TABLE statement for you:

CREATE TABLE NIEBS
      (MBR_NUM      NUMBER
      ,QUESTION_NUM NUMBER
      ,RESPONSE     VARCHAR2(3) CHECK (RESPONSE IN ('YES','NO'))
      ,RESP_DT      DATE
      );

Now all you need to do is to run my "CREATE TABLE..." code, then compose several "INSERT INTO NIEBS..." statements that run successfully into the NIEBS table, above, that reflect the scenario you wish to see.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[yelworcm]

If you can partition and order the questions, then you will likely find either the LEAD or LAG windowing functions to be of significant use. The results of LEAD and LAG will give you the "n rows before/after" effect you sound like you need.

 

[SantaMufasa]

...and as I mentioned earlier, if you post some sample data, we can show samples/proof of concept on these types of Oracle Analytical functions for your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[niebs2]

hope this is what your looking for. i also added fields to table design.
CREATE TABLE NIEBS
(MBR_NUM NUMBER
, survey_name
,QUESTION_NUM NUMBER
,RESPONSE VARCHAR2(3) CHECK (RESPONSE IN ('YES','NO'))
,creat_DT DATE
, lst_up_dt DATE (i added this since its a field and changes from creat date
);

insert into NIEBS
MBR_NUM - survey_name - QUESTION_NUM - RESPONSE - CREAT_DT - LST_UP_DT
11111 smoke 1 no 1/1/2012 1/2/2012 (they can come back to finish)
11111 pre-op 1 yes 2/3/2013 2/3/2013
22222 smoke 1 yes 3/14/2008 3/14/2008
22222 pre-op 3 no 6/22/2013 6/22/2013
33333 cancer 5 no 1/3/2012 1/3/2012
33333 pre-op 3 yes 4/1/2013 4/1/2013
44444 smoke 1 no 2/1/2012 2/2/2012
44444 pre-op 1 yes 2/3/2013 2/3/2013
55555 smoke 1 yes 3/14/2008 3/14/2008
55555 pre-op 3 no 6/22/2013 6/22/2013
66666 cancer 5 no 1/3/2011 1/3/2011
66666 pre-op 3 yes 4/1/2013 4/1/2013
66666 smoke 1 yes 3/1/2012 3/1/2012

 

[SantaMufasa]

(I) hope this is what your looking for.

Well let's check...I said:

(You) compose several "INSERT INTO NIEBS..." statements that run successfully into the NIEBS table

Did the lines of data run successfully into the NIEBS table? I think not...To get your lines of data into the NIEBS table, I had to change your data to read:

insert into niebs values ('11111','smoke','1','NO',to_date('1/1/2012','mm/dd/yyyy'),to_date('1/2/2012','mm/dd/yyyy'));
insert into niebs values ('11111','pre-op','1','YES',to_date('2/3/2013','mm/dd/yyyy'),to_date('2/3/2013','mm/dd/yyyy'));
insert into niebs values ('22222','smoke','1','YES',to_date('3/14/2008','mm/dd/yyyy'),to_date('3/14/2008','mm/dd/yyyy'));
insert into niebs values ('22222','pre-op','3','NO',to_date('6/22/2013','mm/dd/yyyy'),to_date('6/22/2013','mm/dd/yyyy'));
insert into niebs values ('33333','cancer','5','NO',to_date('1/3/2012','mm/dd/yyyy'),to_date('1/3/2012','mm/dd/yyyy'));
insert into niebs values ('33333','pre-op','3','YES',to_date('4/1/2013','mm/dd/yyyy'),to_date('4/1/2013','mm/dd/yyyy'));
insert into niebs values ('44444','smoke','1','NO',to_date('2/1/2012','mm/dd/yyyy'),to_date('2/2/2012','mm/dd/yyyy'));
insert into niebs values ('44444','pre-op','1','YES',to_date('2/3/2013','mm/dd/yyyy'),to_date('2/3/2013','mm/dd/yyyy'));
insert into niebs values ('55555','smoke','1','YES',to_date('3/14/2008','mm/dd/yyyy'),to_date('3/14/2008','mm/dd/yyyy'));
insert into niebs values ('55555','pre-op','3','NO',to_date('6/22/2013','mm/dd/yyyy'),to_date('6/22/2013','mm/dd/yyyy'));
insert into niebs values ('66666','cancer','5','NO',to_date('1/3/2011','mm/dd/yyyy'),to_date('1/3/2011','mm/dd/yyyy'));
insert into niebs values ('66666','pre-op','3','YES',to_date('4/1/2013','mm/dd/yyyy'),to_date('4/1/2013','mm/dd/yyyy'));
insert into niebs values ('66666','smoke','1','YES',to_date('3/1/2012','mm/dd/yyyy'),to_date('3/1/2012','mm/dd/yyyy'));

Notice also that all of the 'yes'/'no' values had to change to 'YES'/'NO' and all of the dates needed TO_DATE() functions? (I'm not making these observations to be critical...I'm bringing them up to improve turnaround time for your questions next time.)

Also, I had to presume that you wanted the "previous-record" logic to apply to groupings by QUESTION_NUM within MBR_NUM, correct? (Otherwise, your sample data does not produce meaningful results per your original specifications.)

First, here is a SELECT of your data in the NIEBS table:

select * from niebs;

   MBR_NUM SURVEY_NAME QUESTION_NUM RES CREAT_DT  LST_UP_DT
---------- ----------- ------------ --- --------- ---------
     11111 smoke                  1 NO  01-JAN-12 02-JAN-12
     11111 pre-op                 1 YES 03-FEB-13 03-FEB-13
     22222 smoke                  1 YES 14-MAR-08 14-MAR-08
     22222 pre-op                 3 NO  22-JUN-13 22-JUN-13
     33333 cancer                 5 NO  03-JAN-12 03-JAN-12
     33333 pre-op                 3 YES 01-APR-13 01-APR-13
     44444 smoke                  1 NO  01-FEB-12 02-FEB-12
     44444 pre-op                 1 YES 03-FEB-13 03-FEB-13
     55555 smoke                  1 YES 14-MAR-08 14-MAR-08
     55555 pre-op                 3 NO  22-JUN-13 22-JUN-13
     66666 cancer                 5 NO  03-JAN-11 03-JAN-11
     66666 pre-op                 3 YES 01-APR-13 01-APR-13
     66666 smoke                  1 YES 01-MAR-12 01-MAR-12

13 rows selected.

Now, using the Oracle Analytics function, "LAG" (to which yelorcm alluded earlier), we can see your columns of data with the previous-row's RESPONSE within groupings by MBR_NUM/QUESTION_NUM:

select MBR_NUM
      ,SURVEY_NAME
      ,QUESTION_NUM
      ,RESPONSE
      ,lag(response,1)
           over (partition by mbr_num,question_num
                     order by lst_up_dt) Prev_resp
      ,CREAT_DT
      ,LST_UP_DT
  from niebs;

MBR_NUM SURVEY_NAME QUESTION_NUM RESPONSE PREV_RESP CREAT_DT  LST_UP_DT
------- ----------- ------------ -------- --------- --------- ---------
  11111 smoke                  1 NO                 01-JAN-12 02-JAN-12
  11111 pre-op                 1 YES      NO        03-FEB-13 03-FEB-13
  22222 smoke                  1 YES                14-MAR-08 14-MAR-08
  22222 pre-op                 3 NO                 22-JUN-13 22-JUN-13
  33333 pre-op                 3 YES                01-APR-13 01-APR-13
  33333 cancer                 5 NO                 03-JAN-12 03-JAN-12
  44444 smoke                  1 NO                 01-FEB-12 02-FEB-12
  44444 pre-op                 1 YES      NO        03-FEB-13 03-FEB-13
  55555 smoke                  1 YES                14-MAR-08 14-MAR-08
  55555 pre-op                 3 NO                 22-JUN-13 22-JUN-13
  66666 smoke                  1 YES                01-MAR-12 01-MAR-12
  66666 pre-op                 3 YES                01-APR-13 01-APR-13
  66666 cancer                 5 NO                 03-JAN-11 03-JAN-11

13 rows selected.

Notice that only two rows of your data have a value for PREV_RESP. This is because no other rows (when grouped by MBR_NUM and QUESTION_NUM) have a value on the just-previous row in the group.

Now, to get just those two rows to display in the result set, I place the previous query inside the "FROM" clause (making it a "virtual VIEW") for an outer query (so that I can compare, in the WHERE clause, the "current response" to the "previous-row's response"):

select *
  from (select MBR_NUM
              ,SURVEY_NAME
              ,QUESTION_NUM
              ,RESPONSE
              ,lag(response,1)
                   over (partition by mbr_num,question_num
                             order by lst_up_dt) Prev_resp
              ,CREAT_DT
              ,LST_UP_DT
          from niebs)
 where response = 'YES'
   and Prev_resp = 'NO'
;

MBR_NUM SURVEY_NAME QUESTION_NUM RESPONSE PREV_RESP CREAT_DT  LST_UP_DT
------- ----------- ------------ -------- --------- --------- ---------
  11111 pre-op                 1 YES      NO        03-FEB-13 03-FEB-13
  44444 pre-op                 1 YES      NO        03-FEB-13 03-FEB-13

2 rows selected.

Let us know if this resolves your original question, and if you have additional, follow-on questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[yelworcm]

An excellent writeup, Mufasa.

 

[niebs2]

Thank you Mufasa. I hope i can get this to work in the design they have here!

 

[Andrzejek]

niebs2,

To say ‘Thank you’ to Mufasa for all his work and help, use “Like this post? Star it!” on his post

He deserves it


Have fun.

---- Andy

 

[SantaMufasa]

Thanks Andy, Niebs2 and yelworcm !

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.