Selecting data based on time 1

[Laurelin]

I am trying to select records from a table based on a couple of time criteria and I have no idea how to go about this using Crystal Reports XI. I need to create a report that shows all the units that have purchases(date/time) within an 8 hour period but not the purchases that occurred within 30 minutes of each other. All of the data is located in one table.


Sample data:
UNIT DATE TIME
TG5648 12/12/2009 04:50:00
RT3235 12/18/2009 07:45:00
TG5648 12/12/2009 05:30:00
YT3245 12/19/2009 08:43:00
TG5648 12/12/2009 04:55:00

The report should select the 1st and 3rd records in the sample data because they have the same unit number and the date/time is within 8 hours of each other. However it should not select the 5th record because it is under 30 minutes from the first record.

Any advice or tips as to how to accomplish this would be much appreciated!

 

[lbass]

Do you mean ANY 8-hour period on one specific date? Or do you mean during certain regular hours, e.g., 9 to 5? What would you expect to see if there were three records for the same unit, and each one was within 30 minutes of the previous one, but the last one was more than 30 minutes from the first one?

What is the rationale underlying these unusual criteria?

-LB

 

[Laurelin]

What we are trying to determine is if someone makes multiple purchases of fuel within any 8 hour time period during a day. This keeps them from being able to purchase a large amount of fuel on a given day without it going unnoticed. I could think of a few ways for them to get this information other than this but they are require by law to produce this report.

Under your scenario I would expect to see the first and last transactions on the report because those two transactions are more than 30 minutes apart.

 

[lbass]

So what about purchases that are 9 hours apart on the same date--you don't care about that?

-LB

 

[Laurelin]

Anything over 8 hours apart I do not want to see on my report.

 

[lbass]

Okay, first insert a group on date (on change of day) and then a group #2 on unit. Then go to report->selection formula->group and enter:

datediff("m",minimum({table.datetime},{table.unit}), maximum({table.datetime},{table.unit}) >= 30

This would return the relevant units, but would still return interim records that might not meet your criteria, so then go into the section expert->detail->suppress->x+2 and enter:

(
datediff("m",minimum({table.datetime},{table.unit}), maximum({table.datetime},{table.unit}) > 480 and
distinctcount({table.unit},{table.date}) = 2
) or
(
{table.unit} = previous({table.unit}) and
datediff("m", previous({table.datetime}),{table.datetime}) < 30 and
{table.datetime} <> maximum({table.datetime},{table.unit})
)

This should get you close anyway.

-LB

 

[Laurelin]

I am going to give this a shot. I was wondering if it was even possible!

Thank you so much for your help. I will let you know how it turns out.

 

[Laurelin]

I got it working after a little tweaking but I would have never made it work without your help. Thanks again.

 

[Laurelin]

I have had an issue come up with this report that I have created based off this thread. The report is working great when there is only two transactions per day for a single unit. But if there is three or more transactions for a unit the group selection process is not working as needed.

If I have three transactions for a single unit the datediff formula is only returning a value for the first and last transaction. It does not take into consideration the middle transaction and the time difference between it and the other two transactions.

Example:
UNIT DATE TIME DATEDIFF(MINUTES)
TG5648 12/12/2009 07:35:00 AM 439
TG5648 12/12/2009 02:46:00 PM 439
TG5648 12/12/2009 02:54:00 PM 439

I need for the groups to be selected based off of the difference in time for each of the previous transactions in a group. By creating a formula on the detail line I am able to show the time difference between each transaction correctly.

Example:
UNIT DATE TIME DATEDIFF(MINUTES)
TG5648 12/12/2009 07:35:00 AM 0
TG5648 12/12/2009 02:46:00 PM 431
TG5648 12/12/2009 02:54:00 PM 8

However I cannot select the records based on this new formula. I am stumped as to the best way to make this happen.

What I would need the report to do in the case of 3 or more transactions is to select the two records that are within a certain amount of time(180 minutes) between each other. I could live with it selecting all three transactions but usually when there is 3 or more the time difference is too great between the first and last and it is not taking the middle transaction into consideration.

Any thoughts on a way to do this?

 

[lbass]

All you can use is section suppression, but I am unclear on all of the rules that you want applied. Just using the 180 minute guideline, you could use:

datediff("n",{table.datetime},next({table.datetime}) > 180 and
datediff("n",previous({table.datetime}),{table.datetime}) > 180

This would suppress any record that was more than three hours different from the previous and next records.

-LB