none
IF-THEN-ELSE in WHERE clause?

    Question

  • Hi all, I have checked some of the other threads about this subject but I think mine doesnt really fit in to the solutions given...not even sure the if-then-else is the right way to go!

    I have to check a telephone table to return what is considered the most accurate telephone number.  The order is like this

     1 Telephone number marked as Primary Contact Number (and the most recent if there is more than one)
     2 The most recently added telephone number

    Its made more complicated as although all telephone numbers are held in one table, they can be applied to either the person or their address. 

    I felt the first thing to do was make a select statement that showed all telephone numbers in one list...

    SELECT   
     Address.PersonID refkey, 
     wxtele.CommValue, 
     wxtele.PrimaryNo, 
     wxtele.StartDate,
     'address' as [type]
    FROM     
     Address INNER JOIN wxtele ON Address.AddressID = wxtele.RefKey
    WHERE 
     wxtele.RefClass = 'entities.Address'
    AND 
     (Address.StartDate IS NOT NULL AND Address.EndDate IS NULL)
    AND
     commtype = 'HOME'
    
    UNION
    
    SELECT
     wxtele.refkey,
     wxtele.commvalue,
     wxtele.primaryno,
     wxtele.startdate,
     'person' as [type]
    FROM
     wxtele
    WHERE 
     commtype = 'HOME'
    AND 
     refclass = 'entities.Person'
    
    

    So what I'm not sure about is how to incorporate this into a script that returns a list of refkeys with a single commvalue for it.  The commvalue should be chosen by the order I put at the top of this post.

    Sorry if this sounds confusing!

    Wednesday, March 30, 2011 8:51 AM

Answers

  • If you have access to row_number function (menaing sql server 2005 and on) and primaryno is unique by refkey, you can use it to get the result. Oh yeah, and if start date is actually date, your sample is showing only day and moth...

    UNTESTED!:

     

    WITH Phones

    AS

    (

    select ROW_NUMBER() OVER (PARTITION BY refkey ORDER BY case when primaryno = 'Y' then '2999-12-31' else  StartDate end desc) as rbr, refkey, commvalue

    from wxtele

    )

    SELECT refkey, commvalue

    FROM Phones

    WHERE rbr = 1

    • Proposed as answer by Naomi NModerator Wednesday, March 30, 2011 12:26 PM
    • Marked as answer by LBIAdam Wednesday, March 30, 2011 2:06 PM
    Wednesday, March 30, 2011 11:32 AM

All replies

  • Please procide table structure + sample data + desired result. Always state what version  you are using
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 30, 2011 8:57 AM
  • Address table
    personid(refkey) ,addressid
    1,                       10
    2,                       9
    3,                       8
    4,                       7
    5,                       6
    6,                       5
    7,                       4
    8,                       3
    9,                       2
    10,                     1

    wxtele table
    refkey, refclass, commtype, commvalue, primaryno, startdate
    10,      address, home,       123,            Y,             01-Jan
    3,        address, home,       234,            Y,             03-Jan
    5,        address, home,       345,            N,             01-Jan
    7,        address, home,       456,            N,             02-Jan
    9,        address, home,       567,            Y,             01-Jan
    1,        person,  home,        678,            N,             01-Jan
    13,      person,  home,        789,            N,             06-Jan
    15,      person,  home,        321,            N,             08-Jan
    7,        person,  home,        432,            Y,             10-Jan
    19,      person,  home,        543,            N,             12-Jan

     

    Hopefully my (mostly) random sample data works well enough.

    As for the desired result.  I need to have a script that just outputs the refkey and then a single commvalue...but that commvalue needs to be either the most recent number thats marked as primaryno or if there are none marked for that refkey then the just the most recent.

    Version...umm you mean the version of SQL Studio?  its 2005.

    Wednesday, March 30, 2011 11:01 AM
  • Your looking for SELECT CASE WHEN ELSE END: http://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause

    Adam


    Ctrl+Z
    Wednesday, March 30, 2011 11:30 AM
  • If you have access to row_number function (menaing sql server 2005 and on) and primaryno is unique by refkey, you can use it to get the result. Oh yeah, and if start date is actually date, your sample is showing only day and moth...

    UNTESTED!:

     

    WITH Phones

    AS

    (

    select ROW_NUMBER() OVER (PARTITION BY refkey ORDER BY case when primaryno = 'Y' then '2999-12-31' else  StartDate end desc) as rbr, refkey, commvalue

    from wxtele

    )

    SELECT refkey, commvalue

    FROM Phones

    WHERE rbr = 1

    • Proposed as answer by Naomi NModerator Wednesday, March 30, 2011 12:26 PM
    • Marked as answer by LBIAdam Wednesday, March 30, 2011 2:06 PM
    Wednesday, March 30, 2011 11:32 AM
  • That could be it mculo...as previous to needing to find the a number using the primaryno flag, I was using this script

    SELECT *
    
    FROM
    
     (
    
     SELECT
    
      homenumber1.personid, 
    
      homenumber1.hometelephonenumber
    
     FROM 
    
      (
    
      SELECT
    
       wxtele.refkey as personid,
    
       wxtele.commvalue as hometelephonenumber,
    
       ROW_NUMBER() OVER (PARTITION BY wxtele.refkey ORDER BY wxtele.startdate) as homecountnum 
    
      FROM
    
       wxtele
    
      WHERE
    
       commtype = 'HOME'
    
      AND refclass = 'entities.Person'
    
      ) AS homenumber1 
    
    Where homecountnum = 1 
    
     ) as home1
    
    

     I've worked it into the existing script and it seems to be fine!  Thanks to you all.


    Wednesday, March 30, 2011 1:28 PM
  • Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. 

    Start with the basics. What are the entities? Phones, addresses and people. Does your industry use a SAN? (Standard Address Number). Where are the phone numbers in E.164 format in your narrative?
    What are the relationships? One person has zero or more telephones. You made an address an attribute of a human being, which is absurd. There is a residence relationship. SQL programmers do not 'Y/N' bit flags for priories. Etc.

    Let's try a normalized schema

    CREATE TABLE Persons --- needs more exact name
    (person_id INTEGER NOT NULL PRIMARY KEY,
     ..);

    CREATE TABLE Telephones
    (phone_nbr CHAR(18) NOT NULL PRIMARY KEY,
     phone_type CHAR(3) NOT NULL
      CHECK (phone_type IN ('hom, 'off', 'cel', ..)),
     ..);

    CREATE TABLE Addresses
    (san CHAR(10) NOT NULL PRIMARY KEY, --- standard address number
     ..);

    CREATE TABLE PhoneOwnership
    (person_id INTEGER NOT NULL
     REFERENCES Persons (person_id),
     phone_nbr CHAR(18) NOT NULL
     REFERENCES Telephones (phone_nbr),
     PRIMARY KEY (person_id, phone_nbr),
     installation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     phone_priority INTEGER DEFAULT 0 NOT NULL --– higher number, higher priority
       CHECK (phone_priority >= 0),
     UNIQUE (person_id, phone_priority),--- no ties
     ..);

    CREATE TABLE Residency
    (person_id INTEGER NOT NULL
     REFERENCES Persons (person_id),
     san CHAR(10) NOT NULL
     REFERENCES Addresses(san),
     PRIMARY KEY person_id, san),
     ..);

    Now you said you wanted “I have to check a telephone table to return what is considered the most accurate telephone number. The order is like this
    1 Telephone number marked as Primary Contact Number (and the most recent if there is more than one)
    2 The most recently added telephone number”

    Your design has a common flaw. Called attribute splitting. The telephone priority in your non-normalized schema is split between a 'y/n' assembly language style flag and a date. You have also crammed unrelated things into single tables because that is how it would look in a paper form.

    CREATE VIEW PrimaryPhones (person_id, phone_nbr)
    AS
    SELECT person_id, phone_nbr
      FROM (SELECT person_id, phone_nbr, phone_priority,
                   MAX(phone_priority)
                   OVER(PARTITION BY person_id) AS high_phone_priority
              FROM PhoneOwnership)
     WHERE phone_priority = high_phone_priority;

    You can join this to the Persons table, then outer join to Residency then to the matching Address.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, March 30, 2011 3:05 PM