March 23, 2010 at 8:22 am
Hi all,
I am trying to build a training matrix that shows the employee number, the program, the machine where they were trained and the level of training.
So far I came up with this:
SELECT EMPLOYEE_NUMBER AS [EMPLOYEE], PROGRAM,
CASE WHEN MACHINE = '104' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 104],
CASE WHEN MACHINE = '109' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 109],
CASE WHEN MACHINE = '117' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 117],
CASE WHEN MACHINE = '119' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 119],
CASE WHEN MACHINE = '120' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 120],
CASE WHEN MACHINE = '121' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 121],
CASE WHEN MACHINE = '125' THEN TRAINING_LEVEL ELSE '-' END AS [IMM 125]
FROM #mytable AS T
WHERE TIMESTAMP IN
(SELECT MAX(TIMESTAMP) AS TIME
FROM #mytable
GROUP BY EMPLOYEE_NUMBER, MACHINE, PROGRAM)
group by EMPLOYEE_NUMBER, MACHINE, PROGRAM, TRAINING_LEVEL
ORDER BY EMPLOYEE_NUMBER
EMPLOYEE PROGRAM IMM 104 IMM 109 IMM 117 IMM 119 IMM 120 IMM 121 IMM 125
--------------- ---------------- ------- ------- ------- ------- ------- ------- -------
1234 37987 - - - - A - -
1234 37987 - - - - - - B
1234 BMX222 - - - - - - D
9999 TOY - - C - - - -
9999 37987 - - - - D - -
9999 LX ASSEM - - - - - B -
280173 TOY D - - - - - -
280173 GMX272 - A - - - - -
280173 37987 - - C - - - -
280173 LX *** F - - - B - - -
280173 37987 - - - - A - -
280173 LC FH - - - - B - -
(12 row(s) affected)
Please note rows 9 and 11, they have the same employee, the same program but are in different rows, understandably because of the 'group by Training_Level'.
What I would like to have is the same Employee, same Program, different Machine on the same line, but I can't get around the Group By clause for the Training Level.
Here is my table and data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[EMPLOYEE_NUMBER] [int] NULL,
[MACHINE] [nvarchar](8) NULL,
[PROGRAM] [nvarchar](16) NULL,
[TRAINING_LEVEL] [nvarchar](1) NULL CONSTRAINT [DF_TBL_TRAINING_MATRIX_TRAINING_LEVEL] DEFAULT (N'D'),
[TIMESTAMP] [datetime] NULL CONSTRAINT [DF_TBL_TRAINING_MATRIX_TIMESTAMP] DEFAULT (getdate())
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, EMPLOYEE_NUMBER, MACHINE, PROGRAM, TRAINING_LEVEL, TIMESTAMP)
SELECT '1','280173','109','GMX272','A','Jan 24 2010 1:52PM' UNION ALL
SELECT '2','280173','104','TOY','D','Feb 24 2010 1:56PM' UNION ALL
SELECT '3','280173','119','LX *** F','B','Feb 24 2010 1:57PM' UNION ALL
SELECT '5','1234','125','BMX222','D','Feb 24 2010 3:52PM' UNION ALL
SELECT '6','1234','125','37987','B','Feb 24 2010 3:53PM' UNION ALL
SELECT '7','9999','121','LX ASSEM','B','Feb 24 2010 3:53PM' UNION ALL
SELECT '8','9999','117','TOY','C','Feb 24 2010 3:53PM' UNION ALL
SELECT '11','9999','120','37987','D','Feb 24 2010 10:22PM' UNION ALL
SELECT '12','280173','120','37987','D','Feb 25 2010 3:34PM' UNION ALL
SELECT '13','280173','120','37987','D','Feb 25 2010 5:10PM' UNION ALL
SELECT '14','1234','120','37987','A','Feb 25 2010 6:18PM' UNION ALL
SELECT '15','280173','120','37987','D','Mar 1 2010 4:20PM' UNION ALL
SELECT '16','280173','120','37987','A','Mar 4 2010 5:42PM' UNION ALL
SELECT '17','280173','120','LC FH','B','Mar 22 2010 1:00PM' UNION ALL
SELECT '18','280173','117','37987','C','Mar 22 2010 4:50PM'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
Thank you so much for your help!
March 24, 2010 at 3:57 am
Would this help?
SELECT Employee_Number, Program,
CHAR(COALESCE([104],0)) AS [IMM 104],
CHAR(COALESCE([109],0)) AS [IMM 109],
CHAR(COALESCE([117],0)) AS [IMM 117],
CHAR(COALESCE([119],0)) AS [IMM 119],
CHAR(COALESCE([120],0)) AS [IMM 120],
CHAR(COALESCE([121],0)) AS [IMM 121],
CHAR(COALESCE([121],0)) AS [IMM 125]
FROM (SELECT Employee_Number, Machine, Program,
ASCII (Training_Level) as TL FROM #myTable
WHERE TIMESTAMP IN
(SELECT MAX(TIMESTAMP) AS TIME
FROM #mytable
GROUP BY EMPLOYEE_NUMBER,
MACHINE, PROGRAM)) AS src
PIVOT (SUM(TL) FOR Machine IN ([104],[109],[117],[119], [120],[121],
[125] )) AS pvt
---------------------------------------------------------------------------------
March 24, 2010 at 6:55 am
I believe you got it!!!!
Thank you so much!
March 24, 2010 at 7:01 am
you are welcome.
---------------------------------------------------------------------------------
June 26, 2010 at 12:16 am
Hello Friends......
Great information.Thanks for sharing this information with all of us.Keep sharing more information in the future.I hope this will help many more like me.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply