August 12, 2014 at 10:16 am
The datatype for everything in this query is char. I would like help with two things, 1) There are two columns, P_Pos (Primary Position) and S_Pos(Secondary Positions). The Primary position comes from the Employee Master table which only includes the employee's primary position. The Emp_Positions table includes all positions attached to each employee but there is nothing to designate their primary position. I would like the primary position not to show up in the secondary position column. 2) I'm not sure this is possible but the way the query generates now (I've included a sample) is all the positions are listed in a column and I would like the data to generate in a row. I understand that I could write a line of code for each position but we have many positions and most people only have one, although some have as many as 20. If I used a line of code for each position I would end up with a result set that would show each employee, our dozens of positions they don' work at, and the one or two they do work at. I am hoping that I can get all the positions listed in a row as such: eg) 14, FT, 300D2,111D2, 133D2, rather than: 14, FT, 133D2, NULL, NULL, NULL, 300D2, NULL, NULL, NULL, NULL, 111D2
SELECT RIGHT (Em.Empno,4) AS EmpID
,CASE em.PayGrp
WHEN'005' THEN 'PT'
WHEN'001'THEN 'FT'
WHEN'002' THEN 'FT'
END AS FT_PT
,em.Position AS P_POSITION
,p.Position as S_Position
FROM Employee_Master AS em
JOIN Emp_Positions AS p
ON em.Empno=p.Empno
JOIN Location AS l
ON em.Locn = l.Locn
WHERE EM.EmployStatus <> 11
ORDER BY Em.Empno
EmpIDFT_PTP_PosS_Pos
13PT546PT 546PT
13PT546PT 279PT
14FT133D2 300D2
14FT133D2 111D2
14FT133D2 133D2
15PT106PT 133PT
15PT106PT 140PT
15PT106PT 106PT
August 12, 2014 at 11:01 am
Seems like a pivot would probably work for what you have described.
You can see an example here. http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
With a table structure (ddl) and more consumable data, somebody could probably help in more detail.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 7, 2014 at 10:23 pm
Your summary mentioned that the Employee Master table includes only the primary position while the emp_positions table has every position the employee holds, if I understood correctly.
A possible way to keep your primary position out of the secondary position column would be to exclude the position listed in the Employee Master table from the possible choices for that other column.
E.g., if Joe is Director (primary position) and honorary barista, he'd have Director in Employee Master but Director and honorary barista in Emp_positions.
Your primary position column would thus select only the role from the Employee Master table, and non-primary (secondary) will be any of Joe's other positions from emp_positions that do not match the one in Employee Master.
Sorry I can't think of a tidy way to handle multiple non-primary roles though.
Becky
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply