Selecting only the record with the max date in each group

building reports crystal reports
1

I have found many related posts and tried several approaches but none are working and I am still a bit confused, so please be patient with me…

In each group, I need my report to return only the record with the greatest date. My current SQL query is below. As you can see, I am grouping by {Job.Job}; for each job, I want only the record with the maximum date in {Job_Operation.Sched_End}.

I know that there are probably several ways to do this. Please give me the easiest one! Thanks!!

SELECT “Job”.“Job”, “Job”.“Sched_Start”, “Job_Operation”.“Vendor”, “Customer”.“Customer”, “Job”.“Part_Number”, “Delivery”.“Promised_Date”, “Job_Operation”.“Status”, “Job_Operation”.“Sched_End”
FROM (“TECH”.“dbo”.“Delivery” “Delivery” INNER JOIN (“TECH”.“dbo”.“Job_Operation” “Job_Operation” INNER JOIN “TECH”.“dbo”.“Job” “Job” ON “Job_Operation”.“Job”=“Job”.“Job”) ON “Delivery”.“Job”=“Job”.“Job”) INNER JOIN “TECH”.“dbo”.“Customer” “Customer” ON “Job”.“Customer”=“Customer”.“Customer”
WHERE (“Job”.“Sched_Start”>={ts ‘2013-07-01 00:00:00’} AND “Job”.“Sched_Start”<{ts ‘2013-07-19 00:00:01’}) AND “Job_Operation”.“Status”=‘O’
ORDER BY “Job”.“Job”

Matteo

Matteo_Luccio (BOB member since 2013-07-30)

2

Hi Matteo,

Using Group selection formula you can acheive your requirement.
Create a group on Job filed and then in Group selection expert write logic as below,

{Job.Sched_Start} = Maximum ({Job.Job}, {Job.Sched_Start})

HTH

–Praveen G

PraveenGuntuka :india: (BOB member since 2013-07-10)

3

Thanks, I had already tried that. However, when I tried to save the group selection formula, I got an error message: it said “There must be a group that matches this field.” and highlighted everything in the formula to the right of the equal sign.

Here’s my formula:

{Job_Operation.Sched_End} = Maximum ({Job.Job}, {Job_Operation.Sched_End})

and here’s my SQL statement:

SELECT “Job”.“Job”, “Job”.“Sched_Start”, “Job_Operation”.“Vendor”, “Customer”.“Customer”, “Job”.“Part_Number”, “Delivery”.“Promised_Date”, “Job_Operation”.“Status”, “Job_Operation”.“Sched_End”
FROM (“TECH”.“dbo”.“Delivery” “Delivery” INNER JOIN (“TECH”.“dbo”.“Job_Operation” “Job_Operation” INNER JOIN “TECH”.“dbo”.“Job” “Job” ON “Job_Operation”.“Job”=“Job”.“Job”) ON “Delivery”.“Job”=“Job”.“Job”) INNER JOIN “TECH”.“dbo”.“Customer” “Customer” ON “Job”.“Customer”=“Customer”.“Customer”
WHERE (“Job”.“Sched_Start”>={ts ‘2013-07-01 00:00:00’} AND “Job”.“Sched_Start”<{ts ‘2013-07-19 00:00:01’}) AND “Job_Operation”.“Status”=‘O’
ORDER BY “Job”.“Job”

As you can see, I am grouping on {Job.Job}

Thanks,

Matteo

Matteo_Luccio (BOB member since 2013-07-30)