Monday, October 28

Miscellaneous Tasks 1


CREATE TABLE COMPLEX(ID int, name varchar(50), dept varchar(50), Manager varchar(50))
 
Insert into COMPLEX(ID,name,dept,Manager) Values(101,'Ranjan','SW','')              
Insert into COMPLEX(ID,name,dept,Manager) Values(102,'Deeksha','SW','101')
Insert into COMPLEX(ID,name,dept,Manager) Values(103,'sham','SW','101')
Insert into COMPLEX(ID,name,dept,Manager) Values(104,'Junaid','SW','101')    
Insert into COMPLEX(ID,name,dept,Manager) Values(105,'Vinay','HQ','')
Insert into COMPLEX(ID,name,dept,Manager) Values(106,'akhilesh','HQ','105')
Insert into COMPLEX(ID,name,dept,Manager) Values(107,'Pranav','HQ','105')

SELECT * from COMPLEX;

Table
COMPLEX
ID
name
dept
Manager
101
Ranjan
SW

102
Deeksha
SW
101
103
sham
SW
101
104
Junaid
SW
101
105
Vinay
HQ

106
akhilesh
HQ
105
107
Pranav
HQ
105

Required Results below format


Required result
Department
Manager
Name
HQ
Vinay
akhilesh


Pranav
SW
Ranjan
Deeksha


sham


Junaid

Solution A:

SELECT 
    Case WHEN ISNULL(SecondTable.Id,0) = 0
         THEN FirstTable.Dept
         ELSE ''
    END As Department, 
    Case WHEN ISNULL(SecondTable.Id,0) = 0
         THEN Manager.Name 
         ELSE ''
    END As  Manager, 
    FirstTable.Name FROM COMPLEX As FirstTable
LEFT JOIN COMPLEX As SecondTable ON
    SecondTable.ID = (SELECT Top 1  COMPLEX.ID
                      FROM COMPLEX 
                      WHERE COMPLEX.Manager = FirstTable.Manager
                        AND COMPLEX.dept = FirstTable.dept
                        AND COMPLEX.Id < FirstTable.Id
                      ORDER BY COMPLEX.ID Desc
LEFT JOIN COMPLEX As Manager ON Manager.Id = FirstTable.Manager  
WHERE FirstTable.Manager <> 0   ORDER BY FirstTable.dept, FirstTable.ID


Solution B:

select name,
Case WHEN seq > 1
THEN ''
else manager
END as Manager,
Case WHEN seq > 1
THEN ''
else dept
END as dept

from
(
select b.name,b.dept,a.name as manager, row_number() over(partition by a.dept order by a.dept desc) seq
from COMPLEX a join COMPLEX b on a.ID=b.Manager
) d