INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Consecutive Reads

Consecutive Reads

(OP)
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

RE: Consecutive Reads

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

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

RE: Consecutive Reads

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Consecutive Reads

(OP)
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,


RE: Consecutive Reads

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

RE: Consecutive Reads

(OP)
Thanks Gordon!

I will try that.

RE: Consecutive Reads

(OP)
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

RE: Consecutive Reads

(OP)
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

RE: Consecutive Reads

Nice work Paul ! that sounds like a very efficient solution

Gordon
Crystalize

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close