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

• Thom A

  SSC Guru

  Points: 99133

  More actions

  April 4, 2018 at 9:21 am

  #1985485

  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

  April 4, 2018 at 9:24 am

  #1985486

  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

  April 4, 2018 at 9:25 am

  #1985488

  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

  April 4, 2018 at 10:00 am

  #1985494

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

• Thom A

  SSC Guru

  Points: 99133

  More actions

  April 4, 2018 at 10:04 am

  #1985496

  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

  April 4, 2018 at 10:07 am

  #1985499

  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

  April 4, 2018 at 12:23 pm

  #1985535

  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

  April 4, 2018 at 12:49 pm

  #1985543

  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

  April 5, 2018 at 2:36 pm

  #1985743

  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

  April 5, 2018 at 2:57 pm

  #1985745

  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

  April 6, 2018 at 1:49 am

  #1985769

  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