How to find out the date of first records of each org should be at least

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  January 19, 2011 at 5:40 am

  #230509

  Hi,

  please suggest me that how to find out such type of records i.e.

  ''The effective date of the first record of each organization should be at least as early as its first officer record'' from the below SQL script.

  My SQL is as below also I request you to please see my sql below suggestions...

• SELECT cixo.ixo_rlt_code Role,

  coe.org_club_id_ext org_ID,

  cie.ind_membership_id_ext Member_ID ,

  cixo.ixo_start_date Start_Date,

  cixo.ixo_end_date End_Date,

  ROW_NUMBER () OVER (PARTITION BY cie.ind_membership_id_ext,

  coe.org_club_id_ext

  ORDER BY cie.ind_membership_id_ext,

  coe.org_club_id_ext,cixo.ixo_rlt_code,

  cixo.ixo_start_date) sequence_nbr

  FROM co_individual_x_organization AS cixo

  JOIN co_individual AS ci ON ci.ind_cst_key = cixo.ixo_ind_cst_key

  JOIN co_individual_ext AS cie ON cie.ind_cst_key_ext = cixo.ixo_ind_cst_key

  JOIN co_organization_ext AS coe ON coe.org_cst_key_ext = cixo.ixo_org_cst_key

  JOIN co_organization as co ON co.org_cst_key = coe.org_cst_key_ext

  AND (cixo.ixo_start_date>co.org_date_founded AND cixo.ixo_end_date>co.org_date_founded)

  WHERE ((cixo.ixo_rlt_code IN('Club President',

  'Club Secretary',

  'Club Executive Secretary/Director',

  'District Membership Development Chair')

  AND (cixo.ixo_end_date > '6/30/2004' OR cixo.ixo_end_date IS NULL))

  OR cixo.ixo_rlt_code = 'District Governor (DG)')

  AND cixo.ixo_start_date <> cixo.ixo_end_date

  AND cixo.ixo_delete_flag = 0

  AND ci.ind_delete_flag = 0

  ORDER BY Member_ID,org_ID,Role,sequence_nbr

Regards,

Kiran R