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.