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

    Unanswered: How to solve such scenario ?

    Good day All,

    i have a scenario as below

    System A
    ---------
    Each society contains 2 persons in charge, one for President, another for vice-president

    Code:
    CREATE table society
    (
    	Society_id int
    	society_name varchar(50), 
    	network_logon int, 
    	position_id int
    
    )
    
    Data :
    222 |IT Society | peter_perilli |10000    (President)
    222 | IT Society | Vincent_Cantonna |20000 (VP)
    Assume user login with NT logon and linked to user table

    Code:
    CREATE table user
    (
    	userid int, 
    	user_name varchar, 
    	network_logon int,
                 society_id
    	effective_date
    	
    )
    
    data :
    123 | peter | peter_perilli|2008-10-11
    456 | vincent | Vincent_Cantonna|2008-10-12
    789 | danny | Danny_camos|2008-10-15
    Now, System B need to consume data of System A.

    System A will provide "network_logon" from society table, and i need to update back System A's ExistingUser table
    as show below

    Code:
    CREATE table Temp_table_from_System_B
    (
    	society_id int,	
    	network_logon int ,
    	position_id int, 
    	primary_flag bit
    	
    )
    
    Data :
    
    222|peter_perilli|10000|TRUE
    222|Vincent_Cantonna|20000|FALSE
    Final result suppose to gain

    Code:
    CREATE table ExistingUser
    (
    	Society_id int,
    	President_id int, 
    	VP_id int 
    	 
    	
    )
    
    Final Result to gain (to get the userid of president and VP):-
    Data :
    222|123|789
    the challenge part here is to implement primary_flag or MAX effective.
    if primary flag = true, get the userid belong to specified user .
    if primary flag = false, get the userid having maximum effective date

    from the data of Temp_table_from_System_B
    Code:
    222|peter_perilli|10000|TRUE
    222|Vincent_Cantonna|20000|FALSE
    peter_perilli is set to be primary, so the ExistingUser record start add as 222|peter_perilli
    Vincent_Cantonna is not set as primary, so it should query user table and get the latest effective date userid , which is Danny_camos now so end up result become 222|123|789




    my tried code :-

    Code:
    SELECT T.society_id
        	(CASE WHEN primary_flag = 1 THEN (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000)
        	 ELSE (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000 order by effective_date)
        	 END ) AS President_id,
    	(CASE WHEN primary_flag = 1 THEN (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000)
    	    	 ELSE (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000 order by effective_date)
        	 END ) AS VP_id,
    FROM 	Temp_table_from_System_B T
    INNER JOIN [USER] U ON T.society_id =U.society_id
    
    
    UNION ALL
    
    SELECT T.society_id
        	(CASE WHEN primary_flag = 1 THEN (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000)
        	 ELSE (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 20000 order by effective_date)
        	 END ) AS President_id,
    	(CASE WHEN primary_flag = 1 THEN (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 10000)
    	    	 ELSE (SELECT top 1 userid from [user] WHERE network_logon = T.network_logon AND POSITION_ID = 20000 order by effective_date)
        	 END ) AS VP_id,
    FROM 	Temp_table_from_System_B T
    INNER JOIN [USER] U ON T.society_id =U.society_id
    above query give me 2 records, how can i make it only 1 record.means take either side of president_id and vp_id



    kindly take note that position_id is to check whether he should be preseident(10000)/VP(20000)



    anyone expert from here to give me some advice or better solution ? thanks for your guidance !
    Last edited by alvincks; 10-26-08 at 16:21.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I know you've spent a decent amount of time on this, however I only made it a third of the way down before giving up. There are errors in your create table statements, and your sample data does not match the tables. Please could you review and correct? I suspect others have been put off responding because of these.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    Hi Pootle Flump, Appreciated your response !

    i need sometimes to review table statement..
    long story short ...
    if i using union all , it give me result of 2 rows

    222|123|NULL
    222|NULL|789

    how should i make it just provided one row by combine president and VP id.
    by getting final result of
    222|123|789

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this question is actually the same as your other question (primary_usr, secondary_usr), right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    74
    Hi r937, it is almost ....when i try to put it in scenario .....i do not know how to get just one record with president and VP 's userid

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i did not look at your queries, they seem inordinately complex

    however, if you have a UNION which produces these two rows --

    222|123|NULL
    222|NULL|789

    and all you want to do is collapse them into one row --

    222|123|789

    then the answer is this --
    Code:
    SELECT society_id
         , MAX(President_id)
         , MAX(VP_id)
      FROM (
          insert your UNION query here
           ) AS u
    GROUP
        BY society_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The same logic as Rudy, however looking at your original table it looks like your data is normalised (Rudy and I will disagree on this but nevermind ).

    Code:
    SELECT society_id
    , pres = MAX(CASE WHEN position_id = 10000 THEN network_logon)
    , vice_pres = MAX(CASE WHEN position_id = 20000 THEN network_logon)
    FROM society
    GROUP BY society_id
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2004
    Posts
    74
    thanks Both r937 and pootle,

    the challenge is
    if primary flag = true, get the userid belong to specified user .
    if primary flag = false, get the userid having maximum effective date

    i still need to take care such condition ....by the way, thanks for the guidance ....it is great workarounds

Posting Permissions

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