SQL Server 2014 query with multiple conditions

Question

I've got an SQL query with multiple conditions, for the given Source(listed below) I need to get the result set as Final (listed below) with the following condition:

• Condition 01: For the active site, contact with ‘RO’ as contact role should be taken
• Condition 02: For the inactive site, contact with ‘Owner’ as contact role should be taken
• Condition 03: If the active site doesn't have contact with ‘RO’ as contact role, then contact with 'Owner' as contact role should be taken
• Condition 04: If the inactive site doesn't have contact with ‘Owner’ as contact role, then contact with 'RO' as contact role should be taken

• Condition 05: If the active site/inactive doesn't have contact with ‘RO’ or ‘Owner’ as contact role, then contact with ‘Operator’ as contact role should be taken

• Condition 06: Only for active site avoid 'XYZ' contact with 'RO' as contact role and pick another contact with 'RO'as contact role, if the site doesn't have 'RO' then pick 'Owner' or 'operator' as contact role

• Condition 07: If the site (active/inactive) doesn't have any contacts, then those sites should have 'NULL' values in the contact field.

Can we include all the above conditions in one single query (without subqueries if possible) as original data set records are more than 20K?

Source 
======

Site_Status Site_id  Site_Contact  Contact Role 
Active      123      Lilly         Owner
Active      123      Elan          RO
Inactive    345      Rose          Owner
Inactive    345      Jack          RO
Active      678      Robert        Owner 
Inactive    912      Linda         RO
Active      234      Nike          Operator 
Inactive    456      Frank         Operator
Active      808      XYZ           RO
Active      808      Kelly         Owner
Active      999      XYZ           RO
Active      999      Debbi         Operator 
Active      122                
Inactive    188              


Final
======
Site_Status Site_id Site_Contact    ContactRole
Active      123     Elan            RO
Inactive    345     Rose            Owner
Active      678     Robert          Owner
Inactive    912     Linda           RO
Active      234     Nick            Operator
Inactive    456     Frank           Operator
Active      808     Kelly           Owner
Active      999     Debbi           Operator 
Active      122     NULL            NULL
Inactive    188     NULL            NULL

Thanks in advance!

Answer 1

You can use a table variable and populate it gradually, but it won't be a single query (it is a ggod candidate for a stored procedure). A single query with subqueries can be created using union (a rather big one):

select * from [Source]
where [Contact Role] = 'RO' and Site_Status = 'Active'
union
select * from [Source]
where [Contact Role] = 'Owner' and Site_Status = 'Inactive'
union
select * from [Source] s1
where [Contact Role] = 'Owner' and Site_Status = 'Active'
and not exists(
    select 1 from [Source] s2 where s1.Site_Id = s2.Site_id 
    and s2.Site_Status = 'Active' and s2.[Contact Role] = 'RO'
)
union
select * from [Source] s1
where [Contact Role] = 'RO' and Site_Status = 'Inactive'
and not exists(
    select 1 from [Source] s2 where s1.Site_Id = s2.Site_id 
    and s2.Site_Status = 'Inactive' and s2.[Contact Role] = 'Owner'
)
union
select * from [Source] s1
where [Contact Role] = 'Operator'
and not exists(
    select 1 from [Source] s2 where s1.Site_Id = s2.Site_id 
    and (s2.[Contact Role] = 'Owner' or s2.[Contact Role] = 'RO')
)

Answer 2

Something like this... using a window function and conditional sorting.

declare @YourTable table (Site_Status varchar(64), Site_id int, Site_Contact varchar(64), ContactRole varchar(64))
insert into @YourTable
values
('Active',123,'Lilly','Owner'),
('Active',123,'Elan','RO'),
('Inactive',345,'Rose','Owner'),
('Inactive',345,'Jack','RO'),
('Active',678,'Robert','Owner'),
('Inactive',912,'Linda','RO'),
('Active',234,'Nick','Operator'),
('Inactive',456,'Frank','Operator')

select
    t.*
from @YourTable t
inner join
    (select 
        Site_id
        ,Site_Status
        ,ContactRole
        ,Active = row_number() over (partition by Site_id, Site_Status order by case 
                                                                                    when Site_Status = 'Active' and ContactRole = 'RO' then 1
                                                                                    when Site_Status = 'Active' and ContactRole = 'Owner' then 2
                                                                                    when Site_Status = 'Active' and ContactRole = 'Operator' then 3
                                                                                end)
        ,InActive = row_number() over (partition by Site_id, Site_Status order by case
                                                                                    when Site_Status = 'InActive' and ContactRole = 'Owner' then 1
                                                                                    when Site_Status = 'InActive' and ContactRole = 'RO' then 2
                                                                                    when Site_Status = 'InActive' and ContactRole = 'Operator' then 3
                                                                                end)
    from @YourTable) x on 
    x.Site_id = t.Site_id 
    and x.Site_Status =  t.Site_Status 
    and t.ContactRole = x.ContactRole 
    and Active = 1 
    and InActive = 1