Hello,
Dealing with manufacturing world data here. My question is how can I get only serial numbers of machines that have a specific set of operation codes. If one of the codes is missing I am not interested in that serial number. It is related to a process. If 5 of the processes have happened it means the process is done and we want that serial number. If one of them is missing then process is not done yet. here is a sample data
create table #operationcodes(operationcode varchar(50))
insert into #operationcodes
select 'AB' union
select 'CD' union
select 'EF' union
select 'GH' union
select 'IJ'
declare @binarychecksum nvarchar(max);
select @binarychecksum = BINARY_CHECKSUM(*) from #operationcodes;
create table #machinelogs(machinesn varchar(60), operationcode varchar(50))
insert into #machinelogs
select sn.sn, oc.operationcode
from #operationcodes oc
cross apply (
select '676734898A' sn
) sn
union
select sn2.sn, oc.operationcode
from #operationcodes oc
cross apply (
select '676734898B' sn
) sn2
union
select sn3.sn, oc.operationcode
from #operationcodes oc
cross apply (
select '676734898C' sn
) sn3
where oc.operationcode <> 'IJ'
select * From #machinelogs;
;with cteBCS(bcs, machinesn)
as
(
select BINARY_CHECKSUM(operationcode) bcs, machinesn
From #machinelogs
--group by machinesn
)
select *
from cteBCS
where bcs = @binarychecksum
drop table #operationcodes
drop table #machinelogs
In this case 676734898C should not be part of my desired final list as it is missing operation code IJ
I have tried IN ('LIST OF OPERATION CODES'), I have tried like 'OC%' and I am about to try maybe binarychecksum to mosh all of the OCodes together.
Thanks