Retreving phone numbers based on phone type

Question

Retreving phone numbers based on phone type

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
April 4, 2018 at 9:11 am

#334195

i have a table where it has empid, phone type and phone number
one employee has multiple phone numbers. is there a way to create temp table and put rank like top 1 and sort by desc by the phone type and take home or cell number
example of the results
Empid     phonetype     phone
1999993    CELL        494757587838
1999993    YUE         873628448787
1999993    HOME        878848487487
1999993    PFVG        98874874874873

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

You should know by now that you need to post consumable sample data and expected results. I'm sure there's a way, yes, but help us help you. When you post your sample data and expected results, could you show us what you've tried so far as well, and tell us why it didn't work?

Thanks.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

mcfarlandparkway - Wednesday, April 4, 2018 9:11 AM
i have a table where it has empid, phone type and phone number
one employee has multiple phone numbers. is there a way to create temp table and put rank like top 1 and sort by desc by the phone type and take home or cell number
example of the results
Empid     phonetype     phone
1999993    CELL        494757587838
1999993    YUE         873628448787
1999993    HOME        878848487487
1999993    PFVG        98874874874873

Yes, there is.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

mcfarlandparkway - Wednesday, April 4, 2018 9:11 AM
i have a table where it has empid, phone type and phone number
one employee has multiple phone numbers. is there a way to create temp table and put rank like top 1 and sort by desc by the phone type and take home or cell number
example of the results
Empid     phonetype     phone
1999993    CELL        494757587838
1999993    YUE         873628448787
1999993    HOME        878848487487
1999993    PFVG        98874874874873

SELECT *, [OrderBy] = CASE

WHEN phonetype = 'YUE' THEN 1

WHEN phonetype = 'CELL' THEN 2

WHEN phonetype = 'PFVG' THEN 3

WHEN phonetype = 'HOME' THEN 4

ELSE NULL END

FROM (VALUES

(1999993, 'CELL', '494757587838'),

(1999993, 'YUE', '873628448787'),

(1999993, 'HOME', '878848487487'),

(1999993, 'PFVG', '98874874874873')

) d (Empid, phonetype, phone)

ORDER BY [OrderBy]

SELECT *

FROM (VALUES

(1999993, 'CELL', '494757587838'),

(1999993, 'YUE', '873628448787'),

(1999993, 'HOME', '878848487487'),

(1999993, 'PFVG', '98874874874873')

) d (Empid, phonetype, phone)

ORDER BY CASE

WHEN phonetype = 'YUE' THEN 1

WHEN phonetype = 'CELL' THEN 2

WHEN phonetype = 'PFVG' THEN 3

WHEN phonetype = 'HOME' THEN 4

ELSE NULL END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

i am talking about using temp table, and by using rank function can we achieve this?

Thom A SSC Guru Points: 99133 More actions · Answer 5

mcfarlandparkway - Wednesday, April 4, 2018 10:00 AM
i am talking about using temp table, and by using rank function can we achieve this?

Probably. Have you read my reply? 🙂

Why do you want to use a temp table though, when you could return the data without using one. Seems we don't have the full picture here.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

mcfarlandparkway - Wednesday, April 4, 2018 10:00 AM
i am talking about using temp table, and by using rank function can we achieve this?

Sure!
First, set up a sample table for folks to code against. Then, write some pseudocode or whatever to give folks a better idea of what you have in mind. This will cut down on the number of guesses folks will have to make, trying to figure out what you want to do.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Since there's been nothing posted to guide anyone's further efforts, here's my guess:SELECT d.*, ROW_NUMBER() OVER(PARTITION BY d.Empid ORDER BY d.phonetype DESC) AS RowNum FROM ( VALUES (1999993, 'CELL', '494757587838'), (1999993, 'YUE', '873628448787'), (1999993, 'HOME', '878848487487'), (1999993, 'PFVG', '98874874874873') ) AS d (Empid, phonetype, phone) WHERE d.phonetype IN ('CELL', 'HOME') ORDER BY RowNum;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

sgmunson - Wednesday, April 4, 2018 12:23 PM
Since there's been nothing posted to guide anyone's further efforts, here's my guess:SELECT d.*, ROW_NUMBER() OVER(PARTITION BY d.Empid ORDER BY d.phonetype DESC) AS RowNum FROM ( VALUES (1999993, 'CELL', '494757587838'), (1999993, 'YUE', '873628448787'), (1999993, 'HOME', '878848487487'), (1999993, 'PFVG', '98874874874873') ) AS d (Empid, phonetype, phone) WHERE d.phonetype IN ('CELL', 'HOME') ORDER BY RowNum;

If it's necessary to sort the phone type non-alphabetically, you can also do something like the following...
ORDER BY CASE WHEN d.phonetype = 'HOME' THEN '1' WHEN d.phonetype = 'CELL' THEN '2' ELSE d.phonetype END

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 9

TY for the reply, i am trying to select phone number and phone type from these two tables based on employee id and insert into dept table

Insert into dbo. Dept
( phonenumber
,phonetype)
select
' ' as phonenumber,
' ' as phonetype
from dbo.employee fd

