Consecutive Reads 1

[PaulWiersma]

Hi All,

Help!

I have a report that is required to capture 4 consecutive read types. For example;

There are three read types, there could be more but we only care about estimate reads in this case.
Actual Read
Estimate
Final

Each reading date will have one read type assigned. I need to find all accounts with 4 consecutive estimate reads. Only 4 consecutive estimate reads, no others are required.

Assuming I need a redim preserve and a loop.

Thanks,
PW

 

[PeterDimitrov]

Is it possible to have repeating values or out of sequence types ? For example is it possible to have :
Actual Read
Actual Read
Final

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[SkipVought]

Hi,

So do you have more than Read Type and Reading Date in this table?

Exactly what do you mean by 4 consecutive 'Estimate' Read Type: simply ordered by Reading Date?

Do you have SQL code?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PaulWiersma]

correct Peter,

I would only need to see

estimate
estimate
estimate
estimate

for the last four read dates.

we could disregard as soon as one of the last four consecutive reads was not an estimate.

Skip: the balance of the fields are negligible as I can pull every column as long as I have the last four consecutive reads being estimates.

I would assume doing a loop + redim preserve on the read type where readtype = 'estimate' and readtype[1] = 'estimate' and exit on the 4th redim would do it. I could then filter on the loop = [estimate, estimate, estimate, estimate]

I'm just not experienced enough with redim preserve and loops.

Thanks,

 

[CrystalizeCanada]

Assuming you are only interested in the last four readings for each AccountNo.
And only interested in AccountNos where the last four readings are Estimates.
One approach would be to limit the records to the last four readings for each AccountNo.
I would do that using a Command.
The command would return the last 4 readings per AccounNo.

So imagine that you have a table called Readings with the following fields:

ReadingId​

ReadingDate​

ReadType​

ReadingValue​

AccountNo​

A record might look like this

1​

1/1/2016​

Estimate​

100​

99​

The following command would be added to the Database Expert and the report would be based on this command (instead of using the Readings Table):
SELECT *
FROM Readings R
WHERE ReadingId In (
SELECT TOP 4 ReadingId
FROM Readings
WHERE AccountNo = R.AccountNo
ORDER BY ReadingDate DESC
)
NOTE: The syntax may differ based on the flavour of SQL you are using

The result should be that you now have the last 4 records per AccountNo.
Create a Group based on AccountNo
Create a formula called @Estimates:
If {Readings.ReadType} = "Estimate" then 1 else 0
Create a Sum of @Estimates per group.
Create a Group Record Selection formula to filter the Groups:
Sum ({@Estimates}, {Readings.AccountNo}) = 4

You will now only have Customers show in the report where the last 4 Readings are Estimate Readings.

If this works this may be a less complicated approach than using a looping formula.





Gordon BOCP
Crystalize

 

[PaulWiersma]

Thanks Gordon!

I will try that.

 

[PaulWiersma]

Hi Gordon,

The query worked like a charm. I had to rewrite it for SQLLITE which uses LIMIT N after the order by instead of TOP N in the select, but worked the same none the less. It's not my favorite syntax.

I now have a different environment to apply this concept to. It's a Visual FoxPro dbf file. The software environment is using Crystal reports viewer to open the .rpt and the rpt's are using file connections and not ODBC driver connections, this is a limitation to the software. Unfortunately this means I can't use a SQL command (sigh).

Now thinking a while x do y loop with an exit while on the fourth reading id can be used in the report selection where the loop result is <= 4. I can then use the summary and group by concept from your original suggestion.

Thoughts?

I appreciate the help,
Paul

 

[PaulWiersma]

Hi Gordon,

I figured it out. I sorted the data by read date descending and created a redim preserve on the read type. Then I created a formula with the redim preserve startswith 'ES, ES, ES, ES,' and used it to suppress the undesirables.

Thanks,
Paul

 

[CrystalizeCanada]

Nice work Paul ! that sounds like a very efficient solution

Gordon
Crystalize