SQLTeam.com | Weblogs | Forums

Bring back only serialnumbers that have a set of operations


#1

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


#2

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


#3
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)

#4

Thanks Scott!