Code (Sample Data Staging):
DECLARE @Emp TABLE
(
[EId] INT IDENTITY(1, 1)
, [FN] NVARCHAR(50)
, [LN] NVARCHAR(50)
) ;
DECLARE @EmpPhCont TABLE
(
[EId] INT
, [PhType] VARCHAR(10)
, [PhNum] VARCHAR(16)
, [PhExt] VARCHAR(10)
, [IsMain] BIT
, [CreatedOn] DATETIME
) ;
INSERT INTO @Emp
VALUES
( N'Emp1', N'Emp1' )
, ( N'Emp2', N'Emp2' )
, ( N'Emp3', N'Emp3' )
, ( N'Emp4', N'Emp4' )
, ( N'Emp5', N'Emp5' )
, ( N'Emp6', N'Emp5' ) ;
INSERT INTO @EmpPhCont
VALUES
( 1, 'Home', '111111111', NULL, 0, '2020-01-01 00:00:01' )
, ( 1, 'Mobile', '222222222', NULL, 1, '2020-01-01 00:00:02' )
, ( 1, 'Work', '333333333', NULL, 0, '2020-01-01 00:00:03' )
, ( 2, 'Work', '444444444', '567', 1, '2020-01-01 00:00:04' )
, ( 2, 'Mobile', '555555555', NULL, 0, '2020-01-01 00:00:05' )
, ( 2, 'Mobile', '454545454', NULL, 0, '2020-01-01 00:00:06' )
, ( 3, 'Home', '777777777', NULL, 0, '2020-01-01 00:00:07' )
, ( 3, 'Mobile', '888888888', NULL, 1, '2020-01-01 00:00:08' )
, ( 3, 'Mobile', '12121212', NULL, 0, '2020-01-01 00:00:09' )
, ( 4, 'Work', '101010101', '111', 1, '2020-01-01 00:00:10' )
, ( 4, 'Work', '101010102', '232', 0, '2020-01-01 00:00:11' )
, ( 5, 'Work', '545454545', '456', 0, '2020-01-01 00:00:10' )
, ( 5, 'Work', '456456456', NULL, 1, '2020-01-01 00:00:11' ) ;
Description:
@Emp is the sample Employee table (Unique Employee records).
- EId = Employee Id
- FN = First Name
- LN = Last Name
@EmpPhCont is the sample Employee Phone Contact table (Each Emp from @Emp table can have zero, one, or multiple phone numbers here – unique by Emp/Type).
- PhType = Phone Type (home, mobile, work, and etc)
- PhNum = Phone Number
- PhExt = Phone Extension (mostly available for “Work” PhType)
- IsMain = Is it main contact number. Each employee with a phone num will have exactly 1 record marked as IsMain.
- CreatedOn = Date the record was created
Goal:
To output 1 record per employee with the following Columns
EId | HomeNum | MobileNum | WorkNum | WorkNumExt | MainPhType
Rules:
Return all EId for all records from @Emp, whether they have a @EmpPhCont record or not.
For each emp that has @EmpPhCont record avail, return the newest created PhNum and PhExt for the corresponding PhType, UNLESS an older record for the same Emp/PhType is marked as IsMain = 1 (For any emp, for whichever PhType, if IsMain = 1, always return that PhNum and PhExt value).
Expected Output:
EId HomeNum MobileNum WorkNum WorkNumExt MainPhType
1 111111111 222222222 333333333 NULL Mobile
2 NULL 454545454 444444444 567 Work
3 777777777 888888888 NULL NULL Mobile
4 NULL NULL 101010102 111 Work
5 NULL NULL 456456456 NULL Work
6 NULL NULL NULL NULL NULL
My unsuccessful try:
SELECT [EM].[EId]
, MAX ( IIF([PH].[PhType] = 'Home', [PH].[PhNum], NULL)) AS [HomePhNum]
, MAX ( IIF([PH].[PhType] = 'Mobile', [PH].[PhNum], NULL)) AS [MobilePhNum]
, MAX ( IIF([PH].[PhType] = 'Work', [PH].[PhNum], NULL)) AS [WorkPhNum]
FROM @Emp AS [EM]
LEFT JOIN @EmpPhCont AS [PH]
ON [EM].[EId] = [PH].[EId]
GROUP BY [EM].[EId] ;
Advertisement
Answer
Use ROW_NUMBER()
window function inside a CTE
to get the rows from @EmpPhCont
that you want returned and join this CTE
to @Emp
:
with cte as (
select *,
row_number() over (partition by [EId], [PhType] order by [IsMain] desc, [CreatedOn] desc) rn
from @EmpPhCont
)
select e.[EId],
max(case when c.[PhType] = 'Home' then c.[PhNum] end) HomeNum,
max(case when c.[PhType] = 'Mobile' then c.[PhNum] end) MobileNum,
max(case when c.[PhType] = 'Work' then c.[PhNum] end) WorkNum,
max(case when c.[PhType] = 'Work' then c.[PhExt] end) WorkNumExt,
max(case when c.[IsMain] = 1 then c.[PhType] end) MainPhType
from @Emp e left join cte c
on c.[EId] = e.[EId] and c.rn = 1
group by e.[EId]
See the demo.
Results:
> EId | HomeNum | MobileNum | WorkNum | WorkNumExt | MainPhType
> --: | :-------- | :-------- | :-------- | :--------- | :---------
> 1 | 111111111 | 222222222 | 333333333 | null | Mobile
> 2 | null | 454545454 | 444444444 | 567 | Work
> 3 | 777777777 | 888888888 | null | null | Mobile
> 4 | null | null | 101010101 | 111 | Work
> 5 | null | null | 456456456 | null | Work
> 6 | null | null | null | null | null