SQLTeam.com | Weblogs | Forums

Bring back only serialnumbers that have a set of operations


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
select sn2.sn,  oc.operationcode
  from #operationcodes oc
 cross apply (
              select '676734898B' sn 
			 ) sn2
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)
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.


ok binary checksum seems to work! will test out with more sample data, millions of rows

SELECT ml.machinesn
FROM #machinelogs ml
INNER JOIN #operationcodes oc ON oc.operationcode = ml.operationcode
GROUP BY ml.machinesn
HAVING COUNT(*) = (SELECT COUNT(*) FROM #operationcodes)
1 Like

Thanks Scott!