# Thread: How to solve such scenario ?

1. Registered User
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)```

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. King of Understatement
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.

3. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
this question is actually the same as your other question (primary_usr, secondary_usr), right?

5. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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 (
) AS u
GROUP
BY society_id```

7. King of Understatement
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```
????

8. Registered User
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
•