here at the the time of selecting the columns from emp table , under phone number column i can join phone table with emp table on empid . want to get top 1 record for each emp id, where phone type is HOME and if the phone number for this type is NULL then i can assign the phone type CELL if this is also NULL then assign the phone type as PFVG. so basically for each empid need to check what they have, the first priority to assign is home number. we should also check for ISNULL.
phone number should assign in the phone number column and the type should assign to the phone type column.. once the values exits here, we can insert into dept table.

dbo.phone table
empid , phonetype, phonenumber
123       HOME       33994999999
123       CELL         9457857855
123       PFVG        9848959898

dbo.employee table
empid phonetype phonenumber
123 NULL NULL

is the below query works for in the select list
(SELECT,d.PHONENUMBER, d.PHONETYPE, ROW_NUMBER() OVER(PARTITION BY d.EMPLID ORDER BY d.PHONETYPE DESC) AS RowNum
from dbo.PHONE d inner join dbo.Employee c on d.EMPLID = c.EMPLID
where d.PHONETYPE in ('HOME','CELL','PFVG')
ORDER BY RowNum,
    CASE
        WHEN d.PHONE_TYPE = 'HOME' THEN '1'
        WHEN d.PHONE_TYPE = 'CELL' THEN '2'
        WHEN d.PHONE_TYPE = 'PFVG' THEN '3'
        ELSE d.PHONE_TYPE
    END) as phonenumber

sgmunson SSC Guru Points: 110640 More actions · Answer 10

mcfarlandparkway - Thursday, April 5, 2018 2:36 PM
TY for the reply, i am trying to select phone number and phone type from these two tables based on employee id and insert into dept table
Insert into dbo. Dept
( phonenumber
,phonetype)
select
' ' as phonenumber,
' ' as phonetype
from dbo.employee fd
here at the the time of selecting the columns from emp table , under phone number column i can join phone table with emp table on empid . want to get top 1 record for each emp id, where phone type is HOME and if the phone number for this type is NULL then i can assign the phone type CELL if this is also NULL then assign the phone type as PFVG. so basically for each empid need to check what they have, the first priority to assign is home number. we should also check for ISNULL.
phone number should assign in the phone number column and the type should assign to the phone type column.. once the values exits here, we can insert into dept table.
dbo.phone table
empid , phonetype, phonenumber
123       HOME       33994999999
123       CELL         9457857855
123       PFVG        9848959898
dbo.employee table
empid    phonetype    phonenumber
123          NULL          NULL
is the below query works for in the select list
(SELECT,d.PHONENUMBER, d.PHONETYPE, ROW_NUMBER() OVER(PARTITION BY d.EMPLID ORDER BY d.PHONETYPE DESC) AS RowNum
from dbo.PHONE d inner join dbo.Employee c on d.EMPLID = c.EMPLID
where d.PHONETYPE in ('HOME','CELL','PFVG')
ORDER BY RowNum,
    CASE
        WHEN d.PHONE_TYPE = 'HOME' THEN '1'
        WHEN d.PHONE_TYPE = 'CELL' THEN '2'
        WHEN d.PHONE_TYPE = 'PFVG' THEN '3'
        ELSE d.PHONE_TYPE
    END) as phonenumber

Try this on for size: CREATE TABLE #Data ( Empid int, phonetype char(4), phone varchar(20) ); INSERT INTO #Data (Empid, phonetype, phone) SELECT Empid, phonetype, phone FROM ( VALUES (1999993, 'CELL', '494757587838'), (1999993, 'YUE', '873628448787'), (1999993, 'HOME', '878848487487'), (1999993, 'PFVG', '98874874874873') ) AS X(Empid, phonetype, phone);

WITH RANKS AS (

SELECT 'HOME' AS PHONE_TYPE, 1 AS THE_RANK UNION ALL SELECT 'CELL', 2 UNION ALL SELECT 'PFVG', 3 ) SELECT X.Empid, D2.phonetype, D2.phone FROM ( SELECT d.Empid, MIN(R.THE_RANK) AS PHONE_TYPE_SELECTOR FROM #Data AS d INNER JOIN RANKS AS R ON d.phonetype = R.PHONE_TYPE GROUP BY d.Empid ) AS X INNER JOIN RANKS AS R2 ON X.PHONE_TYPE_SELECTOR = R2.THE_RANK INNER JOIN #Data AS D2 ON X.Empid = D2.Empid AND D2.phonetype = R2.PHONE_TYPE ORDER BY X.Empid;

DROP TABLE #Data;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Something like this?
SELECT e.*, '#' '#', x.* FROM dbo.Employee e OUTER APPLY ( SELECT TOP(1) d.* FROM dbo.PHONE d WHERE d.EMPLID = c.EMPLID AND d.PHONETYPE IN ('HOME','CELL','PFVG') ORDER BY CASE WHEN d.PHONE_TYPE = 'HOME' THEN '1' WHEN d.PHONE_TYPE = 'CELL' THEN '2' WHEN d.PHONE_TYPE = 'PFVG' THEN '3' ELSE 10 END ) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden