I'm in maintenance/reliability and I've got a lot of asset data that builds as we replace them. I need have a report that demonstrates age of asset at the date pulled, by month.
Example of Data:
DatePulled Location ID
8/18/2009 5 54C
10/6/2009 5 52B
3/4/2010 5 24C
5/11/2010 5 69C
5/22/2010 5 597B
9/24/2010 5 29B
12/4/2010 5 54B
2/7/2011 5 83C
5/30/2009 6 93C
9/8/2009 6 40B
2/6/2010 6 67B
3/2/2010 6 53B
12/7/2010 6 70B
12/7/2010 6 83C
1/27/2011 6 54C
1/31/2011 6 83B
3/9/2011 6 37B
3/9/2009 7 31B
9/19/2009 7 67C
1/14/2010 7 597B
5/11/2010 7 27B
6/2/2010 7 56B
8/14/2010 7 36C
8/22/2010 7 64C
1/15/2011 7 55C
5/4/2011 7 46C
11/25/2009 8 29B
2/5/2010 8 44C
5/21/2010 8 57B
3/10/2011 8 69C
4/14/2011 8 70B
4/17/2011 8 58B
6/1/2011 8 29B
10/18/2011 8 39B
10/24/2011 8 55C
12/5/2011 8 53B
I've grouped by location, used datediff(next(table.date)-(table.date)) with some logic to make sure it was looking at the same location and it works fine for getting the age for the range that I have queried. The problem I have is if I only want by month data for 2011, I need to pull every asset change in 2011 AND that location's previous pull, no matter when that happened. In the above data if I only wanted 2011 info, then I'd have to pull the 12/4/2010 for position 5, 12/7/2010 for 6, 8/22/2010 for 7 and 5/21/2010 for 8. To calculate the ages correctly, I need the last pull of 2010 and all of 2011 data.
I assume this is best done on the SQL side before it ever really gets to crystal, but don't know where to start.
Any ideas or words of advice?