Skip to Content
0
Former Member
Dec 27, 2011 at 09:43 PM

Calculated Age Based on Othewise Unneeded Records

32 Views

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?