dear masbin , issue ini terjadi karena di OM6 ada yang double
di bawah ini cara mengecek data double
makasih
SELECT
DISTINCT om2.ObjectID,
om2.ObjectClass,
om2.RelationshipType,
om2.RelationshipClass,
om2.RelationshipObject AS Parent,
md2.EmployeeID AS EmployeeID,
md1.FullName,
md1.Picture,
CASE WHEN (om1.PositionHead = 1) THEN '1' ELSE '0' END AS PositionHead,
om1.Abbreviation,
om1.ObjectDescription AS Position,
om6.CostCenter,
om6.CostElement,
om6.EmployeeType,
om6.EmployeeSubType,
om6.PositionType,
om6.StructureType,
om6.VacancyStatus,
om6.Notes,
om6.AdditionalSupervisor1,
om6.AdditionalSupervisor2,
om6.AdditionalSupervisor3,
j.Abbreviation,
j.ObjectDescription AS Job
FROM
dbo.PHROM0002 AS om2 WITH (NOLOCK)
LEFT JOIN PHROM0001 AS om1 WITH (NOLOCK) ON om2.ObjectID = om1.ObjectID
AND om1.ObjectClass = 'P'
AND om1.StartDate <= '20241119'
AND om1.EndDate >= '20241119'
LEFT JOIN PHROM0002 AS e WITH (NOLOCK) ON om2.ObjectID = e.ObjectID
AND e.ObjectClass = 'P'
AND e.RelationshipClass = 'E'
AND e.RelationshipDirectory = 'A'
AND e.StartDate <= '20241119'
AND e.EndDate >= '20241119'
LEFT JOIN PHRPA0002 AS md2 WITH (NOLOCK) ON md2.Position = e.ObjectID
AND md2.EmployeeStatus = '01'
AND md2.StartDate <= '20241119'
AND md2.EndDate >= '20241119'
LEFT JOIN PHRPA0001 AS md1 WITH (NOLOCK) ON md2.EmployeeID = md1.EmployeeID
AND md1.StartDate <= '20241119'
AND md1.EndDate >= '20241119'
LEFT JOIN PHROM0006 AS om6 WITH (NOLOCK) ON om2.ObjectID = om6.ObjectID
AND om6.StartDate <= '20241119'
AND om6.EndDate >= '20241119'
LEFT JOIN PHROM0001 AS j WITH (NOLOCK) ON j.ObjectID = om6.Job
AND j.ObjectClass = 'J'
AND j.StartDate <= '20241119'
AND j.EndDate >= '20241119'
WHERE
om2.ObjectClass = 'P'
AND om2.RelationshipDirectory = 'B'
AND om2.RelationshipObject = '00000222'
AND om2.RelationshipClass = 'O'
AND om2.StartDate <= '20241119'
AND om2.EndDate >= '20241119'
