Here is my Query:
/*
select
a.siteid,
ast.vendor,
d.name as [Vendor-Name],
ast.modelnum,
ast.description,
a.wonum,
Cast(a.reportdate as Date) as [Report-Date],
a.description AS request,
a.assetnum,
a.maintby,
a.worktype,
a.totaldwntime AS downtime,
a.status,
c.ldtext AS worklog,
Cast(b.createdate as date) as [Log-Entry-Date],
p1.displayname AS Who
--b.worklogid
FROM dbo.workorder AS a
LEFT OUTER JOIN dbo.worklog AS b ON a.wonum = b.recordkey --OR (b.recordkey is null)
LEFT OUTER JOIN dbo.longdescription AS c ON b.worklogid = c.ldkey
LEFT OUTER JOIN dbo.person AS p1 ON b.createby = p1.personid
LEFT OUTER JOIN asset as ast ON a.assetnum = ast.assetnum
LEFT OUTER JOIN dbo.companies as d ON ast.vendor = d.company
WHERE (c.ldownertable ='worklog') AND (a.wonum in (select wonum from dbo.workorder as d group by wonum having (a.worktype not in ('ppm','mpm','ds','tspm')) OR (b.recordkey is null)
AND (a.reportdate > DATEADD(YEAR,-2,GETDATE())) AND (a.istask =0) )) AND (a.siteid in ('p202','p203'))
AND (a.maintby not in ('ms'))
ORDER BY a.reportdate desc, a.siteid asc, ast.modelnum, a.assetnum
*/
/* Result*/
siteid | vendor | Vendor-Name | modelnum | description | wonum | Report-Date | request | assetnum | maintby | worktype | downtime | status | worklog | Log-Entry-Date | Who |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
P202 | 202963 | Conveyor Technologies Inc. | DS-.8M-RL-2232 | CTI DOUBLE DESTACKER | 6215810 | 2020-12-03 | Machine not dropping boards | 561/13/005 | SA | TSCM | NULL | TS_RVW | Made adjustment to gap where boards would drop. Started back production. | 2020-12-03 | Michael Bailey |
P202 | 202963 | Conveyor Technologies Inc. | DS-.8M-RL-2232 | CTI DOUBLE DESTACKER | 6215810 | 2020-12-03 | Machine not dropping boards | 561/13/005 | SA | TSCM | NULL | TS_RVW | Called back an hour later and found gap needed to be adjusted again. Found the gears were sticking while adjusting. Called Maintenance to check out gears. | 2020-12-03 | Michael Bailey |
It duplicates the record due to the left join and all I want to id capture the worklog and show in one record.
Any help or guidance is appreciated.
Thanks
Landon H1