Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to display Maximum date records - Crystal XI

Status
Not open for further replies.
jpeters01

jpeters01

Technical User
Dec 5, 2007
109
0
0
US
I have tried suggestions from other posts like using Report Selection Formula/ Group but I couldn't get them to work. This is a report that displays a schedule build for procedures and every time the schedule is adjusted it is given a new Effective Date. I need to pull the schedule build with the most recent Effective date. This is a sample of the data:
Procedure Date Week# DOW StartTime StopTime
STRESS T 6/13/11
STRESS T 7/11/11 1 MON 8:30 8:35
STRESS T 7/11/11 1 MON 10:00 10:05
STRESS T 7/11/11 1 TUES 8:30 8:35
STRESS T 7/11/11 1 TUES 10:00 10:05
STRESS T 7/14/11 1 MON 9:00 9:05
STRESS T 7/14/11 1 MON 13:30 13:35
STRESS T 7/14/11 1 TUES 9:00 9:05
STRESS T 7/14/11 1 TUES 9:30 9:35
ABLATION 8/3/13 1 MON 13:00 13:05
ABLATION 8/3/13 1 MON 14:00 14:05
ABLATION 8/3/13 1 TUES 15:00 15:05
ABLATION 8/3/13 1 TUES 13:00 13:05
ABLATION 10/2/15 1 MON 8:00 8:05
ABLATION 10/2/15 1 MON 8:30 8:35
ABLATION 10/2/15 1 TUES 16:00 16:05
ABLATION 10/2/15 1 TUES 16:00 16:05

This is what I need to pull:
Procedure Date Week# DOW StartTime StopTime
STRESS T 7/14/11 1 MON 9:00 9:05
STRESS T 7/14/11 1 MON 13:30 13:35
STRESS T 7/14/11 1 TUES 9:00 9:05
STRESS T 7/14/11 1 TUES 9:30 9:35
ABLATION 10/2/15 1 MON 8:00 8:05
ABLATION 10/2/15 1 MON 8:30 8:35
ABLATION 10/2/15 1 TUES 16:00 16:05
ABLATION 10/2/15 1 TUES 16:00 16:05

Thank you for your help!


 
Sort by date Sort by votes
Thanks for the reply...sorry it is not clear. The date you see on the rows is the effective date. If there is more than one effective date I need to pull only the rows with the most recent effective date. The Stress Test shows two effective dates, 7/11/11 and 7/14/11, I only want the rows associated with the 7/14/11. Same with the Ablation test, I only want to pull the rows associated with the most recent effective date which is 10/2/15.

Thanks so much for looking at this!
 
Upvote 0 Downvote
I would think about basing the report on a command instead of directly on a table/s and get rid of the records I don't want that way.
In the example below I'm assuming you have a table called Schedule.
(Commands are created in the Database Expert).

SELECT *
FROM Schedule INNER JOIN
(
SELECT Procedure, Max(Date) AS LastEffectiveDate
FROM Schedule
GROUP BY Procedure
) AS S2 ON Schedule.Procedure = S2.Procedure AND Schedule.Date = S2.LastEffectiveDate

The exact SQL statement that you use depends on your database server but hopefully this conveys the basic idea.
(MS SQL Server it is T-SQL and Oracle PL-SQL.)




Gordon BOCP
Crystalize
 
Upvote 0 Downvote
I had read earlier that this was a possible solution. I have not worked with commands much and had tried to do this earlier, but will try again using your example.

Thank you,
Jan
 
Upvote 0 Downvote
I am a bit lost...I think because I need to link to so many tables to get from the Procedure name (proname.name) to the Effective date (Effdate.eff_datetime). This is the SQL from the Crystal report I have been working on...

