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.