0

Hi I am using SQL DB2 and have a study window of 2005-01-01 to 2012-12-31, where various people have lived at an address(s) for varying amounts of time e.g.

Person      Date_birth       Address     Start         End
1           1973-06-01       312a        2001-01-01    2006-04-05
1           1973-06-01       104c        2008-04-11    2013-01-02
2           1989-11-26       56e         2006-11-07    2011-10-31

Where for each person I need to know how many days they have spent being a certain age whilst they have lived at their address given in the data, and days are only counted if they exist in the study window.

So person 1 for example spent 120 days being 27 years old between 2001-01-01 and 2001-06-01, then 365 days being 28 years old between 2001-06-01 and 2002-06-01 etc etc.

I can obviously determine how old each person was at the start of each new address, but then how to determine subsequent birthdays while they are living at each valid address. Does anyone have any logical insight as to where I could start. Not necessarily looking for code.

Dhanish Jose
  • 739
  • 1
  • 8
  • 19
brucezepplin
  • 9,202
  • 26
  • 76
  • 129

1 Answers1

0

Try this:

This is a simple sql query to find difference in days between two dates.

SimpleDateFormat format=new SimpleDateFormat("dd/MM/yyyy");
Date myDate=null,myDate1=null;
myDate=format.parse(oldDate);
myDate1=format.parse(newDate);
int diffInDays = (int)( (myDate1.getTime() - myDate.getTime())/ (1000 * 60 * 60 * 24) );
ravibagul91
  • 20,072
  • 5
  • 36
  • 59
  • currently I can use a simple year(Start) - year(Date_birth) to determine age when first moved to address, but I need to know how long they spent being that age at the address, as well as for subsequent years of age. – brucezepplin Jan 05 '14 at 13:45
  • use oldDate=Date when they start living on that address newDate=current Date – ravibagul91 Jan 05 '14 at 13:48