Difference between first and last record in a group

[arodri]

Hello,

I've seen a couple other posts similar to what I need but still haven't been able to figure this out. Any help would be greatly appreciated!

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.

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!

 

[arodri]

Hello again,

I'm getting the same error and it's highlighting this part of the footer formula:


redim v_CodesToDisplay[distinctcount ({LubeMilage.CardNum})]

 

[briangriffin]

Let's just set the array size to 1000:

redim v_CodesToDisplay[1000];

 

[arodri]

Unfortunately I'm still getting the exact same error but this time it's highlighting this part:

redim preserve v_CodesToDisplay;

 

[briangriffin]

I'll generate a large data set for testing, then work out the kinks. Stay tuned...

 

[briangriffin]

OK, here are the formulas for the main report:

Report Header:

whileprintingrecords;
stringvar array v_CodesToDisplay;
redim  v_CodesToDisplay[1000];
numbervar i := 1;

Group Footer:

whileprintingrecords;
numbervar array v_mileage;
numbervar v_total;

stringVar array v_CodesToDisplay;
numberVar i;
Local numberVar v_CodeGroupMileage;

v_total := v_total + (v_mileage[ubound(v_mileage)] - v_mileage[2]);
v_CodeGroupMileage := v_mileage[ubound(v_mileage)] - v_mileage[2];

If not(v_CodeGroupMileage in 0 to 50000) Then
    (
       v_CodesToDisplay[i] := {Sheet1_.code};
       i := i + 1;
    )
;
v_CodeGroupMileage;

The other formulas are unchanged.

This will only work if the total non-qualifying groups is 1000 or less. If you have more than that, then you're approaching the point at which the logic should be passed to the database server via a stored procedure or command object. That would also speed things up considerably.

 

[arodri]

Hello,

Sorry it took me so long to test those formulas - I really appreciate you taking the time to help me with this.
Unfortunately I think I'll need to figure something out in sql because I tried exactly what you suggested above but I think it's too much info for Crystal to process.
When I ran the report it took a really long time generating all of the data and still at the end, it said "out of memory."

Thanks so much for your help though!