Hello,
I posted this same post on the crystal reports forums but in the end I had too many records for it to work in Crystal. So I think what I need to do needs to be done first in SQL server.
I get milage records (listed below) from a View I created in SQL.
They are ordered by Code and Date. I need to get the difference between the last and first milage record for each Code (not min and max, but first and last according to the date).
For example, for code 084C it would be (26,832-3,594)= 23,238.
I also need to add the condition that if there's only one record for a code (i.e. code: 01BZ), then the difference is just 0.
I will then need to sum all of the 'difference' values, but I should be able to do this in crystal. Below is a smple of my data:
Code Date Item Milage
01BZ 1/6/2011 FS Lube 63,251
01KW 4/26/2010 FS Lube 72,883
01KW 10/19/2010 FS Lube 82,226
084C 2/13/2010 Synthetic Lube 3,594
084C 6/7/2010 Synthetic Lube 11,014
084C 9/24/2010 Synthetic Lube 20,669
084C 12/21/2010 Synthetic Lube 26,832
15JY 2/17/2010 FS Lube 209,198
15JY 4/9/2010 FS Lube 212,578
15JY 6/2/2010 FS Lube 216,148
15JY 8/12/2010 FS Lube 22,688
15JY 9/25/2010 FS Lube 224,229
15JY 10/27/2010 FS Lube 228,162
Thanks again for any suggestions!
I posted this same post on the crystal reports forums but in the end I had too many records for it to work in Crystal. So I think what I need to do needs to be done first in SQL server.
I get milage records (listed below) from a View I created in SQL.
They are ordered by Code and Date. I need to get the difference between the last and first milage record for each Code (not min and max, but first and last according to the date).
For example, for code 084C it would be (26,832-3,594)= 23,238.
I also need to add the condition that if there's only one record for a code (i.e. code: 01BZ), then the difference is just 0.
I will then need to sum all of the 'difference' values, but I should be able to do this in crystal. Below is a smple of my data:
Code Date Item Milage
01BZ 1/6/2011 FS Lube 63,251
01KW 4/26/2010 FS Lube 72,883
01KW 10/19/2010 FS Lube 82,226
084C 2/13/2010 Synthetic Lube 3,594
084C 6/7/2010 Synthetic Lube 11,014
084C 9/24/2010 Synthetic Lube 20,669
084C 12/21/2010 Synthetic Lube 26,832
15JY 2/17/2010 FS Lube 209,198
15JY 4/9/2010 FS Lube 212,578
15JY 6/2/2010 FS Lube 216,148
15JY 8/12/2010 FS Lube 22,688
15JY 9/25/2010 FS Lube 224,229
15JY 10/27/2010 FS Lube 228,162
Thanks again for any suggestions!