I am attempting to write a sql statement that displays a list of operations per job and assembly sequence. Right now it lists all of the operations for all of the assemblies.
The SQL so far:
select distinct
jobhead.JobNum, JobHead.PartNum,jobhead.ProdCode as ProdGroup,joboper.AssemblySeq as ASM,
(REPLACE(REPLACE(((select
(case when jobhead.JobClosed=1 and joboper.OpComplete=1 then CAST(concat(joboper.AssemblySeq,‘:’,joboper.oprseq,‘:’,joboper.opcode) AS VARCHAR) + ‘,’ else ‘’ end) as OpCode
from Erp.JobOper as JobOper where JobOper.JobNum = jobhead.jobNum order by case when joboper.AssemblySeq=0 then 999 else joboper.AssemblySeq end
FOR XML PATH(‘’))) , ‘’,‘’),‘’,‘’)) as Operations
from Erp.JobHead as Jobhead inner join erp.joboper as joboper on jobhead.jobnum=joboper.JobNum
Here is an example of the current results:
JobNum | Part | ProdGroup | ASM | Operations |
---|---|---|---|---|
1234 | ABC | Group1 | 0 | 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01, |
1234 | ABC | Group1 | 1 | 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01, |
1234 | ABC | Group1 | 2 | 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01, |
How I want this example to look:
JobNum | Part | ProdGroup | ASM | Operations |
---|---|---|---|---|
1234 | ABC | Group1 | 0 | 0:10:ASY01,0:20:QC01, |
1234 | ABC | Group1 | 1 | 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01 |
1234 | ABC | Group1 | 2 | 2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01 |