We help IT Professionals succeed at work.

Create a Temp Table, inserting specific values to avoid duplicating rows

Annette Wilson, MSIS
Annette Wilson, MSIS used Ask the Experts™
on
I need to create one row of data for my emailing program.

I have one table for Employee Information including Functional Mgr
Another Table has Employee Assets and each Asset has an assigned asset manager.

In my Email program, I needt to create one Row that Includes Functional Mgr Email & Name, Talent Mgr Email and Name and the Asset Manager Email and Name.

I don't have to worry about the assets.  I have created a query within an HTML body that returns a table with those values.

I do need to include all Asset Managers, the one Functional Manager and the one Talent Manager in the email.

I'm thinking that I can create a temp table to pull the emal addresses from in my stored procedure.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you clarify your spec with some example, please? it may be easier for us to help you.
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
bpnchan,
Thank you for responding.  The following query is returning the row of details data for each employee but since the employee may have a number of assets assigned, this returns the details and duplicates the lines because of the different asset managers.


SELECT  Distinct   CEmpDetails.EmployeeID, 
                   CEmpDetails.FunctionalMgrID, 
                   CEmpDetails.Job, 
                   CEmpDetails.WBS, 
                   CEmpDetails.LastworkDate, 
                   CEmpDetails.EmployeeName, 
                   CEmpDetails.FunctionalMgrName, 
                   CEmpDetails.FunctionalMgrEmail, 
                   CEmpDetails.TalentMgrName, 
                   CEmpDetails.TalentMgrEmail, 
                   CEmpDetails.Library, 
                   AMgr.AssetMgrName, 
                   AMgr.AssetMgrEmail
FROM         CEmpDetails Left Outer Join
             AMgr ON CEmpDetails.EmployeeID = AMgr.EmployeeID
WHERE     (CEmpDetails.EmailSent = 0) And EmployeeName is not NULL

Open in new window

Commented:
Hi,
   can you please attach the table structures as well. so it will be helpful for us.

Commented:
SELECT  CEmpDetails.EmployeeID,
            CEmpDetails.FunctionalMgrID,
            CEmpDetails.Job,
            CEmpDetails.WBS,
            CEmpDetails.LastworkDate,
            CEmpDetails.EmployeeName,
            CEmpDetails.FunctionalMgrName,
            CEmpDetails.FunctionalMgrEmail,
            CEmpDetails.TalentMgrName,
            CEmpDetails.TalentMgrEmail,
            CEmpDetails.Library,
            AMgr.AssetMgrName,
            AMgr.AssetMgrEmail
FROM         CEmpDetails Left Outer Join
             AMgr ON CEmpDetails.EmployeeID = AMgr.EmployeeID
WHERE     (CEmpDetails.EmailSent = 0) And EmployeeName is not NULL
Group By  CEmpDetails.EmployeeID,
            CEmpDetails.FunctionalMgrID,
            CEmpDetails.Job,
            CEmpDetails.WBS,
            CEmpDetails.LastworkDate,
            CEmpDetails.EmployeeName,
            CEmpDetails.FunctionalMgrName,
            CEmpDetails.FunctionalMgrEmail,
            CEmpDetails.TalentMgrName,
            CEmpDetails.TalentMgrEmail,
            CEmpDetails.Library,
            AMgr.AssetMgrName,
            AMgr.AssetMgrEmail
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
mimram18, thank you for responding.  I still get duplicate rows with the query because there are multiple asset manager for each employee.

Is there a way that I can
selecting all of the details, then select the asset managers and add them to individual columns in as temp table?  There is only a possiblity of having 7 asset managers.

Theis whould give me one row of data.

Commented:
Can you plz give me some sample data ? will help to understand the relationship between tables.
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
I have attached a diagram of these test tables.  I just recently added the AssetMgr table hoping it would help me.  Can delete it if necessary.

I was looking to dynamically add columns sort of like the following example... only, I would loop through the EmployeeAssets table and add a column for each Asset Manager and Asset Manager Email.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26476687.html
TableDiagram.doc
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
I'm going to try working through the logic of one of the samples.  Just found another...  Just have to get a couple hours of sleep first.


DECLARE @VisitingHoursStartTime AS DATETIME --Cursor Local Variables
    DECLARE @VisitingHoursEndTime AS DATETIME

    SET @VisitingHoursStartTime = '09:00' -- morning 9 am
    SET @VisitingHoursEndTime = '17:00' -- evening 6 pm

    CREATE TABLE #Temp(Id NVARCHAR(50)) -- Creating Temp Table

    -- Loop to add columns to temp table
    WHILE (@VisitingHoursStartTime <= @VisitingHoursEndTime)
    BEGIN
        DECLARE @DynamicSQL VARCHAR(500)

        IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('[dbo].[#Temp]') AND NAME = '@VisitingHoursStartTime' )
        BEGIN
            SET @DynamicSQL = 'ALTER TABLE #Temp ADD ['+ CONVERT(VARCHAR(5),@VisitingHoursStartTime,108) +'] NVARCHAR(100) NULL'
            EXECUTE (@DynamicSQL)
        END

        SET @VisitingHoursStartTime = DateADD(MI,15,@VisitingHoursStartTime)
        IF @VisitingHoursStartTime = @VisitingHoursEndTime
            BREAK;
    END

    SELECT * FROM #Temp

    IF EXISTS(SELECT * FROM sysobjects WHERE NAME LIKE '%#Temp%')
        DROP TABLE #Temp
END

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you could also do the query directly to get distinct values:
http://www.experts-exchange.com/A_3203.html
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
angellll,
Does it matter that the tables are used as utilities.... all previous data is deleted first then new data is automatically added.  The primary keys change.   Don't know if this matters??
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
it does not matter, because your "DISTINCT" is not using the generated key values, but must rely on the column data you actually want to insert. ... if I got the question right ...
Sr. Programmer Analyst
Commented:
Thank you for all of your help.  I really appreciate the valuable insight you have provided.  The users for this scenario accepted the scenario of me sending differnent emails according to Asset Manager and listed addresses but copying the other managers on the emails.
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
Users accepted my solution.