Selection of a record from a set of records

[vvira1316]

Hi,

I have table in which there are multiple records for a group. Out of these multiple records I've to select a record based on value in a date field.

If a date field is null for a record then that record has to be displayed and rest of the records have to be ignored.

If there is no record with null date value then record with latest date has to be displayed and rest of the records have to be ignored.

LEAD_STUDY_MANAGER:LOAD *;SQL SELECT
     PTCL.ROW_ID As PROTOCOL_ROW_ID,
     PTCL.PTCL_NUM AS PROTOCOL_NUM,
     PTCL.PTCL_NAME AS PROTOCOL_NAME,
     p.name,
     p.pr_emp_id,
     cnt.fst_name,
     cnt.last_name,
     pp.start_dt,
     pp.end_dt,
     pp.role_cd
from
     siebel.s_pt_pos_hst_ls pp,
     siebel.s_postn p,
     siebel.s_contact cnt,
     siebel.s_cl_ptcl_ls ptcl
where
     ptcl.ptcl_num=ptcl.ptcl_name and
     ptcl.ptcl_stat_cd <> 'Cancelled' and
     ptcl.row_id=pp.cl_ptcl_id(+) and
     pp.postn_id= p.row_id(+) and
     p.pr_emp_id=cnt.row_id(+) and
     ptcl.ptcl_type_cd in ('Company Sponsored','CCT','Clinical Pharmacology') and
     pp.role_cd = 'Lead Study Manager';

I'm attaching a screen shot of records and selection explanation for the same.

So in above example I have to show only last record instead of all three. If last record had date (04/01/2012) then the record with 03/02/2014 should be shown.

Any suggestions will be helpful.

Thanks,

Vijay

[Gysbert_Wassenaar]

I suggest you post some sample data. Preparing examples for Upload - Reduction and Data Scrambling

[vvira1316]

Grouping based on following fields

Product

Study Code

Status

Type

Managed by

[maxgro]

if I understand the rule is (in 5 fields group)

- if there is a null date in a group use that record and discard other in the group

- else use the record with max date  and discard other in the group

see attachment, I pointed out the records to keep in FlagOK field

the script between  "// start check" and  "//end check" is just for verification, you can comment it