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