Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: incrementing/decrementing a date by a year

    Hi I have two tables A and B

    Table A - fields are Person and Purchase_dt
    Table B - fields are Person, Insurance_Valid(bool), from_dt,to_dt

    Table A simply contains people in the database and their purchase dates, Table B contains the same people, whether their insurance was valid(y/n) and from when and to their insurance was valid.

    My Question:

    I need to pick the first ever purchase date per person from table A and then find out if their insurance was valid or not at the time of the first purchase date using from_dt,to_dt and Insurance_Valid from table B. ThenI need a way to look back 1 year from the purchase date, then 2 years back and so on...

    so far I have:
    select distinct a.Person, Insurance_Valid from
    (select distinct Person, min(Purchase_dt) from Table A
    Group by Person) a
    (select distinct Person, from_dt,_to_dt from table B) b
    on a.person = b.person
    where year(first_dt - 1) between from_dt and to_dt   --this line I need help

    it's the last line I need help with as the syntax doesn't work. If anyone can tell me how to solve this that would be great.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by brucezepplin View Post
    where year(first_dt - 1) between from_dt and to_dt
    where first_dt - 1 year between from_dt and to_dt
    Datetime operations and durations - IBM DB2 9.7 for Linux, UNIX, and Windows
    "It does not work" is not a valid problem statement.

Posting Permissions

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