Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: find people on monotherapy

    Hi, I would like to find people that have been taking any 1 of 3 drugs, and then only select those that were on monotherapy (only on one drug at any one given time). The best way to do it is to choose the date the drug was given, and then if there was no other different drug given within 2 months of that date, then they are classes as being on monotherapy. I also need to bring back the drugs that these monotherapy were taking.Here's what I have:

    Code:
    select distinct person, drug_type, drug_date from db
    case when drug_type = 'drug1' then drug1
           when drug_type = 'drug2' then drug2
           when drug_type = 'drug3' then drug3
    else 'error' end as drug_type
    
    /*this is the bit i need help with*/
    
    group by person, 
    case when drug_type = 'drug1' then drug1
           when drug_type = 'drug2' then drug2
           when drug_type = 'drug3' then drug3
    else 'error' end
    so basically I'm struggling with the logic side of things to get all the drug_dates and then pick out the people who's drug_dates for different drug_types are less than 2 months apart. Anyone done anything like this before?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you want to get a meaningful answer, you'd be better off supplying your table definition(s) in the form of CREATE TABLE statements, sample data (INSERT statements), and the expected output.

    With the information you've provided so far, your query should look something like
    Code:
    select distinct person, drug_type, drug_date, case .... end drug_type
    from db d1
    where not exists (
     select 1 from db d2 
     where d2.drug_type <> d1.drug_type
     and d2.date_given between [whatever you mean by "within 2 months of" d1.date_given]
    )
    The GROUP BY clause you've shown is invalid and not necessary.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release and platform OS?

    What are the DDL(CREATE TABLE ... or DESCRIBE TABLE ...) of table(s)?

    Please try to publish test data including most considerable cases.
    For example: some persons who are monotherapy who are not monotherapy with different reason, who are taking more than three drugs, who are not taking drugs, who took more than three drugs but less than three at a time, so on...

    I'm afraid that you might want to add more other conditions or change some conditions from your original,
    if I showed some example based on a small test data.

    The descriptions of requirements literaly may be not complete, or may include some unclear statement for others.
    Although you thought that you stated clearly, it might be not so clear for who didn't share environments/backgrounds with you(including me).

    For example: what means "within two month"?
    (1) difference of month part of two dates is less than or equal to 2.
    (1') difference of month part of two dates is less than or equal to 1.
    (2) days between two dates is less than 60 days.
    (3) difference of month part of two dates is 0 or 1.
    or difference of month part of dates is 2 and difference of day part of dates is 0 or negative.
    (4) or other...

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by n_i View Post
    If you want to get a meaningful answer, you'd be better off supplying your table definition(s) in the form of CREATE TABLE statements, sample data (INSERT statements), and the expected output.

    With the information you've provided so far, your query should look something like
    Code:
    select distinct person, drug_type, drug_date, case .... end drug_type
    from db d1
    where not exists (
     select 1 from db d2 
     where d2.drug_type <> d1.drug_type
     and d2.date_given between [whatever you mean by "within 2 months of" d1.date_given]
    )
    The GROUP BY clause you've shown is invalid and not necessary.
    Although i am not so clear about his requirement.
    but I think maybe the sql should be added a condition
    d1.person = d2.person

    Code:
    select distinct person, drug_type, drug_date, case .... end drug_type
    from db d1
    where not exists (
     select 1 from db d2 
     where d2.drug_type <> d1.drug_type
     and d1.person = d2.person 
     and d2.date_given between [whatever you mean by "within 2 months of" d1.date_given]
    )

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by brucezepplin View Post
    Hi, I would like to find people that have been taking any 1 of 3 drugs, and then only select those that were on monotherapy (only on one drug at any one given time). The best way to do it is to choose the date the drug was given, and then if there was no other different drug given within 2 months of that date, then they are classes as being on monotherapy. I also need to bring back the drugs that these monotherapy were taking.Here's what I have:

    Code:
    select distinct person, drug_type, drug_date from db
    case when drug_type = 'drug1' then drug1
           when drug_type = 'drug2' then drug2
           when drug_type = 'drug3' then drug3
    else 'error' end as drug_type
    
    /*this is the bit i need help with*/
    
    group by person, 
    case when drug_type = 'drug1' then drug1
           when drug_type = 'drug2' then drug2
           when drug_type = 'drug3' then drug3
    else 'error' end
    so basically I'm struggling with the logic side of things to get all the drug_dates and then pick out the people who's drug_dates for different drug_types are less than 2 months apart. Anyone done anything like this before?
    I agree with what others have said in this thread. An idea to a solution looks like:

    select person
    from T as a
    where not exists (
    select 1 from T as b
    where a.person = b.person
    and a.drug_date <= b.drug_date + 2 months
    and drug_type <> b.drug_type
    union all
    select 1 from T as b
    where a.person = b.person
    and a.drug_date >= b.drug_date - 2 months
    and drug_type <> b.drug_type
    )

    untested
    --
    Lennart

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by lelle12 View Post
    I agree with what others have said in this thread. An idea to a solution looks like:

    select person
    from T as a
    where not exists (
    select 1 from T as b
    where a.person = b.person
    and a.drug_date <= b.drug_date + 2 months
    and drug_type <> b.drug_type
    union all
    select 1 from T as b
    where a.person = b.person
    and a.drug_date >= b.drug_date - 2 months
    and drug_type <> b.drug_type
    )

    untested
    Oh, I discovered there's a slight problem with the predicates in my query. The idea should hold, but you need to change the exists clause.
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •