Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: How to get expecting result on given query

    Good day All,

    i have following query and the result

    Code:
    SELECT	aff_party_id,
    	NULL AS in_rep_nt_logon	,	
    	(USER_ID) as id,
    	SALESREP.effectivedate
    FROM tableA BF_ROLE
    INNER JOIN tableB ON SALESREP.network_logon = BF_ROLE.rep_nt_logon
    WHERE BF_ROLE.primary_owner = 'FALSE'
    AND BF_ROLE.aff_party_id in (SELECT dISTINCT aff_party_id FROM 
    tableA )
    ORDER BY SALESREP.effectivedate DESC
    result :
    Code:
    aff_party_id,|in_rep_nt_logon|(USER_ID)|effectivedate
    1002	NULL	2053	2008-06-06 00:00:00.000
    10000	NULL	2364	2008-01-24 00:00:00.000
    10000	NULL	988	2007-02-27 00:00:00.000
    10000	NULL	908	2006-05-04 00:00:00.000
    10000	NULL	908	2005-09-01 00:00:00.000
    10000	NULL	988	2005-07-30 00:00:00.000

    the expecting result i would like to get

    Code:
    1002	NULL	2053	2008-06-06 00:00:00.000
    10000	NULL	2364	2008-01-24 00:00:00.000
    my question is how do i make get the result only having record with distinct aff_party_id by maximum effective date ?

    thank you for your guidance !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT bf_role.aff_party_id
         , NULL             AS in_rep_nt_logon 
         , bf_role.user_id  AS id
         , salesrep.effectivedate
      FROM bf_role
    INNER 
      JOIN ( SELECT network_logon
                  , MAX(effectivedate) AS max_date
               FROM salesrep 
             GROUP
                 BY network_logon ) AS m
        ON m.network_logon = bf_role.rep_nt_logon
    INNER 
      JOIN salesrep 
        ON salesrep.network_logon = bf_role.rep_nt_logon
       AND salesrep.effectivedate = m.max_date
     WHERE bf_role.primary_owner = 'FALSE'
    ORDER 
        BY salesrep.effectivedate DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    Thank you r937 ....i still get the same aff_party_id display in result ....i have to fix this by using Cursor

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you don't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since the code you originally posted doesn't seem to be syntactically correct, could you post the code that you are actually using?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •