none
SQL Server code problem

    Question

  • Hello,

    I am working on a scheduling program were the user has the freedom to add certain Phone types and email types.  For example, a long time ago there were no cell phones.  Throughout the years the phone types have changed and now we have cellphones, smart phones, etc.  Now a lot of people have different types of phones and other ways to make contact with people such as emails. 

    My program allows the user to add different phone and email types as time changes. In my program, SQL part of the program, I have hard-coded the way people can contact someone, such as this:

    Home Phone, Work Phone, Work Mobile Phone, Personal Mobile Phone, Fax Phone.

    But what if there was another type of phone that the user wanted to add, my code would not allow for that.  I need to make this so that the user can add another phone type, if one existed that I don’t have listed here.  And the output needs to look like this:

    Here is the code that I have right now.  I’m not sure if there is a way to do this or not, but I would certainly appreciate the help if anyone knows how to make this happen.

    DROP PROCEDURE proc_GetEmployeeStuff
    
    --SET ANSI_NULLS ON
    --GO
    --SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROC  proc_GetEmployeeStuff
    --@Text Nvarchar(1000) OUTPUT
    AS
    BEGIN
    
    
    
    DECLARE @employeeList TABLE
    (
    empPK					INTEGER NOT NULL,
    empID					INTEGER NOT NULL,
    empFirstName			VARCHAR(40),
    empLastName				VARCHAR(40)
    
    PRIMARY KEY(empPK)
    --PRIMARY KEY(empID)
    );
    
    DECLARE @phoneList TABLE
    (
    --empPhonePK				INTEGER NOT NULL,
    emplID					INTEGER NOT NULL,
    --phoneTypeID				INTEGER,
    --phoneNumberID			VARCHAR(10),
    homePhone				VARCHAR(15),
    workPhone				VARCHAR(15),
    workMobilePhone			VARCHAR(15),
    personalMobilePhone		VARCHAR(15),
    faxPhone				VARCHAR(15)
    
    --PRIMARY KEY(empPhonePK)-- phoneTypeID)
    PRIMARY KEY(emplID)
    );
    
    
    DECLARE @emailList TABLE
    (
    
    employeeID				INTEGER NOT NULL,
    --emailTypeID				INTEGER,
    personalEmailAddress	VARCHAR(50),
    workEmailAddress		VARCHAR(50),
    otherEmailAddress		VARCHAR(50)
    
    PRIMARY KEY(employeeID)
    --PRIMARY KEY(empEmailPK)
    );
    
    DECLARE 
    	@empPK					INTEGER,
    	@empID					INTEGER,
    	@empFirstName			VARCHAR(40),
    	@empLastName			VARCHAR(40),
    	@emplID					INTEGER,
    	@empPhonePK				INTEGER,
    	@homePhone				VARCHAR(15),
    	@workPhone				VARCHAR(15),
    	@workMobilePhone		VARCHAR(15),
    	@personalMobilePhone	VARCHAR(15),
    	@faxPhone				VARCHAR(15),
    	@employeeID				INTEGER,
    	@empEmailPK				INTEGER,
    	@emailTypeID				INTEGER,
    	@personalEmailAddress	VARCHAR(50),
    	@workEmailAddress		VARCHAR(50),
    	@otherEmailAddress		VARCHAR(50);
    
    	
    	--
    IF EXISTS(
    			SELECT E.EMP_PK, E.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
    			)
    				BEGIN
    			 DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    --
    			SELECT  E.EMP_PK, E.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
     OPEN c;
      --
    			FETCH NEXT FROM c INTO @empPK, @empID, @empFirstName, 	@empLastName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --
    	INSERT @employeeList( empPK, empID, empFirstName, empLastName) 
    
    	--
    	SELECT @empPK, @empID, @empFirstName, 	@empLastName;
    	--
    	FETCH NEXT FROM c INTO  @empPK,  @empID, @empFirstName,	@empLastName;
    	END
    	
    CLOSE c;
    DEALLOCATE c;
    END
    BEGIN
    			 DECLARE D CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    				With BaseQuery AS(
    	SELECT  E.EMP_ID_NUM AS [EmployeeID], PINFO.PHONE_TYPE_ID AS [PhoneTypeID],
    	  
    	PT.PHONE_TYPE_DESC AS [PhoneTypeDescription], 
    	('(' + PINFO.AREACODE + ')' + ' ' + PINFO.PHONE_EXCH + '-' + PINFO.PHONE) AS [PhoneNumber]
     	FROM  EMPLOYEE E, PHONE_INFO PINFO, PHONE_TYPE PT
    	WHERE E.EMP_ID_NUM <> 100000
    	AND  PT.PHONE_TYPE_ID = PINFO.PHONE_TYPE_ID
    	AND PINFO.EMP_PK = E.EMP_PK)
     SELECT (EmployeeID),  
        Max(Case When [PhoneTypeID] = 200 THEN [PhoneNumber] ELSE NULL END) as Home_Phone,
        Max(Case When [PhoneTypeID] = 201 THEN [PhoneNumber] ELSE NULL END) as Work_Phone,
        Max(Case When [PhoneTypeID] = 202 THEN [PhoneNumber] ELSE NULL END) as Work_Mobile_Phone,
        Max(Case When [PhoneTypeID] = 203 THEN [PhoneNumber] ELSE NULL END) as Personal_Mobile_Phone,
        Max(Case When [PhoneTypeID] = 204 THEN [PhoneNumber] ELSE NULL END) as Fax_Phone
       FROM BaseQuery
     Group by EmployeeID
    
     OPEN D
    
    	FETCH NEXT FROM D INTO   @emplID,  @homePhone, @workPhone, @workMobilePhone,  
    	@personalMobilePhone, 	@faxPhone;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @phoneList( emplID,  homePhone, workPhone, 
    	workMobilePhone,  personalMobilePhone, faxPhone)
    
    	
    	SELECT @emplID,  @homePhone, @workPhone, @workMobilePhone,  @personalMobilePhone, 
    	@faxPhone;
    
    	
    	FETCH NEXT FROM D INTO   @emplID,   @homePhone, @workPhone, 
    		@workMobilePhone,  @personalMobilePhone, @faxPhone;
    
    	END
    	
    CLOSE D;
    DEALLOCATE D;
    END
    
    BEGIN
    DECLARE A CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    				With BaseQuery AS(
    	SELECT  E.EMP_ID_NUM AS [EmployeeID], 
    	EI.EMAIL_TYPE_ID AS [EmailTypeID], 
    	EI.EMAIL_ADDRESS AS [Email]
    	FROM  EMPLOYEE E,  EMAIL_INFO EI, EMAIL_TYPE ET
    	WHERE E.EMP_ID_NUM <> 100000
    	AND E.EMP_PK = EI.EMP_PK
    	AND EI.EMAIL_TYPE_ID = ET.EMAIL_TYPE_ID)
    	
     SELECT (EmployeeID), 
        Max(Case When [EmailTypeID] = 600 THEN [Email] ELSE NULL END) as Personal_Email,
        Max(Case When [EmailTypeID] = 601 THEN [Email] ELSE NULL END) as Work_Email,
        Max(Case When [EmailTypeID] = 602 THEN [Email] ELSE NULL END) as Other_Email
    
     FROM BaseQuery
     Group by EmployeeID
    
    
     OPEN A;
    
    			FETCH NEXT FROM A INTO  @employeeID,  	@personalEmailAddress, @workEmailAddress,
    			 @otherEmailAddress; 
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    			INSERT @emailList( employeeID,  personalEmailAddress, workEmailAddress, 
    			otherEmailAddress)
    --empEmailPK, @empEmailPK,
    
    	SELECT   @employeeID, 	@personalEmailAddress, @workEmailAddress, @otherEmailAddress; 
    
    -- @empEmailPK,
    	FETCH NEXT FROM A INTO  @employeeID, @personalEmailAddress, @workEmailAddress,
    			 @otherEmailAddress; 
    
    END
    	
    CLOSE A;
    DEALLOCATE A;
    END
    BEGIN
    --EL.empPK AS [EmpPK],
    SELECT   EL.empID AS [EmployeeID],  EL.empFirstName AS [FirstName], EL.empLastName AS [LastName], 
    	PL.homePhone AS [HomePhone], PL.workPhone AS [WorkPhone], PL.workMobilePhone AS [WorkMobilePhone],
    	PL.personalMobilePhone AS [PersonalMobilePhone], PL.faxPhone AS [FaxPhone],
    	EML.personalEmailAddress AS [PersonalEmail], EML.workEmailAddress AS [WorkEmail], 
    	EML.otherEmailAddress AS [OtherEmail]
    FROM @employeeList EL
     LEFT JOIN @phoneList PL
    	ON EL.empID = PL.emplID
    	LEFT JOIN @emailList EML
    	ON EL.empID = EML.employeeID
    
    	
    
    	END
    
    END
    GO
    
    EXEC proc_GetEmployeeStuff

    Thank you very much,

    Bosco Dog


    Sunday, January 21, 2018 3:40 PM

Answers

  • Here's full illustration of what I'm talking about

    Your existing data

    CREATE TABLE MEDIUM_TYPE 
    (
    MEDIUM_TYPE_ID		INTEGER IDENTITY(1499, 1),
    MEDIUM_TYPE_NAME	VARCHAR(35)
    
    PRIMARY KEY (MEDIUM_TYPE_ID)
    );
    
    CREATE TABLE COMMUNICATION_MEDIUM 
    (
    COMM_MEDIUM_ID			INTEGER IDENTITY(1599, 1),
    MEDIUM_NAME			VARCHAR(35),
    MEDIUM_DESCRIPTION		VARCHAR(35)
    
    PRIMARY KEY(COMM_MEDIUM_ID)
    );
    
    CREATE TABLE EMPLOYEE_COMMUNICATION
    (
    COMM_ID				INTEGER IDENTITY(1300, 1) NOT NULL,
    EMP_PK				INTEGER NOT NULL,
    EMP_ID_NUM			INTEGER,
    COMM_MEDIUM_ID		INTEGER,
    MEDIUM_TYPE_ID		INTEGER,
    VALUE				VARCHAR(10),
    PREFERRED_TYPE		BIT,
    PREFERRED_MEDIUM	BIT
    
    
    
    );
    
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Personal Home Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Personal Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Personal Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Work Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Work Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Work Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Princess Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Cockpit Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Executive Phone');
    
    
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Home');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Office');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Truck');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Plane');
    
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1599, 1499, '5175552323', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1600, 1499, '2485553789', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1605, 1499, '2485553788', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (117, 100014, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, 1607, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1605, 1500, '734876900', 1, 1);
    

    The dynamic crosstab procedure

    CREATE PROC EmpCommunicationDetails @EmpID INT = -1 -- when -1 is passed it returns all employees details AS DECLARE @SQL VARCHAR(MAX),@CrosstabList VARCHAR(MAX) IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t SELECT ec.EMP_ID_NUM,mt.MEDIUM_TYPE_NAME,cm.MEDIUM_NAME,ec.VALUE INTO #T FROM EMPLOYEE_COMMUNICATION ec JOIN COMMUNICATION_MEDIUM cm ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID JOIN MEDIUM_TYPE mt ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
    WHERE ec.EMP_ID_NUM = @EmpID
    OR @EmpID = -1

    SELECT @CrosstabList= STUFF((SELECT DISTINCT ',MAX(CASE WHEN MEDIUM_TYPE_NAME = ''' + MEDIUM_TYPE_NAME + ''' AND MEDIUM_NAME = ''' + MEDIUM_NAME + ''' THEN VALUE END) AS [' + MEDIUM_TYPE_NAME + '_' + MEDIUM_NAME + ']' FROM #t FOR XML PATH('')),1,1,'') SET @SQL = 'SELECT EMP_ID_NUM,' + @CrosstabList +' FROM #t c GROUP BY EMP_ID_NUM' EXEC(@SQL) GO


    Now execute it

    EXEC EmpCommunicationDetails
    
    
    /*
    output
    --------------------------------------------------------
    EMP_ID_NUM  Home_Land Phone Home_Mobile Phone Office_Land Phone Plane_Plane Phone
    ----------- --------------- ----------------- ----------------- -----------------------
    100007      NULL            NULL              734876900         8106329990
    100014      NULL            NULL              NULL              8106329990
    100041      5175552323      2485553789        NULL              NULL
    200000      NULL            NULL              NULL              8106329990
    
    
    */

    Now lets add two new medium types say FlyMail and SkyPhone

    This will be added to employee with PK 100 for this illustration

    The insert script would be as below

    DECLARE @MediumTypeID INT,@CommMediumID int
    
    	INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Personal');
    
    	SET @MediumTypeID = SCOPE_IDENTITY()
    
    
    	INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Flymail', 'Fmail');
    	SET @CommMediumID = SCOPE_IDENTITY()
    	INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, @CommMediumID, @MediumTypeID, '123005#324', 0, 0);
    
    
    	INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Skyphone', 'Sphone');
    		SET @CommMediumID = SCOPE_IDENTITY()
    
    	INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, @CommMediumID, @MediumTypeID, '356415##23', 0, 0);
    

    Now execute the procedure and you will see the new comm types and value added automatically to the output as below

    EXEC EmpCommunicationDetails
    GO
    
    
    
    /*
    
    output
    --------------------------------------------------------------------------------------------------------------------------
    EMP_ID_NUM  Home_Land Phone Home_Mobile Phone Office_Land Phone Personal_Flymail Personal_Skyphone Plane_Plane Phone
    ----------- --------------- ----------------- ----------------- ---------------- ----------------- -----------------
    100007      NULL            NULL              734876900         NULL             NULL              8106329990
    100014      NULL            NULL              NULL              NULL             NULL              8106329990
    100041      5175552323      2485553789        NULL              NULL             NULL              NULL
    200000      NULL            NULL              NULL              123005#324       356415##23        8106329990
    
    
    */
    

    this illustrates how they can see new medium types without tweaking any logic within the existing procedure

    Now if you want to see only a single employee details, it will only show the communication mediums relevant to him

    see this illustration for that

    EXEC EmpCommunicationDetails 200000
    GO
    
    /*
    output
    ------------------------------------------------------------
    -------
    EMP_ID_NUM  Personal_Flymail Personal_Skyphone Plane_Plane Phone
    ----------- ---------------- ----------------- -----------------
    200000      123005#324       356415##23        8106329990
    
    
    */

    As you see it doesnt list any medium info which are unrelated to the employee being passed

    Hope this makes the concept clear to you


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 12:17 PM

All replies

  • For introducing the flexibility like that, you've to tweak your model like this

    CommunicationMedium
    -----------------
    CommMediumID - Unique ID
    MediumName - Land Phone, Mobile Phone, Fax, Email etc
    MediumDescription - any additional description
    
    
    MediumType
    ----------------
    MediumTypeID - Unique ID
    TypeName - Home, Official, etc
    
    
    EmployeeCommunication
    -------------------------
    CommID - Unique ID
    EmployeeID - FK to EMployee
    CommMediumID - FK to CommunicationMedium
    MediumTypeID - FK to MediumType
    Value - Actual phone number ,email etc value PreferredType - boolean ( which is preferred medium type for communication - Cell Phoen for ex) PreferredMedium - boolean - (which is preferred medium - for example in case multiple cell numbers which is preffered)


    Once you've this model you can create a flattened view out of this for each employee like

    SELECT e.EmployeeID,
    MAX(CASE WHEN m.TypeName = 'Mobile' THEN e.Value END) AS MobileNumber,
    MAX(CASE WHEN m.TypeName = 'Landline' THEN e.Value END) AS LandlineNumber,
    MAX(CASE WHEN m.TypeName = 'Email' THEN e.Value END) AS Email,
    MAX(CASE WHEN m.TypeName = 'Fax' THEN e.Value END) AS FaxNumber,
    MAX(CASE WHEN e.PreferredMedium = 1 THEN e.Value END) AS PreferredCommValue,
    MAX(CASE WHEN e.PreferredType = 1 THEN m.TypeName END) AS PreferredCommType
    FROM EmployeeCommunication e
    JOIN COmmunicationMedium c
    ON c.CommMediumID = e.CommMediumID
    JOIN MediumType m
    ON m.MediumTypeID = e.MediumTypeID
    GROUP BY e.EmployeeID

    And use this to join to employee to get other details

    Any new type would require you to just add type as a new row in CommunicationMedium and relevant type in MediaType(home/office etc). The actual value would be added to EmployeeCommunication table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 21, 2018 4:01 PM
  • Visakh16:

    Thank you very much for responding so quickly.  I will try this in a little while when I get home. 

    Thank you again.

    Bosco Dog.

    Sunday, January 21, 2018 4:22 PM
  • Here is the code that I have right now. I’m not sure if there is a way to do this or not, but I would certainly appreciate the help if anyone knows how to make this happen.

    You are not yet writing SQL. Your writing whatever procedural language you learned originally and putting it into SQL. The prefix “proc_” is called a Tibble; it is the fallacy of putting metadata into data element names and it comes from the “tbl_” prefix that newbies use. And so are your PK and FK affixes. 

    There should be no such thing as a “get Personnel stuff” because the name is too vague. There’s the law of identity which states that to be is to be something in particular and to be anything in general or nothing in particular is to be nothing at all. What stuff are you getting? Precisely and exactly what are you getting with this procedure? Why is it text? COBOL, which is what you’re really writing, keeps everything in text.

    You write out to a scratch file, just like we did with magnetic tapes in the 1960s, and then use a cursor to manipulate it. Again, just like we did in the 1960s. Totally nonrelational. I’ve written five cursors in my entire career and I know that if I had the current SQL features, I could have easily avoided three of them. 

    We never use numerics for identifiers because we do no math on them. Think about it; what is the square root of your credit card number?

    SQL is declarative language, so we hate things like local variables. But you used to writing in a procedural language (VB? Cobol?) in which you hang a scratch tape (now replaced with a local table declaration, the same logical concept).

    There is also nice no such thing as a “<something>_type_id” in RDBMS. The postfixes “type” and “id” are what ISO calls an attribute property. You can only have one of those on an attribute by definition. Again, think about it; what your blood_type_id? Why not keep going and use “blood_type_id_code_value” and make it worse.

    Also we don’t use inconsistent capitalization and haven’t for about 30 years now. We tested it and found the problem with having a capital letter away from the first position in a word or name is that your eye jumps to it. This is how the Latin alphabet works. You’ve added about 8 to 12% more time in debugging by doing this.

    How did you arrive at a 40 character first and last name? The postal convention for an entire name line is 35 characters. Would you please give me an example of someone with a name that long? When you’re designing a table, the data types, and in particular the length of character strings is one of the things that maintains integrity.

    SQL is based on sets, so we don’t have list as a data structure. Your phone list table is not just poorly named, but it confuses what an entity is with its values. Phone numbers are not variable length! Nor are they free-form. Look up the standard format for international phone numbers. Then you go to the other extreme. I have an email address is too short; while it doesn’t occur very often an email can be as long as 256 characters.

    The problem is that you don’t understand that a phone number is a phone number, whether at work or home or wherever. 

    Finally, each set of entities should have its own table; employees collectively are called personnel so they get a table. Likewise, phone numbers are entities and emails are entities. After that you need to create a table that shows the relationship among these three things. Here is the first shot:

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
     emp_first_name VARCHAR(20) NOT NULL,
     emp_last_name VARCHAR(20) NOT NULL);

    CREATE TABLE Phones
    (phone_nbr CHAR(10) NOT NULL
       CHECK (phone_nbr 
            LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’),
     phone_type VARCHAR(10) NOT NULL
      CHECK (phone_type IN
             (‘home’, ‘work’, ‘work mobile’, ‘home_mobile’, ‘fax’))
    );

    CREATE TABLE Emails 
    (email_addr VARCHAR(256) NOT NULL PRIMARY KEY
      -– Check horrible reg expression
     email_type VARCHAR(10) NOT NULL
      CHECK (email_type IN (‘personal’, ‘work’, ‘other’))
    );

    CREATE TABLE Employee_Contacts –- this is a relationship
    (emp_id CHAR(10) NOT NULL 
       REFERENCES Personnel(emp_id)
       ON UPDATE CASCADE
       ON UPDATE DELETE,
     emp_phone_nbr CHAR(10)
     REFERENCES Phones
       ON UPDATE CASCADE
       ON UPDATE DELETE,
     emp_email_addr VARCHAR(256)
     REFERENCES Emails
       ON UPDATE CASCADE
       ON UPDATE DELETE,
     UNIQUE(emp_id, emp_phone_nbr, emp_email_addr)
    );
     
    I have used nulls for the emails and the phones, so in order to guarantee uniqueness I have to use the UNIQUE() clause instead of a PRIMARY KEY. However, you might consider putting default clauses that would give a dummy phone number and a dummy email 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

    Sunday, January 21, 2018 11:06 PM
  • Dear Mr. Celko:

    You have very good questions.  I want to answer one of your first questions.  "get Personnel stuff",

    The name in my code is actually "procedure_GetEmployeeContactInformation". Originally I wrote all of this under this name.  Then I was going to revise it so that I might be able to get the results that I needed.  I did not want to mess up my original, so I just gave it a name so that I could use as a work file.  It is true, it has no meaning.  You are absolutely correct. 

    You know what drives me nuts? . . . when people use 'bar' or 'foo' in their code.  So I totally understand why you questioned me about this.  I really wasn't concerned about what I called it at the time; I just didn't want to mess up my original code. 

    . 

    I will try to go through the rest of your concerns as soon as I can.  But, right now I'm trying to work on the ideas that Visakh16 gave me. 

    This is my first major project and I'm still learning.  I know I have a lot to learn.  But I certainly am open to ideas that anyone gives me.

    Thank you for your concerns.

    Bosco



    • Edited by bosco dog Tuesday, January 23, 2018 10:08 PM
    Tuesday, January 23, 2018 10:01 PM
  • Visakh16,

    I just want you to know that I am still working on your suggestion.  Please be patient about me 'marking' your suggestion as answered. 

    Thanks again for your suggestion.  Thank you for your patience.

    Sincerely,

    Bosco Dog

    Monday, January 29, 2018 9:48 PM
  • The affixes like “procedure”, mix data and metadata in the data element name. We don’t do that. it’s redundant, and it confuses the two levels that you have to work with in a data model. The use of the prefix “tbl” on tables is called a Tibble, and “vw” on a view is called a Volkswagen. Phil Factor has actually done some pretty funny columns on this design error.

    The history of “foobar” or “fubar” is interesting. It was originally military abbreviation like snafu, but in the early days of software engineering, Ed Yourdon started using it in his books; he had gotten it from the slang going around MIT, as I recall. When you need to replace something with a real name, it makes just as good a nonsense word marker for your text edit as anything else.

    --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

    Monday, January 29, 2018 10:40 PM
  • Visakh16:

    I have been working on this code and here is what I did:

    I created the tables.

    CREATE TABLE MEDIUM_TYPE 
    (
    MEDIUM_TYPE_ID		INTEGER IDENTITY(1499, 1),
    MEDIUM_TYPE_NAME	VARCHAR(35)
    
    PRIMARY KEY (MEDIUM_TYPE_ID)
    );
    
    CREATE TABLE COMMUNICATION_MEDIUM 
    (
    COMM_MEDIUM_ID			INTEGER IDENTITY(1599, 1),
    MEDIUM_NAME			VARCHAR(35),
    MEDIUM_DESCRIPTION		VARCHAR(35)
    
    PRIMARY KEY(COMM_MEDIUM_ID)
    );
    
    CREATE TABLE EMPLOYEE_COMMUNICATION
    (
    COMM_ID				INTEGER IDENTITY(1300, 1) NOT NULL,
    EMP_PK				INTEGER NOT NULL,
    EMP_ID_NUM			INTEGER,
    COMM_MEDIUM_ID		INTEGER,
    MEDIUM_TYPE_ID		INTEGER,
    VALUE				VARCHAR(10),
    PREFERRED_TYPE		BIT,
    PREFERRED_MEDIUM	BIT
    
    PRIMARY KEY(COMM_ID),
    FOREIGN KEY(EMP_PK) REFERENCES EMPLOYEE(EMP_PK),
    FOREIGN KEY(COMM_MEDIUM_ID) REFERENCES COMMUNICATION_MEDIUM(COMM_MEDIUM_ID),
    FOREIGN KEY(MEDIUM_TYPE_ID) REFERENCES  MEDIUM_TYPE(MEDIUM_TYPE_ID)
    
    );

    Then I added data:

    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Personal Home Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Personal Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Personal Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Work Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Work Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Work Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Princess Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Cockpit Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Executive Phone');
    
    
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Home');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Office');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Truck');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Plane');
    
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1599, 1499, '5175552323', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1600, 1499, '2485553789', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1605, 1499, '2485553788', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (117, 100014, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, 1607, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1605, 1500, '734876900', 1, 1);

    Notice that I added different Medium Types such as Truck and Plane.


    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Truck');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Plane');


    And added different COMMUNICATION_MEDIUM Descriptions such as Princess Phone, Cockpit Phone and Executive Phone on the plane.

    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) 
    VALUES ('Land Phone', 'Princess Phone');
    
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) 
    VALUES ('Plane Phone', 'Cockpit Phone');
    
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION)
     VALUES ('Plane Phone', 'Executive Phone');

    Which is what I want to be able to do.  I want to allow the user who is using a GUI C# code that I wrote to be able to add a different Phone Type, or Email type, or add a different Medium_Description.  I want to be able to add any type of communication type that may be created in the future. 

    Here’s my code:

    DROP PROCEDURE procedure_GetEmployeeContactInformation
    
    GO
    CREATE PROCEDURE procedure_GetEmployeeContactInformation
    AS 
    BEGIN
    
    	
    DECLARE @employeeList TABLE
    (
    empPK			INTEGER NOT NULL,
    employID		INTEGER NOT NULL,
    firstName		VARCHAR(40),
    lastName		VARCHAR(40)
    
    PRIMARY KEY(empPK, employID)
    
    );
    
    DECLARE @communicationMedium TABLE
    (
    commMediumID 			INTEGER NOT NULL,
    mediumName				VARCHAR(30),
    mediumDescription		VARCHAR(30)
    
    
    PRIMARY KEY(CommMediumID)
    );
    
    DECLARE @mediumType TABLE
    (
    mediumTypeID	INTEGER NOT NULL,
    typeName		VARCHAR(30)
    
    PRIMARY KEY (MediumTypeID)
    );
    
    DECLARE @employeeCommunication TABLE
    (
    commID			INTEGER NOT NULL,
    employID		INTEGER NOT NULL,
    commMediumID	INTEGER NOT NULL,
    mediumTypeID	INTEGER NOT NULL,
    value			VARCHAR(10),
    preferredType	BIT,
    preferredMedium BIT
    
    PRIMARY KEY(commID)
    
    );
    
    	DECLARE 
    	@empPK						INTEGER,
    	@employeeID					INTEGER,
    	@empFirstName				VARCHAR(40),
    	@empLastName				VARCHAR(40),
    	@commMediumID				INTEGER,
    	@mediumName					VARCHAR(30),
    	@mediumDescription			VARCHAR(40),
    	@mediumTypeID				INTEGER,
    	@typeName					VARCHAR(30),
    	@commID						INTEGER,
    	@value						VARCHAR(10),
    	@preferredType			    BIT,	
    	@preferredMedium			BIT;
    
    --*****************************************************************
    --- SECTION 1
    
    IF EXISTS(
    			SELECT E.EMP_PK, E.EMP_ID_NUM
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
    			)
    
    			 DECLARE EmployCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  E.EMP_PK, E.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
     OPEN EmployCursor;
     
    			FETCH NEXT FROM EmployCursor INTO @empPK, @employeeID, @empFirstName, @empLastName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @employeeList(empPK, employID, firstName, lastName) 
    
    	
    	SELECT @empPK, @employeeID, @empFirstName, 	@empLastName;
    
    	FETCH NEXT FROM EmployCursor INTO  @empPK,  @employeeID, @empFirstName,	@empLastName;
    	--END
    END	
    CLOSE EmployCursor;
    DEALLOCATE EmployCursor;
    
    -- END OF SECTION 1
    --********************************************************************************
    -- SECTION 2
    
    			 DECLARE CommMediumCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  CM.COMM_MEDIUM_ID, CM.MEDIUM_NAME, CM.MEDIUM_DESCRIPTION
    			FROM COMMUNICATION_MEDIUM CM
    			
    
     OPEN CommMediumCursor;
     
    			FETCH NEXT FROM CommMediumCursor INTO @commMediumID, @mediumName, @mediumDescription;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @communicationMedium(commMediumID, mediumName, mediumDescription) 
    
    	
    	SELECT @commMediumID, @mediumName, @mediumDescription;
    
    	FETCH NEXT FROM CommMediumCursor INTO @commMediumID, @mediumName, @mediumDescription;
    	
    
    END
    CLOSE CommMediumCursor;
    DEALLOCATE CommMediumCursor;
    
    -- END OF SECTION 2
    --*********************************************************************************
    -- SECTION 3
    
    			 DECLARE MedTypeCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  MT.MEDIUM_TYPE_ID, MT.MEDIUM_TYPE_NAME
    			FROM MEDIUM_TYPE MT
    			
    
     OPEN MedTypeCursor;
     
    			FETCH NEXT FROM MedTypeCursor INTO @mediumTypeID, @typeName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @mediumType(mediumTypeID, typeName) 
    
    	
    	SELECT @mediumTypeID, @typeName;
    
    	FETCH NEXT FROM MedTypeCursor INTO @mediumTypeID, @typeName;
    	
    END	
    CLOSE MedTypeCursor;
    DEALLOCATE MedTypeCursor;
    
    
    
    -- END OF SECTION 3
    
    --*********************************************************************************
    -- SECTION 4
    
    			 DECLARE EmployeeCommCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  EC.COMM_ID, EC.EMP_ID_NUM, EC.COMM_MEDIUM_ID, EC.MEDIUM_TYPE_ID,
    				EC.VALUE, EC.PREFERRED_TYPE, EC.PREFERRED_MEDIUM
    			FROM EMPLOYEE_COMMUNICATION EC
    			
    
     OPEN EmployeeCommCursor;
     
    			FETCH NEXT FROM EmployeeCommCursor INTO @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @employeeCommunication(commID, employID, commMediumID, mediumTypeID,
    		value, preferredType, preferredMedium)
    
    	
    	SELECT @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium;
    
    	FETCH NEXT FROM EmployeeCommCursor INTO @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium;
    
    END	
    CLOSE EmployeeCommCursor;
    DEALLOCATE EmployeeCommCursor;
    
    -- END OF SECTION 4
    -- **************************************************************************
    -- SECTION 5 Added code for testing
    
    --SELECT el.empPK, el.employID, el.firstName, el.lastName, cm.commMediumID, cm.mediumName,
    --cm.mediumDescription, mt.typeName, mt.mediumTypeID, ec.value
    --FROM @employeeList el LEFT OUTER JOIN @employeeCommunication ec
    --ON el.employID = ec.employID
    --LEFT OUTER JOIN @communicationMedium cm
    --ON cm.commMediumID = ec.commMediumID
    --LEFT OUTER JOIN @mediumType mt
    --ON mt.mediumTypeID = ec.mediumTypeID
    --WHERE ec.mediumTypeID IS NOT NULL
    --ORDER BY el.employID
    
    
    --END
    --GO
    
    
    --EXEC procedure_GetEmployeeContactInformation
    -- END OF SECTION 5 Add code for testing
    
    --*********************************************************************************
    -- SECTION 6
    
    				With BaseQuery AS(
    	SELECT  E.EMP_ID_NUM AS [EmployeeID], E.EMP_FNAME AS [FirstName], E.EMP_LNAME AS [LastName],
    	 MT.MEDIUM_TYPE_ID AS [MediumTypeID],
    	MT.MEDIUM_TYPE_NAME AS [MediumTypeName], 
    	 CM.COMM_MEDIUM_ID AS [CommMediumID],
    	CM.MEDIUM_NAME AS [MediumName], CM.MEDIUM_DESCRIPTION AS [MediumDescription],
    	 EC.VALUE AS [Value]
    	FROM  EMPLOYEE E, MEDIUM_TYPE MT, COMMUNICATION_MEDIUM CM, 
    			EMPLOYEE_COMMUNICATION EC
    	WHERE E.EMP_ID_NUM <> 100000
    	AND E.EMP_ID_NUM = EC.EMP_ID_NUM
    	AND CM.COMM_MEDIUM_ID = EC.COMM_MEDIUM_ID
    	AND  MT.MEDIUM_TYPE_ID = EC.MEDIUM_TYPE_ID)
    	
    SELECT EC.EmployID,
    MAX(CASE WHEN M.TypeName = 'Mobile' THEN EC.Value END) AS MobileN,
    MAX(CASE WHEN M.TypeName = 'Landline' THEN EC.Value END) AS LandNum,
    MAX(CASE WHEN M.TypeName = 'Email' THEN EC.Value END) AS Email,
    MAX(CASE WHEN M.TypeName = 'Fax' THEN EC.Value END) AS FaxN,
    MAX(CASE WHEN EC.PreferredMedium = 1 THEN EC.Value END) AS PrefCommValue,
    MAX(CASE WHEN EC.PreferredType = 1 THEN M.TypeName END) AS PrefCommType
    FROM @employeeList EL  JOIN @employeeCommunication EC
    ON EL.employID = EC.employID
    JOIN @communicationMedium C
    ON C.commMediumID = EC.commMediumID
    JOIN @mediumType M
    ON M.mediumTypeID = EC.mediumTypeID
    GROUP BY EC.employID;
    
    -- END OF SECTION 6
    
    END
    GO
    
    -- END OF procedure_GetEmployeeContactInformation
    
    EXEC procedure_GetEmployeeContactInformation


    Notice that I made comments such as SECTION 1, SECTION 2, etc.

    I also have a -- SECTION 5 Added code for testing

    which is commented out.

    When I run the code above as it is shown, I get these results:

    EmployID MobileN LandNum Email	FaxN PrefCommValue PrefCommType
    100007	 NULL	 NULL	 NULL	NULL 8106329990	   Plane	
    100014	 NULL	 NULL	 NULL	NULL 8106329990	   Plane
    100041	 NULL	 NULL	 NULL	NULL 5175552323	   Home
    200000	 NULL	 NULL	 NULL	NULL 8106329990	   Plane

    As you can see, because the user added a Plane and a Truck with this code, the SQL code doesn’t work.

    Now if I re-run the code by commenting out SECTION 6 and adding SECTION 5, you get this code:

    DROP PROCEDURE procedure_GetEmployeeContactInformation
    
    GO
    CREATE PROCEDURE procedure_GetEmployeeContactInformation
    AS 
    BEGIN
    
    	
    DECLARE @employeeList TABLE
    (
    empPK			INTEGER NOT NULL,
    employID		INTEGER NOT NULL,
    firstName		VARCHAR(40),
    lastName		VARCHAR(40)
    
    PRIMARY KEY(empPK, employID)
    
    );
    
    DECLARE @communicationMedium TABLE
    (
    commMediumID 			INTEGER NOT NULL,
    mediumName				VARCHAR(30),
    mediumDescription		VARCHAR(30)
    
    
    PRIMARY KEY(CommMediumID)
    );
    
    DECLARE @mediumType TABLE
    (
    mediumTypeID	INTEGER NOT NULL,
    typeName		VARCHAR(30)
    
    PRIMARY KEY (MediumTypeID)
    );
    
    DECLARE @employeeCommunication TABLE
    (
    commID			INTEGER NOT NULL,
    employID		INTEGER NOT NULL,
    commMediumID	INTEGER NOT NULL,
    mediumTypeID	INTEGER NOT NULL,
    value			VARCHAR(10),
    preferredType	BIT,
    preferredMedium BIT
    
    PRIMARY KEY(commID)
    
    );
    
    	DECLARE 
    	@empPK						INTEGER,
    	@employeeID					INTEGER,
    	@empFirstName				VARCHAR(40),
    	@empLastName				VARCHAR(40),
    	@commMediumID				INTEGER,
    	@mediumName					VARCHAR(30),
    	@mediumDescription			VARCHAR(40),
    	@mediumTypeID				INTEGER,
    	@typeName					VARCHAR(30),
    	@commID						INTEGER,
    	@value						VARCHAR(10),
    	@preferredType			    BIT,	
    	@preferredMedium			BIT;
    
    --*****************************************************************
    --- SECTION 1
    
    IF EXISTS(
    			SELECT E.EMP_PK, E.EMP_ID_NUM
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
    			)
    
    			 DECLARE EmployCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  E.EMP_PK, E.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME
    			FROM EMPLOYEE E
    			WHERE E.EMP_ID_NUM <> 100000
    
     OPEN EmployCursor;
     
    			FETCH NEXT FROM EmployCursor INTO @empPK, @employeeID, @empFirstName, @empLastName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @employeeList(empPK, employID, firstName, lastName) 
    
    	
    	SELECT @empPK, @employeeID, @empFirstName, 	@empLastName;
    
    	FETCH NEXT FROM EmployCursor INTO  @empPK,  @employeeID, @empFirstName,	@empLastName;
    	--END
    END	
    CLOSE EmployCursor;
    DEALLOCATE EmployCursor;
    
    -- END OF SECTION 1
    --********************************************************************************
    -- SECTION 2
    
    			 DECLARE CommMediumCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  CM.COMM_MEDIUM_ID, CM.MEDIUM_NAME, CM.MEDIUM_DESCRIPTION
    			FROM COMMUNICATION_MEDIUM CM
    			
    
     OPEN CommMediumCursor;
     
    			FETCH NEXT FROM CommMediumCursor INTO @commMediumID, @mediumName, @mediumDescription;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @communicationMedium(commMediumID, mediumName, mediumDescription) 
    
    	
    	SELECT @commMediumID, @mediumName, @mediumDescription;
    
    	FETCH NEXT FROM CommMediumCursor INTO @commMediumID, @mediumName, @mediumDescription;
    	
    
    END
    CLOSE CommMediumCursor;
    DEALLOCATE CommMediumCursor;
    
    -- END OF SECTION 2
    --*********************************************************************************
    -- SECTION 3
    
    			 DECLARE MedTypeCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  MT.MEDIUM_TYPE_ID, MT.MEDIUM_TYPE_NAME
    			FROM MEDIUM_TYPE MT
    			
    
     OPEN MedTypeCursor;
     
    			FETCH NEXT FROM MedTypeCursor INTO @mediumTypeID, @typeName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @mediumType(mediumTypeID, typeName) 
    
    	
    	SELECT @mediumTypeID, @typeName;
    
    	FETCH NEXT FROM MedTypeCursor INTO @mediumTypeID, @typeName;
    	
    END	
    CLOSE MedTypeCursor;
    DEALLOCATE MedTypeCursor;
    
    
    
    -- END OF SECTION 3
    
    --*********************************************************************************
    -- SECTION 4
    
    			 DECLARE EmployeeCommCursor CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    
    			SELECT  EC.COMM_ID, EC.EMP_ID_NUM, EC.COMM_MEDIUM_ID, EC.MEDIUM_TYPE_ID,
    				EC.VALUE, EC.PREFERRED_TYPE, EC.PREFERRED_MEDIUM
    			FROM EMPLOYEE_COMMUNICATION EC
    			
    
     OPEN EmployeeCommCursor;
     
    			FETCH NEXT FROM EmployeeCommCursor INTO @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	INSERT @employeeCommunication(commID, employID, commMediumID, mediumTypeID,
    		value, preferredType, preferredMedium)
    
    	
    	SELECT @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium;
    
    	FETCH NEXT FROM EmployeeCommCursor INTO @commID, @employeeID, @commMediumID,
    				@mediumTypeID, @value, @preferredType, @preferredMedium;
    
    END	
    CLOSE EmployeeCommCursor;
    DEALLOCATE EmployeeCommCursor;
    
    -- END OF SECTION 4
    -- **************************************************************************
    -- SECTION 5 Added code for testing
    
    SELECT el.empPK AS [EmpPK], el.employID AS [empID], el.firstName AS [fName], 
    el.lastName AS [lName], cm.commMediumID AS[comMedID], cm.mediumName AS [medName],
    cm.mediumDescription AS [medDes], mt.typeName AS [typeName], mt.mediumTypeID AS [medTypID], 
    ec.value AS [value]
    FROM @employeeList el LEFT OUTER JOIN @employeeCommunication ec
    ON el.employID = ec.employID
    LEFT OUTER JOIN @communicationMedium cm
    ON cm.commMediumID = ec.commMediumID
    LEFT OUTER JOIN @mediumType mt
    ON mt.mediumTypeID = ec.mediumTypeID
    WHERE ec.mediumTypeID IS NOT NULL
    ORDER BY el.employID
    
    
    END
    GO
    
    
    EXEC procedure_GetEmployeeContactInformation

    Note that I added this line in the code

    WHERE ec.mediumTypeID 

    because I didn’t want to get every employee’s
    name because it eats up too much space.


    And you get these results, which is what I want to get.  Except the formatting is not correct.  empID 100007 should have all of his results on one line.  I'm need to pivot the rows to be columns.


    EmpPK empID  fName   lName  comMedID medName	    medDes             typeName medTypID	value
    110   100007 Rick    Hayman    1606  Plane Phone  Cockpit Phone         Plane	1502	8106329990
    110   100007 Rick    Hayman    1605  Land Phone   Princess Phone        Office	1500	7348769000
    117   100014 Matthew Warehouse 1606  Plane Phone  Cockpit Phone         Plane	1502	8106329990
    101   100041 Gerald  Strapper  1599  Land Phone   Personal Home Phone   Home	1499	5175552323
    101   100041 Gerald  Strapper  1600  Mobile Phone Personal Mobile Phone Home	1499	2485553789
    101   100041 Gerald  Strapper  1605  Land Phone   Princess Phone        Home    1499	2485553788
    100   200000 Timothy Diamond   1607  Plane Phone  Executive Phone       Plane	1502	8106329990

    You can see it works up until I get to this code:

    	With BaseQuery AS(
    	SELECT  E.EMP_ID_NUM AS [EmployeeID], E.EMP_FNAME AS [FirstName], E.EMP_LNAME AS [LastName],
    	 MT.MEDIUM_TYPE_ID AS [MediumTypeID],
    	MT.MEDIUM_TYPE_NAME AS [MediumTypeName], 
    	 CM.COMM_MEDIUM_ID AS [CommMediumID],
    	CM.MEDIUM_NAME AS [MediumName], CM.MEDIUM_DESCRIPTION AS [MediumDescription],
    	 EC.VALUE AS [Value]
    	FROM  EMPLOYEE E, MEDIUM_TYPE MT, COMMUNICATION_MEDIUM CM, 
    			EMPLOYEE_COMMUNICATION EC
    	WHERE E.EMP_ID_NUM <> 100000
    	AND E.EMP_ID_NUM = EC.EMP_ID_NUM
    	AND CM.COMM_MEDIUM_ID = EC.COMM_MEDIUM_ID
    	AND  MT.MEDIUM_TYPE_ID = EC.MEDIUM_TYPE_ID)
    	
    SELECT EC.EmployID,
    MAX(CASE WHEN M.TypeName = 'Mobile' THEN EC.Value END) AS MobileN,
    MAX(CASE WHEN M.TypeName = 'Landline' THEN EC.Value END) AS LandNum,
    MAX(CASE WHEN M.TypeName = 'Email' THEN EC.Value END) AS Email,
    MAX(CASE WHEN M.TypeName = 'Fax' THEN EC.Value END) AS FaxN,
    MAX(CASE WHEN EC.PreferredMedium = 1 THEN EC.Value END) AS PrefCommValue,
    MAX(CASE WHEN EC.PreferredType = 1 THEN M.TypeName END) AS PrefCommType
    FROM @employeeList EL  JOIN @employeeCommunication EC
    ON EL.employID = EC.employID
    JOIN @communicationMedium C
    ON C.commMediumID = EC.commMediumID
    JOIN @mediumType M
    ON M.mediumTypeID = EC.mediumTypeID
    GROUP BY EC.employID;

    I’m thinking the case statements needs to use some kind of variables for this to work.  I just don’t know how to apply it.  But, of course, I may be wrong about using a variable.

    If you can think of another way to make this work, please let me know.

    Thank you very much for your patience.  I’m just a little slow at this and I have another job as well that eats up most of my time.

    Thank you very much, Visakh16 for all your help.

    Bosco Dog





    • Edited by bosco dog Saturday, February 3, 2018 6:41 PM
    Saturday, February 3, 2018 5:47 PM
  • Hi Bosco,

    Thanks for your reply.

    Per your reply, I know you have followed Visakh16's solution. However, such complex code script makes me confused, and I couldn't determine what results you want to achieve. Could you please provide the sample data and desired output based on your current problem? So that we could provide more effective solution to your problem.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:43 AM
    Moderator
  • Hi Bosco

    You have included too much information here which I'm not able to understand.

    What is the purpose of these cursors?

    As per the previous model you gave, all would need is something like below to get associated numbers for a employee

    CREATE TABLE MEDIUM_TYPE 
    (
    MEDIUM_TYPE_ID		INTEGER IDENTITY(1499, 1),
    MEDIUM_TYPE_NAME	VARCHAR(35)
    
    PRIMARY KEY (MEDIUM_TYPE_ID)
    );
    
    CREATE TABLE COMMUNICATION_MEDIUM 
    (
    COMM_MEDIUM_ID			INTEGER IDENTITY(1599, 1),
    MEDIUM_NAME			VARCHAR(35),
    MEDIUM_DESCRIPTION		VARCHAR(35)
    
    PRIMARY KEY(COMM_MEDIUM_ID)
    );
    
    CREATE TABLE EMPLOYEE_COMMUNICATION
    (
    COMM_ID				INTEGER IDENTITY(1300, 1) NOT NULL,
    EMP_PK				INTEGER NOT NULL,
    EMP_ID_NUM			INTEGER,
    COMM_MEDIUM_ID		INTEGER,
    MEDIUM_TYPE_ID		INTEGER,
    VALUE				VARCHAR(10),
    PREFERRED_TYPE		BIT,
    PREFERRED_MEDIUM	BIT
    
    
    
    );
    
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Personal Home Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Personal Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Personal Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Work Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Work Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Work Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Princess Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Cockpit Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Executive Phone');
    
    
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Home');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Office');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Truck');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Plane');
    
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1599, 1499, '5175552323', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1600, 1499, '2485553789', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1605, 1499, '2485553788', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (117, 100014, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, 1607, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1605, 1500, '734876900', 1, 1);
    
    	SELECT ec.EMP_ID_NUM,
    	MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS HomePhone1,
    	MIN(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS HomePhone2,
    	MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Mobile Phone' THEN VALUE END) AS MobilePhone,
    	MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Plane' AND cm.MEDIUM_NAME = 'Plane Phone' THEN VALUE END) AS PlanePhone,
    	MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Office' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS OffPhone
    	--cm.MEDIUM_NAME,cm.MEDIUM_DESCRIPTION,mt.MEDIUM_TYPE_NAME,ec.PREFERRED_TYPE,ec.PREFERRED_MEDIUM,ec.VALUE
    	FROM EMPLOYEE_COMMUNICATION ec 
    	JOIN COMMUNICATION_MEDIUM cm 
    	ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID 
    	JOIN  MEDIUM_TYPE mt
    	ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
    	GROUP BY ec.EMP_ID_NUM
    
    
    
    /*
    output
    -------------------------------------------------------
    EMP_ID_NUM	HomePhone1	HomePhone2	MobilePhone	PlanePhone	OffPhone
    ----------------------------------------------------------------------------------------
    100007	NULL	NULL	NULL	8106329990	734876900
    100014	NULL	NULL	NULL	8106329990	NULL
    100041	5175552323	2485553788	2485553789	NULL	NULL
    200000	NULL	NULL	NULL	8106329990	NULL
    */

    Once you do this you get flattened communications details per employee which you can join with other tables like EMPLOYEE etc to get other details for the employee

    Few things to note here

    1. You've multiple Landphones specified for same employee. In that case you would need multiple cross tabs to get them as Landphone1,landphone2 etc

    2. To make above solution flexible you can make it dynamic so that it does crosstabbing based on available medium types and medium names

    You can refer this post for examples

    https://www.databasejournal.com/features/mssql/article.php/3657506/SQLServer-Dynamic-Cross-Tab.htm

    3. Your PREFFERED TYPE and MEDIUM flags are all 1. In actual this will be 1 for only one of the MEDIUM TYPE AND one of the value. This you can cross tab in the same way to return preferred type and preffered value

    i.e like

    MAX(CASE WHEN PREFERRED_TYPE = 1 AND PREFERRED_MEDIUM = 1 THEN VALUE END) AS Preferred,
    ..

    But in your current sace it wont work as you've them all set to 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 7, 2018 10:28 AM
  • I put all that code there to show you what I did to try your idea about getting this to work.  I just wanted you to see that it didn’t work with the testing I was doing.  You know, you are right about all those cursors.  I’m still in the learning stage and appreciate your help.

    I’m thinking that you may not understand what I am trying to accomplish when you added this code:

    •	SELECT ec.EMP_ID_NUM,
    •		MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS HomePhone1,
    •		MIN(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS HomePhone2,
    •		MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Home' AND cm.MEDIUM_NAME = 'Mobile Phone' THEN VALUE END) AS MobilePhone,
    •		MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Plane' AND cm.MEDIUM_NAME = 'Plane Phone' THEN VALUE END) AS PlanePhone,
    •		MAX(CASE WHEN mt.MEDIUM_TYPE_NAME = 'Office' AND cm.MEDIUM_NAME = 'Land Phone' THEN VALUE END) AS OffPhone
    •		--cm.MEDIUM_NAME,cm.MEDIUM_DESCRIPTION,mt.MEDIUM_TYPE_NAME,ec.PREFERRED_TYPE,ec.PREFERRED_MEDIUM,ec.VALUE
    •		FROM EMPLOYEE_COMMUNICATION ec 
    •		JOIN COMMUNICATION_MEDIUM cm 
    •		ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID 
    •		JOIN  MEDIUM_TYPE mt
    •		ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
    •		GROUP BY ec.EMP_ID_NUM
    

    What I am trying to do is giving the user the opportunity to add their own medium type names and medium names.  With the code above, you have determined what the user should have.  My question is, ‘what if the user wants to add their own medium types and medium type names?’ 

    This code will only give them what you have put in the CASE statements above.  What if another type of communication is invented?  The user will have to have another Stored procedure written to accommodate the new technology.  I want it so that the user can add this new technology of communication without having someone re-write the SQL code.

    I am thinking I have to use a variable to accomplish this so that if the user enters a new communication device, it can be added to the program dynamically.

    I know that the following code doesn’t work but I’m trying to show you what I mean by letting the user select their own communication device.

    SELECT EL.empPK AS [EmpPK], EL.employID AS [EmployeeID], EL.firstName AS [fName], 
    EL.lastName AS [lName], CM.commMediumID AS[comMedID], CM.mediumName AS [medName],
    CM.mediumDescription AS [medDes], MT.typeName AS [typeName], MT.mediumTypeID AS [medTypID], 
    EC.value AS [value]
    FROM @employeeList EL LEFT OUTER JOIN @employeeCommunication EC
    ON EL.employID = EC.employID
    LEFT OUTER JOIN @communicationMedium CM
    ON CM.commMediumID = EC.commMediumID
    LEFT OUTER JOIN @mediumType MT
    ON MT.mediumTypeID = EC.mediumTypeID
    --WHERE EC.mediumTypeID IS NOT NULL
    SELECT (EC.employID) FROM @employeeList EL,
    MAX(CASE WHEN MT.typeName = @commMediumID THEN EC.value END) AS [medName]

    In the code above, I’m using the variable @commMediumID and medName in the case statement.

    I hope this may describe what I am trying to do here better.  Any suggestions is appreciated.

    Bosco Dog



    Thursday, February 8, 2018 12:18 AM
  • Hi Bosco,

    Thanks for your reply.

    Per your reply, I find it hard to get useful information from your detailed description. And I'm not quite clear what the purpose of the code statement above is.

    For this type of question, the best is to post:

    1) Queries to CREATE your table(s) including indexes
     2) Queries  to INSERT sample data.
     3) The desired result given the sample, as text or image of excel for example.
     4) A short description of the business rules, and how you got 1-2 of the results
     5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Thanks for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 9, 2018 9:49 AM
    Moderator
  • You still didnt understand my point :)

    Did you read the posted link at all?

    It has the explanation to make it dynamic, which is what you're asking for!

    In any case, the new communication type (say Skyphone for example) needs to be added to MediumType table and corresponding entries for employees need to be added to employeecommunication table. This is any case a manual activity

    Once its done and you use the dynamic logic specified in the link, it will automatically pull medium types set in the table and give the details per employee by dynamic crosstab. I gave a static solution for you to understand the concept first


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 10:29 AM
  • Here's full illustration of what I'm talking about

    Your existing data

    CREATE TABLE MEDIUM_TYPE 
    (
    MEDIUM_TYPE_ID		INTEGER IDENTITY(1499, 1),
    MEDIUM_TYPE_NAME	VARCHAR(35)
    
    PRIMARY KEY (MEDIUM_TYPE_ID)
    );
    
    CREATE TABLE COMMUNICATION_MEDIUM 
    (
    COMM_MEDIUM_ID			INTEGER IDENTITY(1599, 1),
    MEDIUM_NAME			VARCHAR(35),
    MEDIUM_DESCRIPTION		VARCHAR(35)
    
    PRIMARY KEY(COMM_MEDIUM_ID)
    );
    
    CREATE TABLE EMPLOYEE_COMMUNICATION
    (
    COMM_ID				INTEGER IDENTITY(1300, 1) NOT NULL,
    EMP_PK				INTEGER NOT NULL,
    EMP_ID_NUM			INTEGER,
    COMM_MEDIUM_ID		INTEGER,
    MEDIUM_TYPE_ID		INTEGER,
    VALUE				VARCHAR(10),
    PREFERRED_TYPE		BIT,
    PREFERRED_MEDIUM	BIT
    
    
    
    );
    
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Personal Home Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Personal Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Personal Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Email', 'Work Email');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Work Phone' );
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Mobile Phone', 'Work Mobile Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Land Phone', 'Princess Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Cockpit Phone');
    INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Plane Phone', 'Executive Phone');
    
    
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Home');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Office');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Truck');
    INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Plane');
    
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1599, 1499, '5175552323', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1600, 1499, '2485553789', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (101, 100041, 1605, 1499, '2485553788', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (117, 100014, 1606, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, 1607, 1502, '8106329990', 1, 1);
    
    INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (110, 100007, 1605, 1500, '734876900', 1, 1);
    

    The dynamic crosstab procedure

    CREATE PROC EmpCommunicationDetails @EmpID INT = -1 -- when -1 is passed it returns all employees details AS DECLARE @SQL VARCHAR(MAX),@CrosstabList VARCHAR(MAX) IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t SELECT ec.EMP_ID_NUM,mt.MEDIUM_TYPE_NAME,cm.MEDIUM_NAME,ec.VALUE INTO #T FROM EMPLOYEE_COMMUNICATION ec JOIN COMMUNICATION_MEDIUM cm ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID JOIN MEDIUM_TYPE mt ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
    WHERE ec.EMP_ID_NUM = @EmpID
    OR @EmpID = -1

    SELECT @CrosstabList= STUFF((SELECT DISTINCT ',MAX(CASE WHEN MEDIUM_TYPE_NAME = ''' + MEDIUM_TYPE_NAME + ''' AND MEDIUM_NAME = ''' + MEDIUM_NAME + ''' THEN VALUE END) AS [' + MEDIUM_TYPE_NAME + '_' + MEDIUM_NAME + ']' FROM #t FOR XML PATH('')),1,1,'') SET @SQL = 'SELECT EMP_ID_NUM,' + @CrosstabList +' FROM #t c GROUP BY EMP_ID_NUM' EXEC(@SQL) GO


    Now execute it

    EXEC EmpCommunicationDetails
    
    
    /*
    output
    --------------------------------------------------------
    EMP_ID_NUM  Home_Land Phone Home_Mobile Phone Office_Land Phone Plane_Plane Phone
    ----------- --------------- ----------------- ----------------- -----------------------
    100007      NULL            NULL              734876900         8106329990
    100014      NULL            NULL              NULL              8106329990
    100041      5175552323      2485553789        NULL              NULL
    200000      NULL            NULL              NULL              8106329990
    
    
    */

    Now lets add two new medium types say FlyMail and SkyPhone

    This will be added to employee with PK 100 for this illustration

    The insert script would be as below

    DECLARE @MediumTypeID INT,@CommMediumID int
    
    	INSERT INTO MEDIUM_TYPE(MEDIUM_TYPE_NAME) VALUES('Personal');
    
    	SET @MediumTypeID = SCOPE_IDENTITY()
    
    
    	INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Flymail', 'Fmail');
    	SET @CommMediumID = SCOPE_IDENTITY()
    	INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, @CommMediumID, @MediumTypeID, '123005#324', 0, 0);
    
    
    	INSERT INTO COMMUNICATION_MEDIUM(MEDIUM_NAME, MEDIUM_DESCRIPTION) VALUES ('Skyphone', 'Sphone');
    		SET @CommMediumID = SCOPE_IDENTITY()
    
    	INSERT INTO EMPLOYEE_COMMUNICATION (EMP_PK, EMP_ID_NUM, COMM_MEDIUM_ID, MEDIUM_TYPE_ID,
    	VALUE, PREFERRED_TYPE, PREFERRED_MEDIUM) VALUES (100, 200000, @CommMediumID, @MediumTypeID, '356415##23', 0, 0);
    

    Now execute the procedure and you will see the new comm types and value added automatically to the output as below

    EXEC EmpCommunicationDetails
    GO
    
    
    
    /*
    
    output
    --------------------------------------------------------------------------------------------------------------------------
    EMP_ID_NUM  Home_Land Phone Home_Mobile Phone Office_Land Phone Personal_Flymail Personal_Skyphone Plane_Plane Phone
    ----------- --------------- ----------------- ----------------- ---------------- ----------------- -----------------
    100007      NULL            NULL              734876900         NULL             NULL              8106329990
    100014      NULL            NULL              NULL              NULL             NULL              8106329990
    100041      5175552323      2485553789        NULL              NULL             NULL              NULL
    200000      NULL            NULL              NULL              123005#324       356415##23        8106329990
    
    
    */
    

    this illustrates how they can see new medium types without tweaking any logic within the existing procedure

    Now if you want to see only a single employee details, it will only show the communication mediums relevant to him

    see this illustration for that

    EXEC EmpCommunicationDetails 200000
    GO
    
    /*
    output
    ------------------------------------------------------------
    -------
    EMP_ID_NUM  Personal_Flymail Personal_Skyphone Plane_Plane Phone
    ----------- ---------------- ----------------- -----------------
    200000      123005#324       356415##23        8106329990
    
    
    */

    As you see it doesnt list any medium info which are unrelated to the employee being passed

    Hope this makes the concept clear to you


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 12:17 PM
  • Visakh16,

    Thank you so much.  You have no clue how much you have helped me.  Thank you! Thank you! Thank you!  It works perfectly.

    I don’t know how many points you get for this answer, but I’d like to tack on another 1,000 points.  I am in awe at how you did this.  This is perfect.  I knew there had to be a way to do this, but had no clue on how to do it.   Thank you so much.  I’m sure this will help others as well.

    Thanks again, Visakh16!!

    Bosco Dog

    Saturday, February 10, 2018 12:05 AM
  • Visakh16,

    Thank you so much.  You have no clue how much you have helped me.  Thank you! Thank you! Thank you!  It works perfectly.

    I don’t know how many points you get for this answer, but I’d like to tack on another 1,000 points.  I am in awe at how you did this.  This is perfect.  I knew there had to be a way to do this, but had no clue on how to do it.   Thank you so much.  I’m sure this will help others as well.

    Thanks again, Visakh16!!

    Bosco Dog

    You're welcome Bosco :)

    Please mark the post which gave you the solution rather than you own post as the answer to benefit others who follow this thread


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Saturday, February 10, 2018 5:57 AM
    Saturday, February 10, 2018 5:57 AM
  • Vikash16:

    It’s me again.  Just wondering about something; if I wanted to add the EMP_FNAME and EMP_LNAME  (employee’s first name and last name) to this, how would I add that to this solution.  I am not able to add the first and last name.  Does this require creating another stored procedure like this:

    GO
    CREATE PROCEDURE procedure_getNames
    AS 
    BEGIN
    	SELECT EMP_ID_NUM, EMP_FNAME, EMP_LNAME
    	FROM EMPLOYEE
    	
    END
    GO
    

    Then doing a join between the two procedures? 

    I’ve tried adding the E.EMP_FNAME and E.EMP_LNAME into this part of the procedure as shown below and I get the same results as if they were not SELECTed.


    SELECT ec.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME, mt.MEDIUM_TYPE_NAME,cm.MEDIUM_NAME,ec.VALUE
    INTO #T
    	FROM EMPLOYEE E JOIN 
    	EMPLOYEE_COMMUNICATION ec 
    	ON ec.EMP_ID_NUM = E.EMP_ID_NUM
    		 JOIN COMMUNICATION_MEDIUM cm 
    	ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID 
    	JOIN  MEDIUM_TYPE mt
    	ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
            WHERE ec.EMP_ID_NUM = @EmpID
            OR @EmpID = -1
    

    I was wondering what would work here?

    Thank you for your time.

    Bosco Dog


    Monday, February 12, 2018 10:16 PM
  • Visakh16:

    At the bottom of this page I asked you to tell me how to get the Employee's First name and Last Name.  Well, I figured it out myself. Here is my solution:

    DROP PROCEDURE Procedure_EmpCommunicationDetails
    
    GO
    CREATE PROC Procedure_EmpCommunicationDetails
    @EmpID INT = -1 -- when -1 is passed it returns all employees details
    AS
    
    
    DECLARE  @SQLQuery VARCHAR(MAX), @CrosstabList VARCHAR(MAX)--, @EmpIDEN INTEGER
    
    --SET @EmpIDEN = @EmpID
    
    IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
    
    SELECT ec.EMP_ID_NUM, E.EMP_FNAME, E.EMP_LNAME, mt.MEDIUM_TYPE_NAME,cm.MEDIUM_NAME,ec.VALUE
    INTO #T
    	FROM EMPLOYEE E JOIN 
    	EMPLOYEE_COMMUNICATION ec 
    	ON ec.EMP_ID_NUM = E.EMP_ID_NUM
    		 JOIN COMMUNICATION_MEDIUM cm 
    	ON cm.COMM_MEDIUM_ID = ec.COMM_MEDIUM_ID 
    	JOIN  MEDIUM_TYPE mt
    	ON mt.MEDIUM_TYPE_ID = ec.MEDIUM_TYPE_ID
            WHERE ec.EMP_ID_NUM = @EmpID
            --OR @EmpID = -1
    		OR @EmpID = -1
    
    
    	SELECT @CrosstabList= STUFF((SELECT DISTINCT ',MAX(CASE WHEN MEDIUM_TYPE_NAME = ''' + MEDIUM_TYPE_NAME + ''' AND MEDIUM_NAME = ''' + MEDIUM_NAME + ''' THEN VALUE END) AS ['  + MEDIUM_TYPE_NAME + '_' + MEDIUM_NAME + ']'
    FROM #t
    	FOR XML PATH('')),1,1,'')
    
    
    	SET @SQLQuery = 'SELECT  EMP_ID_NUM, EMP_FNAME, EMP_LNAME,' +   @CrosstabList +'
    	FROM #t c
    	GROUP BY EMP_ID_NUM, EMP_FNAME, EMP_LNAME'
    	EXEC (@SQLQuery)
    	GO
    
    	EXEC Procedure_EmpCommunicationDetails
    

    Thank you, again for all your help.  Do you see anything in my solution that might give me potential problems in the future?

    Thanks again.

    Bosco Dog

    Wednesday, February 14, 2018 1:45 AM