SELECT
"proname"."name",
"proname"."abbr",
"res"."res_id",
"respool"."respool_id",
"respoolsched"."dow_id",
"respoolsched"."num_avails",
"respoolsched"."timestamp",
"respoolsched"."week_number",
"proreslist"."pro_id", "res"."abbr",
"respoolsched"."start_time",
"respoolsched"."duration",
"resunit"."abbr",
"proname"."duration",
"effdate"."eff_datetime",
"protext"."hints"
FROM (((((((("phsprod"."dbo"."resunit" "resunit" LEFT OUTER JOIN "phsprod"."dbo"."pro" "pro" ON "resunit"."resunit_id"="pro"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolunitlist" "respoolunitlist" ON "pro"."resunit_id"="respoolunitlist"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."proreslist" "proreslist" ON "pro"."pro_id"="proreslist"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."proname" "proname" ON "pro"."proname_id"="proname"."proname_id") LEFT OUTER JOIN "phsprod"."dbo"."protext" "protext" ON "pro"."pro_id"="protext"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."respool" "respool" ON "respoolunitlist"."respool_id"="respool"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."effdate" "effdate" ON "respool"."respool_id"="effdate"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolsched" "respoolsched" ON "effdate"."effdate_id"="respoolsched"."effdate_id") INNER JOIN "phsprod"."dbo"."res" "res" ON "proreslist"."res_id"="res"."res_id"
WHERE "res"."abbr" LIKE 'T%' AND "effdate"."eff_datetime" IS NOT NULL

 
Upvote 0 Downvote
Here's another approach:

Group by Task, sort descending by date

Place this formula in the Report Header and the Group Footer: SHARED STRINGVAR SUPPRESS := 'N'

Place the following formula in your Detail (but change the formatting so it is suppressed)
SHARED STRINGVAR SUPPRESS;
IF ONFIRSTRECORD
THEN SUPPRESS := SUPPRESS
ELSE
IF {TASK} = PREVIOUS({TASK} AND {DATE} < PREVIOUS({DATE})
THEN SUPPRESS := 'Y'
ELSE SUPPRESS := SUPPRESS

Then in the Section Expert for the Detail create this as a Suppression formula:
SHARED STRINGVAR SUPPRESS;
SUPPRESS = 'Y'
 
Upvote 0 Downvote
If you are not too familiar with SQL you may want to stick to suppression but if you play with the SQL you can try the following:

See if the SQL below gives you one row per procedure and the last effective date for that procedure.
(Test it in a new report based on a command which you will find in the database expert)

SELECT
proname.name,
MAX(effdate.eff_datetime) AS LastEffectiveDate
FROM (((((((("phsprod"."dbo"."resunit" "resunit" LEFT OUTER JOIN
"phsprod"."dbo"."pro" "pro" ON "resunit"."resunit_id"="pro"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolunitlist" "respoolunitlist" ON "pro"."resunit_id"="respoolunitlist"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."proreslist" "proreslist" ON "pro"."pro_id"="proreslist"."pro_id") LEFT OUTER JOIN
"phsprod"."dbo"."proname" "proname" ON "pro"."proname_id"="proname"."proname_id") LEFT OUTER JOIN "phsprod"."dbo"."protext" "protext" ON "pro"."pro_id"="protext"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."respool" "respool" ON "respoolunitlist"."respool_id"="respool"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."effdate" "effdate" ON "respool"."respool_id"="effdate"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolsched" "respoolsched" ON "effdate"."effdate_id"="respoolsched"."effdate_id") INNER JOIN "phsprod"."dbo"."res" "res" ON "proreslist"."res_id"="res"."res_id"
WHERE "res"."abbr" LIKE 'T%' AND "effdate"."eff_datetime" IS NOT NULL
GROUP BY proname.name

If the SQL command does work in a new report then you have some choices.
OPTION 1:
You can just add this as a command (again using the Database Expert) to your existing report and link it on the Name field.
Bring in the LastEffectiveDateField to your report and the other records should not show anymore.
Problem with this method (mixing commands with tables) is that it can be very slow.
OPTION 2:
Merge the SQL above into one big command and base a new report off that command.
Below is an attempt at the SQL. Without being able to test it may be missing something.
SELECT
"proname"."name",
"proname"."abbr",
"res"."res_id",
"respool"."respool_id",
"respoolsched"."dow_id",
"respoolsched"."num_avails",
"respoolsched"."timestamp",
"respoolsched"."week_number",
"proreslist"."pro_id", "res"."abbr",
"respoolsched"."start_time",
"respoolsched"."duration",
"resunit"."abbr",
"proname"."duration",
"effdate"."eff_datetime",
"protext"."hints"
FROM (((((((("phsprod"."dbo"."resunit" "resunit" LEFT OUTER JOIN "phsprod"."dbo"."pro" "pro" ON "resunit"."resunit_id"="pro"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolunitlist" "respoolunitlist" ON "pro"."resunit_id"="respoolunitlist"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."proreslist" "proreslist" ON "pro"."pro_id"="proreslist"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."proname" "proname" ON "pro"."proname_id"="proname"."proname_id") LEFT OUTER JOIN "phsprod"."dbo"."protext" "protext" ON "pro"."pro_id"="protext"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."respool" "respool" ON "respoolunitlist"."respool_id"="respool"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."effdate" "effdate" ON "respool"."respool_id"="effdate"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolsched" "respoolsched" ON "effdate"."effdate_id"="respoolsched"."effdate_id") INNER JOIN "phsprod"."dbo"."res" "res" ON "proreslist"."res_id"="res"."res_id"
INNER JOIN
(
SELECT
proname.name,
MAX(effdate.eff_datetime) AS LastEffectiveDate
FROM (((((((("phsprod"."dbo"."resunit" "resunit" LEFT OUTER JOIN
"phsprod"."dbo"."pro" "pro" ON "resunit"."resunit_id"="pro"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolunitlist" "respoolunitlist" ON "pro"."resunit_id"="respoolunitlist"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."proreslist" "proreslist" ON "pro"."pro_id"="proreslist"."pro_id") LEFT OUTER JOIN
"phsprod"."dbo"."proname" "proname" ON "pro"."proname_id"="proname"."proname_id") LEFT OUTER JOIN "phsprod"."dbo"."protext" "protext" ON "pro"."pro_id"="protext"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."respool" "respool" ON "respoolunitlist"."respool_id"="respool"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."effdate" "effdate" ON "respool"."respool_id"="effdate"."respool_id") LEFT OUTER JOIN "phsprod"."dbo"."respoolsched" "respoolsched" ON "effdate"."effdate_id"="respoolsched"."effdate_id") INNER JOIN "phsprod"."dbo"."res" "res" ON "proreslist"."res_id"="res"."res_id"
WHERE "res"."abbr" LIKE 'T%' AND "effdate"."eff_datetime" IS NOT NULL
GROUP BY proname.name
) X on x.name = proname.name
WHERE "res"."abbr" LIKE 'T%' AND "effdate"."eff_datetime" IS NOT NULL

Hope this helps guide you as to one possible approach

Gordon BOCP
Crystalize
 
Upvote 0 Downvote
While I often use Commands in complex reports, unless I am missing something I wouldn't bother in this case (I haven't studied the SQL from your report in any detail so it is possible I am wrong).

I did it as follows as a simple test using the data you provided, copied into a spreadsheet:
[ol 1]
[li]Create a group on the Procedure field and another on the Date field (making sure Date group is by Day and not the default Week. The Group Headers and Footers can be suppressed.[/li]

[li]Add the following formula to the Group Selection Formula

Code: 
{Table.Date} = Maximum({Table.Date},{Table.Procedure})
[/li]
[/ol]

Hope this helps.

Cheers
Pete

 
Upvote 0 Downvote
I like Pete's approach - using a Group Selection Formula is a great idea.

Gordon BOCP
Crystalize
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Question
Date comparison Formula
Replies
2
Views
251
TPG_IT
T
john432
  • Locked
  • Question
Crystal Report
Replies
2
Views
154
john432
john432
D
  • Question
Comparing two Date Ranges
Replies
0
Views
134
doodledump
D
sofseattle
  • Locked
  • Question
Crystal formula for barcode display shows extra characters in date format?
Replies
1
Views
168
BobSuruncle
BobSuruncle
john432
  • Locked
  • Question
Crystal Report Help 1
Replies
2
Views
163
john432
john432

Part and Inventory Search

Sponsor

Back
Top