Post reply

The date of first records should be at least as early its first officer record

  • January 19, 2011 at 4:59 am

    #243825

    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 aslo 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

  • January 19, 2011 at 5:07 am

    #1275209

    Please don't post multiple threads for one problem.

    No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1049894-391-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply