September 28, 2011 at 6:17 pm
Hi All,
I have 4 tables,
Employee:
Id, code, name, pos_def_id
213, Z300, Diana Benk, 108
ShiftAssigment:
Id, shiftId, EmployeeID, date, assigmentReason_def_id
180, 3, 213, 2011-09-23, 198
Shift:
Id,code,desc,locationID,duration
3, NSW HR1 AM, 52,210
Definition:
Id, DefinitionCategoryID, description
108, 2, Full Time
198, 1, Medical
You can see employee Diana ID 213 is full time(108) and has been assigned shift 3 which is a medical(198) shift type.
The sql i have written shows the employee details and shift but not the full time or part time as the case maybe. I need to reference the definition table for each shift assigned and for each employee.
Current SQL:
SELECT Sh.Description, Sh.LocationID, Sh.StartTime, sh.Duration,
sa.ShiftID, sa.EmployeeID, sa.Date,
E.Code AS EmpCode, E.Name, E.IsActive,
D.DefinitionCategoryID,AssignmentReason_DefID, D.Description AS [Hearing Type],
E.Position_DefID, D.Description AS [Shift Type]
FROM
ShiftAssignment sa
INNER JOIN
Definition d ON D.ID = sa.AssignmentReason_DefID
INNER JOIN
Shift sh ON Sh.ID = sa.ShiftID
INNER JOIN
Employee e ON E.ID = sa.EmployeeID
Current Results:
Description LocationID StartTime Duration ShiftID EmployeeID Date EmpCode Name IsActive DefinitionCategoryID AssignmentReason_DefID Hearing Type Position_DefID Shift Type
NSW HR1 AM 52 00:00.0 210 3 213 00:00.0 Z300 Diana Benk 1 1 198 Show Cause 108 Show Cause
I need the last column to be 'full time' not 'show cuase'.
Thanks
September 28, 2011 at 9:05 pm
your data and problem is not clear. Can you post your sample data and expected result in excel sheet and post it as attachment.
Regards,
Raj
September 28, 2011 at 9:15 pm
Based on what you are providing the problem does not match the data.
It appears that you may have a Definition ID that has multiple descriptions but different categories. If that is the case, then you will need to also include the category in your join statement or where clause.
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
September 28, 2011 at 10:36 pm
It was pretty easy in the end, added last join. Thanks for trying all.
SELECT Sh.LocationID, sh.code,Sh.Description, Sh.StartTime, Sh.Duration,
sa.id,sa.ShiftID, sa.EmployeeID, sa.Date,
E1.Code AS EmpCode,E1.Name, E1.IsActive,
AssignmentReason_DefID,
D1.Description AS [Hearing Type],
E1.Position_DefID,D2.Description AS [Shift Type]
FROM ShiftAssignment sa
INNER JOIN
Definition d1 ON d1.ID = sa.AssignmentReason_DefID
INNER JOIN
Shift sh ON Sh.ID = Sa.ShiftID
INNER JOIN
Employee E1 ON e1.id= sa.EmployeeID
INNER JOIN
Definition d2 ON d2.id = E1.position_defid
September 28, 2011 at 11:29 pm
Glad you got it working.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply