How do you get the most recent date of a field? 1

[oxygen101]

I am seeking assistance in figuring out how to pull the most recent date that a work order was completed. The issue- we have multiple completed_dates for work orders but I need to get the most recent of those dates for a report.

I have tried-- max(wl.complete_date) but I get an error stating Not a single group application.

Any other suggestions?

Please help.

Thanks.

 

[oxygen101]

That error is from testing out a view but I am trying to get this resolved through Crystal Reports.

Thanks again.

Sonya

 

[lbass]

Are you looking for the most recent date for a particular group? If so, you can use allow all dates in your record selection, but then in the GROUP selection, use a formula like this:

{table.date}= maximum({table.date},{table.groupfield})

Another alternative is to create a SQL expression {%maxdate} like this:

(
select max(`date`)
from table A
where A.`groupfield` = table.`groupfield`
)

Then in the record selection formula area, use:

{table.date} = {%maxdate}

-LB

 

[oxygen101]

Thank you for the fast response. Would I need to use a group? I already have a particular group of work orders that show work order numbers, start_date, cleared_date, and average close time between start_date and closed_date(excluding weekends). The problem is that because there are a number of closed_dates to a work order I am getting every closed_date associated to that work order. I only want to show one work order and the most recent closed date because that is truely when the work order was completed.

Sorry and thanks.

 

[lbass]

I just meant for you to substitute the field you wanted to see the most recent date for. It sounds like you want the most recent date per workorder, so in the SQL expression, subsitute that field for the groupfield. If you haven't created a SQL expression before, you should know that you need to substitute the actual field names and the table name, something like:

(
select max(`complete_date`)
from wl A
where A.`workorder` = wl.`workorder`
)

...assuming your table is "wl" and that workorder is the name of that field. You also need to adapt the punctuation based on your datasource. When you create the SQL expression, add the workorder field from the field list, and notice how it is punctuated and then use that convention throughout. Do not use a table name within the max() summary if you are using any version above 8.5. If you are using 8.5 or lower, you can use: max(A.`complete_date`). You should always specify your CR version, by the way.

-LB

 

[oxygen101]

Thank you again. By the way, my Crystal Version is 9. This is what I tried. I build the SQL expression in Crystal.

(
select max(`complete_date`)
from wo_labor wl
where wl.`wo_id` = wo.`wo_id`
)

I am getting an invalid character error when I check the code.

Any ideas?

 

[oxygen101]

I also want it to give me only the most recent complete_date only for a particular assigned_id group. I tried this same bit of code by creating it in the view first but it was unsucessful because it gives me the most recent complete_date for everything vs. only for the assigned_id group. I also tried this:

SELECT MAX (wl.complete_date)
FROM wo_labor wl
WHERE wo.wo_id = wl.wo_id
AND wl.assigned_group_id = '3621') AS complete_date


This still didn't work. Thanks again for any ideas.

 

[lbass]

Are you using more than one tablein your report? If so, what are they and how are they linked?

-LB

 

[oxygen101]

I am only using two tables and they are linked by wo.wo_id = wl.wo_id. It is a work_order and work_labor.

 

[lbass]

Please go to database->show SQL query and copy your query and paste it into this thread. Also please show some sample data at the detail level (but with existing groups), and be sure to label with both table and field names like {table.field}. Identify what fields are grouped on as well.

-LB

 

[oxygen101]

Copy of SQL Query in view

SELECT category_id,
wo.prord_number || '-' || wo.prord_issue AS preord_number, wo.status,
wo.creation_datetime AS created_date, wl.assigned_group_id,
wl.start_time, wl.stop_time, wo.type_id, wl.complete_date
/*--comment out- this sql added to Crystal report
(SELECT MAX (wl.complete_date)
FROM wo_labor wl
WHERE wo.wo_id = wl.wo_id
and wl.assigned_group_id = '3621') AS complete_date*/
FROM wo_tcom wo,
wo_labor wl
WHERE wo.wo_id = wl.wo_id(+)
AND wo.type_id IN ('IP', 'DA', 'LP')
AND wl.assigned_group_id = '3621'

Copy of data- I copied this into Excel in order to copy here. This can be copied back into Excel to view it better.

CATEGORY_ID PREORD_NUMBER STATUS CREATED_DATE ASSIGNED_GROUP_ID START_TIME STOP_TIME TYPE_ID COMPLETE_DATE
2 47174-1 0 1/28/2009 2:40:56 PM 3621 DA
2 47173-1 0 1/28/2009 2:21:16 PM 3621 DA
2 47173-1 0 1/28/2009 2:21:16 PM 3621 DA
2 47168-2 0 1/28/2009 12:23:33 PM 3621 DA
0 46997-1 2 1/21/2009 11:50:03 AM 3621 1/28/2009 1:00:00 PM 1/28/2009 2:00:00 PM DA 1/27/2009 2:00:00 PM
0 46997-1 2 1/21/2009 11:50:03 AM 3621 1/28/2009 1/28/2009 DA 1/27/2009 4:12:58 PM

Let me know if you need more information. I want the most recent complete-date but only for assigned_group_id 3621. I have tried putting the max sql expression in the view and also tried it through Crystal but I am still unable to get it working either way. If there are multiple assigned_id for the specific group, I only want to see the most recent date for that group not just the most recent date for any complete_date.

Thanks again. I think its something small but I have been staring at it too long.

 

[lbass]

I'm a little confused. I wanted to see what is shown within CR->database->show SQL query. Is this what I'm seeing? Why are you selecting on assigned_group_ID? Is this your intent or is this your group field and you just did that to get a small sample? What ARE your group fields?

-LB

 

[oxygen101]

My apologies. The SQL query is from the view I built within the database not through Crystal. The SQL query I am using within Crystal is

(
select max(`complete_date`)
from wo_labor wl
where wl.`wo_id` = wo.`wo_id`
)

Using this exact SQL query gives me errors.

My only group field is by created_date, monthly.

I am new to this so thank you for your patience.

 

[lbass]

No, now you are showing me the SQL expression. In CR, go to database->show SQL query and copy what is there. Also please ALWAYS identify fields by both table and field. I don't know what table created date is from.

-LB

 

[oxygen101]

Thank you for indicating to me where I need to go. I was not aware that you could get the SQL query that way.


SELECT "UNM_RPT_WK_ORDS_TUTI"."PREORD_NUMBER", "UNM_RPT_WK_ORDS_TUTI"."COMPLETE_DATE", "UNM_RPT_WK_ORDS_TUTI"."CREATED_DATE"
FROM "PINN_CUSTOM"."UNM_RPT_WK_ORDS_TUTI" "UNM_RPT_WK_ORDS_TUTI"
WHERE "UNM_RPT_WK_ORDS_TUTI"."CREATED_DATE">=TO_DATE ('01-12-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Thank you again.

Sonya