I have below table A
Empl_id Add_type Ph_no
66 H 44-14424
66 F 44-1442
66 C 67-384-295
66 B (8888) 8440
66 H 608 6775
57 B 12345
57 C 78910
58 C 9988
I need to get distinct emp_id and phone_number where if Address =B then ph_number else null as phone_number
my output should be as below where SQL picks Add_type as B and pulls ph_no else shows null
Empl_id Ph_no
66 (8888) 8440
57 12345
58 NULL
I have tried below query:
select distinct y.emp_id, case when Add_type='B' then i.iphone else null end
ip from (SELECT empl_id,Add_type as phty,ph_no as ph,Row_number()
OVER(PARTITION BY employee_id ORDER BY employee_id) rn
FROM
(SELECT DISTINCT
E.empl_id,E.Add_type,E.ph_no FROM Table Emp E )t )y
left outer join
(SELECT
e1.empl_id,Add_type,E1.ph_no, case when Add_type='B' then ph_no else null
end phone
FROM Table Emp E1
group by E1.employee_id, Address,
E1.phone_number )i
on y.empl_id=i.empl_id
WHERE y.rn = 1 and i.emp_id='66'
The output I get of above query is
emp_id ph
66 (8888) 8440
66 NULL
I get 2 rows, I need to have one with the ph_no. Where am I doing it wrong?
Select
addresses.Empl_id,
addresses.Add_type,
addresses.Ph_no
From (
Select DISTINCT
employees.Empl_id
From Emp
) employees
Left Join Emp addresses
On addresses.Empl_id = employees.Empl_id
And Add_type = 'B'
hi
know long time this post
another way of doing this
using row number
drop create data
use tempdb
go
drop table emp
go
create table emp
(
Empl_id int ,
Add_type varchar(100) ,
Ph_no varchar(100)
)
go
insert into emp select 66 ,'H',' 44-14424'
insert into emp select 66 ,'F',' 44-1442'
insert into emp select 66 ,'C',' 67-384-295'
insert into emp select 66 ,'B',' (8888) 8440'
insert into emp select 66 ,'H',' 608 6775'
insert into emp select 57 ,'B',' 12345'
insert into emp select 57 ,'C',' 78910'
insert into emp select 58 ,'C',' 9988'
go
SQL using row number
SELECT a.*
FROM (SELECT Row_number()
OVER(
partition BY empl_id
ORDER BY ph_no) AS rn,
empl_id,
CASE
WHEN add_type = 'B' THEN ph_no
END AS ph_no
FROM emp) a
WHERE a.rn = 1
Results
hi
a third way of doing this
using case when and max
drop create data
USE tempdb
go
DROP TABLE emp
go
CREATE TABLE emp
(
empl_id INT,
add_type VARCHAR(100),
ph_no VARCHAR(100)
)
go
INSERT INTO emp
SELECT 66,
'H',
' 44-14424'
INSERT INTO emp
SELECT 66,
'F',
' 44-1442'
INSERT INTO emp
SELECT 66,
'C',
' 67-384-295'
INSERT INTO emp
SELECT 66,
'B',
' (8888) 8440'
INSERT INTO emp
SELECT 66,
'H',
' 608 6775'
INSERT INTO emp
SELECT 57,
'B',
' 12345'
INSERT INTO emp
SELECT 57,
'C',
' 78910'
INSERT INTO emp
SELECT 58,
'C',
' 9988'
go
SQL using case when max
SELECT empl_id,
Max(CASE
WHEN add_type = 'B' THEN ph_no
ELSE NULL
END) AS maxok
FROM emp
GROUP BY empl_id
Result
hi
fourth way of doing it
little complicated
what this will take care of is
if there is more than 1 add_type B record for employee
drop create data .. more than 1 add_type B record for employee scenario
use tempdb
go
drop table emp
go
create table emp
(
empid int ,
Add_type varchar(100) ,
Ph_no varchar(100)
)
go
insert into emp select 66 ,'H',' 44-14424'
insert into emp select 66 ,'F',' 44-1442'
insert into emp select 66 ,'C',' 67-384-295'
insert into emp select 66 ,'B',' (8888) 8440'
insert into emp select 66 ,'B',' (999999'
insert into emp select 66 ,'H',' 608 6775'
insert into emp select 57 ,'B',' 12345'
insert into emp select 57 ,'C',' 78910'
insert into emp select 58 ,'C',' 9988'
go
SQL .. little complicated
; WITH cte
AS (SELECT empid,
Count(CASE
WHEN add_type = 'B' THEN 1
END) AS countb
FROM emp
GROUP BY empid)
SELECT a.empid,
a.ph_no
FROM emp a
JOIN cte b
ON a.empid = b.empid
WHERE b.countb <> 0
AND a.add_type = 'B'
UNION ALL
SELECT a.empid,
NULL
FROM emp a
JOIN cte b
ON a.empid = b.empid
WHERE b.countb = 0
Results
--create table phonenumbers
--(
--Emp_ID int,
--AD_TYPE char(2),
--Phone_Number VARCHAR(100)
--)
--insert into phonenumbers values(66,'H','44-14424')
--insert into phonenumbers values(66,'F','44-1442')
--insert into phonenumbers values(66,'C','67-384-295')
--insert into phonenumbers values(66,'B','(8888) 8440')
--insert into phonenumbers values(66,'H','608 6775')
--insert into phonenumbers values(57,'B','12345')
--insert into phonenumbers values(57,'C','78910')
--insert into phonenumbers values(58,'C','9988')
select EMP_ID,max(case when AD_TYPE = 'B' THEN Phone_Number else null end) as Phone_Number from phonenumbers
group by EMP_ID
order by EMP_ID DESC