INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

RE: Get record if previous answer was no and current answer is yes

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,

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

RE: Get record if previous answer was no and current answer is yes

(OP)
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.

RE: Get record if previous answer was no and current answer is yes

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:

CODE

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.

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

RE: Get record if previous answer was no and current answer is yes

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.

RE: Get record if previous answer was no and current answer is yes

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

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

RE: Get record if previous answer was no and current answer is yes

(OP)
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

RE: Get record if previous answer was no and current answer is yes

3

Quote (NIEBS2)

(I) hope this is what your looking for.

Well let's check...I said:

Quote (SantaMufasa)

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

CODE

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:

CODE

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:

CODE

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"):

CODE

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.

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

RE: Get record if previous answer was no and current answer is yes

An excellent writeup, Mufasa.

RE: Get record if previous answer was no and current answer is yes

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

RE: Get record if previous answer was no and current answer is yes

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 smile

Have fun.

---- Andy

RE: Get record if previous answer was no and current answer is yes

Thanks Andy, Niebs2 and yelworcm !

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close