Meter Count

[pomster]

my data is as follows using CR8.5 and SQL2000:

meter date
1000 01/01/04
1346 01/02/04
1987 01/03/04
2120 01/04/04

I need to know the actual meter clicks for each record so:

meter date count
1000 01/01/04 0
1346 01/02/04 1346
1987 01/03/04 641
2120 01/04/04 133

How do I do this? do I use a running total of some kind or what? Your help is appreciated.

Regards,


David

 

[Katy44]

Not sure what you are trying to do - should the second 'count' be 346? If so, you probably need to use the 'Previous' keyword, a formula of something like:

{meter} - Previous({meter})

You may also need to use WhilePrintingRecords; at the top of the formula, I'm never sure about these things!

 

[lbass]

Expanding upon Katy's suggestion, first group on {table.meterID} or a field like it. Then create a formula {@count}:

if onfirstrecord or
{table.meterID} <> previous({table.meterID}) then 0 else
{table.meterreading} - previous({table.meterreading})

-LB

 

[pomster]

Back to this dilemma again, thanks Katy and LB both work fine. However, I need to sum the meter readings based on an operator as follows:

meter date count Op1 PrevOp
1000 01/01/04 0 t1
1346 01/02/04 346 t1 t1
1987 01/03/04 641 t2 t1
2120 01/04/04 133 t1 t2

I need the sum of count based on the PrevOp field so that t1 is assigned 346 + 641 and t2 is assigned 133. I have tried to use a summary but using both LB's and Katy's formulae a summary could not be created.

Any Ideas,


Thanks,


David