USE [TPL1] GO /****** Object: StoredProcedure [dbo].[WFOMatchFileReport] Script Date: 8/6/2019 3:25:14 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[WFOMatchFileReport]( @GID NVARCHAR(50) ) AS BEGIN SET NOCOUNT ON; --********************************************************************************************************************************** /*1. Matched*/ select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s-- where isnull(wfoid,'')<>''-- and GID='54438369-0bac-48e0-b229-49e65953f753' ----and doNotProcess=0 and Comments not like '%'+'}, Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%' and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 --and isduplicate=0 order by ScientificName--486 ------------------------------%%%%%%%%%%%%%%%%%%%%%%%% /*2. Only diff family first, followed by only diff tax. Status second and followed by family and tax. Status third*/ --family and tax. Status Not match but assigned wfoid select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 order by Comments_family,Comments_taxonomicStatus--0 ---- --1 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn --,WFOID_old,doNotProcess_reason_Backbone,Comments_old from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 order by ScientificName--0 --union --2 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'--1074 --and isduplicate=0 order by ScientificName --union --3 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'--53 --and isduplicate=0 order by ScientificName -- --4 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 order by ScientificName--0 --5 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData where RECID not in ( select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName-- union --2 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName union --3 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName -- union --4 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 )and isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments like '%'+' Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'--51 --and isduplicate=0 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /*3. scientificName maymatch but assigned wfoid*/ -- select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 --and Comments like '%'+' Not Match:'+'%' and Comments like 'May Match: ScientificName {'+'%'--9 --and isduplicate=0 order by scientificName-- -------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /*3. Only diff authors first followed by Authors and Family different second followed by authors and tax. Status third followed by Authors, Family and tax status different fourth */ --todo seperate select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and isduplicate=0 order by ScientificName-- 22 select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData m where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and isduplicate=0 and Comments_Authors like 'May Match:%' select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData m where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and isduplicate=0 and Comments_Authors like 'May Match:%' --and RECID in ('urn:lsid:ipni.org:names:20001136-1') order by ScientificName-- 20 ---- select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData m where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and isduplicate=0 and Comments_Authors like 'Not Match:%' order by ScientificName-- 22 ---------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ----4. Miscellaneous select WFOID ,ScientificNameWithAuthors scientificName,scientificNameWithoutAuthor ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where RECID not in ( select RECID from WFOMatchFileInputData s where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' --and Comments like '%'+', Not Match: Authors {'+'%' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' ) and isnull(wfoid,'')='' and Comments<>'Match not found' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 --and isduplicate=0 order by ScientificName--22 miscellinuous select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData m where RECID not in ( select RECID from WFOMatchFileInputData m where isnull(wfoid,'')='' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' --and Comments like '%'+', Not Match: Authors {'+'%' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' ) and isnull(wfoid,'')='' and Comments<>'Match not found' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments_Authors like 'May Match:%' and scientificNameAuthorship like '%ex%' order by ScientificName select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where RECID not in ( select RECID from WFOMatchFileInputData s where isnull(wfoid,'')='' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 and Comments<>'Match not found' --and Comments like '%'+', Not Match: Authors {'+'%' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' ) and isnull(wfoid,'')='' and Comments<>'Match not found' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone<>1 --and isduplicate=0 --and Comments_ScientificName like 'May Match:%' order by ScientificName--22 miscellinuous ---------------%%%%%%%%%%%%%%%%%%%%%%% /*5. Match not found */ --todo select WFOID ,ScientificNameWithAuthors scientificName ,replace(SUBSTRING(Comments_ScientificName,patindex('%{%',Comments_ScientificName)+1,(patindex('%}%',Comments_ScientificName))),'}','') WFO_scientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,replace(SUBSTRING(Comments_Authors,patindex('%{%',Comments_Authors)+1,(patindex('%}%',Comments_Authors))),'}','') WFO_Author ,isnull([rank],'') TaxonRank ,replace(SUBSTRING(comments_taxonRank,patindex('%{%',comments_taxonRank)+1,(patindex('%}%',comments_taxonRank))),'}','') WFO_TaxonRank ,ISNULL(Family,'') Family ,replace(SUBSTRING(Comments_family,patindex('%{%',Comments_family)+1,(patindex('%}%',Comments_family))),'}','') WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,replace(SUBSTRING([Comments_taxonomicStatus],patindex('%{%',comments_taxonomicstatus)+1,(patindex('%}%',comments_taxonomicstatus))),'}','') comments_taxonomicstatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,replace(SUBSTRING(Comments_namePublishedIn,patindex('%{%',Comments_namePublishedIn)+1,(patindex('%}%',Comments_namePublishedIn))),'}','') WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')='' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 --and doNotProcess_backbone=0 --check and Comments='Match not found' --and isduplicate=0 order by ScientificName--45 ----------------report for donotprocess=1 ------------------------------ /*1. Matched*/ select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0-- and Comments not like '%'+'}, Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%' and doNotProcess_1_Backbone=1 --and isduplicate=0 order by ScientificName--0 ------------------------------%%%%%%%%%%%%%%%%%%%%%%%% /*2. Only diff family first, followed by only diff tax. Status second and followed by family and tax. Status third*/ --family and tax. Status Not match but assigned wfoid select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 order by Comments_family,Comments_taxonomicStatus--0 --1 select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'--0 --and isduplicate=0 order by ScientificName --order by ScientificName- --union --2 select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'--0 --and isduplicate=0 order by ScientificName --3 select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'--1 --and isduplicate=0 order by ScientificName --union --4 select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 order by ScientificName--0 --5 select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData where RECID not in ( --1 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and GID='54438369-0bac-48e0-b229-49e65953f753' and Comments like '%'+' Not Match:'+'%'-- and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'--0 --and isduplicate=0 --order by ScientificName --order by ScientificName- union --2 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and GID='54438369-0bac-48e0-b229-49e65953f753' and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName union --3 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and GID='54438369-0bac-48e0-b229-49e65953f753' and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus not like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family not like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName union --4 select RECID from WFOMatchFileInputData s where isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments_taxonomicStatus like '%'+'Not Match: TaxonomicStatus'+'%' and Comments_family like '%'+'Not Match: Family'+'%'-- and Comments_namePublishedIn not like '%'+'Not Match: NamePublishedIn {'+'%' and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --order by ScientificName-- ) and isnull(wfoid,'')<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' --and doNotProcess=0 and doNotProcess_1_Backbone=1 and Comments like '%'+' Not Match:'+'%'-- and Comments not like 'May Match: ScientificName {'+'%'-- --and isduplicate=0 --check Cardamomum,Amomum--30 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /*3. scientificName maymatch but assigned wfoid*/ -- select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')<>'' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_backbone=0 and doNotProcess_1_Backbone=1 --and Comments like '%'+' Not Match:'+'%' and Comments like 'May Match: ScientificName {'+'%'--0 --and isduplicate=0 order by scientificName--,5 -------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /*3. Only diff authors first followed by Authors and Family different second followed by authors and tax. Status third followed by Authors, Family and tax status different fourth */ --todo seperate select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where isnull(wfoid,'')='' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_backbone=0 and doNotProcess_1_Backbone=1 and Comments<>'Match not found' --and Comments like '%'+', Not Match: Authors {'+'%' --and Comments not like '%'+', Not Match: Family {'+'%' --and Comments not like '%'+', Not Match: Taxonomicstatus {'+'%' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and Comments not like '%'+'May Match: Authors {'+'%' --and isduplicate=0 order by ScientificName--0 ---------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --4. Miscellaneous select WFOID ,ScientificNameWithAuthors scientificName ,comments_scientificName_doNotProcess WFO_ScientificName ,ISNULL(FullScientificNameAuthorship,'') ScientificNameAuthorship ,comments_Authors_doNotProcess WFO_ScientificNameAuthorship ,isnull([rank],'') TaxonRank ,comments_taxonRank_doNotProcess WFO_TaxonRank ,ISNULL(Family,'') Family ,comments_family_doNotProcess WFO_Family ,ISNULL(TaxonomicStatus,'') TaxonomicStatus ,comments_taxonomicStatus_doNotProcess WFO_TaxonomicStatus ,ISNULL(fullNamePublishedIn,'') NamePublishedIn ,Comments_namePublishedIn_doNotProcess WFO_NamePublishedIn from WFOMatchFileInputData s where RECID not in ( select RECID from WFOMatchFileInputData s where isnull(wfoid,'')='' --and doNotProcess=0 and doNotProcess_backbone=0 and doNotProcess_1_Backbone=1 and Comments<>'Match not found' and GID='54438369-0bac-48e0-b229-49e65953f753' --and Comments like '%'+', Not Match: Authors {'+'%' and Comments like '%'+'Not Match'+'%' and Comments not like 'May Match: ScientificName {'+'%' --and isduplicate=0 ) and isnull(wfoid,'')='' and Comments<>'Match not found' --and doNotProcess=0 and GID='54438369-0bac-48e0-b229-49e65953f753' and doNotProcess_backbone=0 and doNotProcess_1_Backbone=1 --and isduplicate=0 order by ScientificName--5 miscellinuous check --5. duplicate wfoids ------------------------------------------------------------------------------ --5. duplicate wfoids --select * --from WFOMatchFileInputData s --where -- isnull(wfoid,'')<>'' -- and GID='54438369-0bac-48e0-b229-49e65953f753' --and isduplicate=1 --order by ScientificName--1633 miscellinuous check ------5a. ----select distinct s1.RECID, ----isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, ----s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors ----,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn ----,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone ----from WFOMatchFileInputData s1 ----inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname ----where isnull(s1.wfoid,'')<>'' ----and isnull(s2.wfoid,'')<>'' ----and s1.isduplicate=1 ----and s2.isDuplicate=1 ----and s1.scientificnameauthorship=s2.scientificnameauthorship ----and s1.family=s2.family ----and s1.taxonomicStatus=s2.taxonomicStatus ----and s1.RECID<>s2.RECID ----order by s1.ScientificName--766 --5b --select distinct s1.RECID, --isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, --s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors --,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn --,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and isnull(s2.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship<>s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus=s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--105 --5c --select distinct s1.RECID, --isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, --s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors --,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn --,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship<>s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus<>s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--148 --5d --select --isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, --s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors --,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn --,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship=s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus<>s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--822 --select distinct s1.RECID, isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, --s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors --,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn --,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone from WFOMatchFileInputData s1 --where RECID not in --( --select distinct s1.RECID --isnull(WFOID,'') WFOID,RECID,scientificName,scientificNameAuthorship,family,taxonomicStatus, --comments_scientificName_doNotProcess comments_scientificName,Comments_Authors_doNotProcess Comments_Authors --,Comments_family_doNotProcess Comments_family,Comments_taxonomicStatus_doNotProcess Comments_taxonomicStatus,Comments_namePublishedIn_doNotProcess Comments_namePublishedIn --,doNotProcess_1_Backbone doNotProcess_Backbone,doNotProcess_1_reason_Backbone doNotProcess_reason_Backbone,comments_1_Backbone comments_Backbone --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship=s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus=s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--766 --union --5b --select --isnull(WFOID,'') WFOID,RECID,scientificName,scientificNameAuthorship,family,taxonomicStatus, --comments_scientificName_doNotProcess comments_scientificName,Comments_Authors_doNotProcess Comments_Authors --,Comments_family_doNotProcess Comments_family,Comments_taxonomicStatus_doNotProcess Comments_taxonomicStatus,Comments_namePublishedIn_doNotProcess Comments_namePublishedIn --,doNotProcess_1_Backbone doNotProcess_Backbone,doNotProcess_1_reason_Backbone doNotProcess_reason_Backbone,comments_1_Backbone comments_Backbone --distinct s1.RECID --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship<>s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus=s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--105 --union --5c --select --isnull(WFOID,'') WFOID,RECID,scientificName,scientificNameAuthorship,family,taxonomicStatus, --comments_scientificName_doNotProcess comments_scientificName,Comments_Authors_doNotProcess Comments_Authors --,Comments_family_doNotProcess Comments_family,Comments_taxonomicStatus_doNotProcess Comments_taxonomicStatus,Comments_namePublishedIn_doNotProcess Comments_namePublishedIn --,doNotProcess_1_Backbone doNotProcess_Backbone,doNotProcess_1_reason_Backbone doNotProcess_reason_Backbone,comments_1_Backbone comments_Backbone --distinct s1.RECID --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship<>s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus<>s2.taxonomicStatus --and s1.RECID<>s2.RECID --order by s1.ScientificName--148 --) --and isDuplicate=1 --662 -----------------%%%%%%%%%%%%%%%%%%%%%%% --select --distinct s1.RECID, --isnull(s1.WFOID,'') WFOID,s1.RECID,s1.scientificName,s1.scientificNameAuthorship,s1.family,s1.taxonomicStatus, --s1.comments_scientificName comments_scientificName,s1.Comments_Authors Comments_Authors --,s1.Comments_family Comments_family,s1.Comments_taxonomicStatus Comments_taxonomicStatus,s1.Comments_namePublishedIn Comments_namePublishedIn --,s1.doNotProcess_Backbone doNotProcess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone -- from WFOMatchFileInputData s1 where RECID not in ( --select distinct s1.RECID--rocess_Backbone,s1.doNotProcess_reason_Backbone doNotProcess_reason_Backbone,s1.comments_Backbone comments_Backbone --from WFOMatchFileInputData s1 --inner join WFOMatchFileInputData s2 on s1.scientificname=s2.scientificname --where isnull(s1.wfoid,'')<>'' --and isnull(s2.wfoid,'')<>'' --and s1.isduplicate=1 --and s2.isDuplicate=1 --and s1.scientificnameauthorship=s2.scientificnameauthorship --and s1.family=s2.family --and s1.taxonomicStatus=s2.taxonomicStatus --and s1.RECID<>s2.RECID ----order by s1.ScientificName--867 --) --and isDuplicate=1 -------------------------- select * from WFOMatchFileInputData where doNotProcess_Backbone is null select count(*) from WFOMatchFileInputData where doNotProcess_Backbone=0 and doNotProcess_1_Backbone=0 and isnull(WFOID,'')<>''--12879 select count(*) from WFOMatchFileInputData where doNotProcess_Backbone=0 and doNotProcess_1_Backbone=1 and isnull(WFOID,'')<>''--61 select count(*) from WFOMatchFileInputData where isnull(WFOID,'') =''--1474 --checking duplicate wfoids ;with dups as( select count(*) c,wfoid from WFOMatchFileInputData where wfoid<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' group by WFOID having count(*)>1 ) select * from WFOMatchFileInputData f inner join dups d on d.WFOID=f.WFOID where f.wfoid<>'' and GID='54438369-0bac-48e0-b229-49e65953f753' order by f.scientificname--1633 SET NOCOUNT OFF; END --************************************************************* /* /*1. Matched*/ select WFOID, s.SPNUMBER,s.TAXSTAT,s.FAMILY,isnull(s.GENUS,'') GENUS,isnull(s.SP1,'') SP1,isnull(s.AUTHOR1,'') AUTHOR1,isnull(s.RANK1,'') RANK1,isnull(s.SP2,'') SP2,isnull(s.AUTHOR1,'') AUTHOR2, isnull(s.RANK2,'') RANK2,isnull(s.SP3,'') SP3,isnull(s.AUTHOR3,'') AUTHOR3,s.fullScientificName SCIENTIFICNAME,Comments_ScientificName,Comments_Authors,Comments_family,Comments_taxonomicStatus,Comments from SANBI_Taxon_Staging s where isnull(wfoid,'')<>'' and Comments not like '%'+'}, Not Match:'+'%' order by ScientificName--15822 ------------------------------%%%%%%%%%%%%%%%%%%%%%%%% /*2. Only diff family first, followed by only diff tax. Status second and followed by family and tax. Status third*/ --family and tax. Status Not match but assigned wfoid select WFOID, s.SPNUMBER,s.TAXSTAT,s.FAMILY,isnull(s.GENUS,'') GENUS,isnull(s.SP1,'') SP1,isnull(s.AUTHOR1,'') AUTHOR1,isnull(s.RANK1,'') RANK1,isnull(s.SP2,'') SP2,isnull(s.AUTHOR1,'') AUTHOR2, isnull(s.RANK2,'') RANK2,isnull(s.SP3,'') SP3,isnull(s.AUTHOR3,'') AUTHOR3,s.fullScientificName SCIENTIFICNAME,Comments_ScientificName,Comments_Authors,Comments_family,Comments_taxonomicStatus,Comments from SANBI_Taxon_Staging s where isnull(wfoid,'')<>'' and Comments like '%'+' Not Match:'+'%' order by Comments_ScientificName--5423 2 --and (Comments like '%'+'}, Not Match: Family {'+'%' --or Comments like '%'+', Not Match: TaxonomicStatus {'+'%')--7900 -------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /*3. Only diff authors first followed by Authors and Family different second followed by authors and tax. Status third followed by Authors, Family and tax status different fourth */ select WFOID, s.SPNUMBER,s.TAXSTAT,s.FAMILY,isnull(s.GENUS,'') GENUS,isnull(s.SP1,'') SP1,isnull(s.AUTHOR1,'') AUTHOR1,isnull(s.RANK1,'') RANK1,isnull(s.SP2,'') SP2,isnull(s.AUTHOR1,'') AUTHOR2, isnull(s.RANK2,'') RANK2,isnull(s.SP3,'') SP3,isnull(s.AUTHOR3,'') AUTHOR3,s.fullScientificName SCIENTIFICNAME,Comments_ScientificName,Comments_Authors,Comments_family,Comments_taxonomicStatus,Comments from SANBI_Taxon_Staging s where isnull(wfoid,'')='' and Comments<>'Match not found' and Comments like '%'+', Not Match: Authors {'+'%' and Comments not like 'May Match: ScientificName {'+'%' order by ScientificName--949 ---------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --4. Miscellaneous select WFOID, s.SPNUMBER,s.TAXSTAT,s.FAMILY,isnull(s.GENUS,'') GENUS,isnull(s.SP1,'') SP1,isnull(s.AUTHOR1,'') AUTHOR1,isnull(s.RANK1,'') RANK1,isnull(s.SP2,'') SP2,isnull(s.AUTHOR1,'') AUTHOR2, isnull(s.RANK2,'') RANK2,isnull(s.SP3,'') SP3,isnull(s.AUTHOR3,'') AUTHOR3,s.fullScientificName SCIENTIFICNAME,Comments_ScientificName,Comments_Authors,Comments_family,Comments_taxonomicStatus,Comments from SANBI_Taxon_Staging s where SPNUMBER not in ( select s.SPNUMBER from SANBI_Taxon_Staging s where isnull(wfoid,'')='' and Comments<>'Match not found' and Comments like '%'+', Not Match: Authors {'+'%' and Comments not like 'May Match: ScientificName {'+'%' ) and isnull(wfoid,'')='' and Comments<>'Match not found' order by ScientificName--553 miscellinuous ---------------%%%%%%%%%%%%%%%%%%%%%%% /*5. Match not found */ select WFOID, s.SPNUMBER,s.TAXSTAT,s.FAMILY,isnull(s.GENUS,'') GENUS,isnull(s.SP1,'') SP1,isnull(s.AUTHOR1,'') AUTHOR1,isnull(s.RANK1,'') RANK1,isnull(s.SP2,'') SP2,isnull(s.AUTHOR1,'') AUTHOR2, isnull(s.RANK2,'') RANK2,isnull(s.SP3,'') SP3,isnull(s.AUTHOR3,'') AUTHOR3,s.fullScientificName SCIENTIFICNAME,Comments_ScientificName,Comments_Authors,Comments_family,Comments_taxonomicStatus,Comments from SANBI_Taxon_Staging s where isnull(wfoid,'')='' and Comments='Match not found' order by ScientificName--2348 */ --******************************************************************************** /* /*1. Matched*/ select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')<>''--937 and gid = @GID order by ScientificName ------------------------------%%%%%%%%%%%%%%%%%%%%%%%% /*2. Only diff authors first followed by Authors and Family different second followed by authors and tax. Status third followed by Authors, Family and tax status different fourth */ --Only diff authors first select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Authors {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' union -- Authors and Family different second followed by select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found'--30 and gid = @GID and Comments like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' union --authors and tax. Status third select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' --and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' union --Authors, Family and tax status different fourth select WFOID,InputColumns,Comments from WFOMatchFileInputData where Comments like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and gid = @GID and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' --------------------%%%%%%%%%%%%%%%%%%%%% /*3. Only diff family first, followed by only diff tax. Status second and followed by family and tax. Status third*/ -- Only diff family first select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Family {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Family {%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%'--86 and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' --order by ScientificName--2 union --only diff tax. Status select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: TaxonomicStatus {%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: TaxonomicStatus {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%'--86 --order by ScientificName--2 union --family and tax. Status select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Family {%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' ---------%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% select WFOID,InputColumns,Comments from WFOMatchFileInputData where ScientificName not in ( /*2. Only diff authors first followed by Authors and Family different second followed by authors and tax. Status third followed by Authors, Family and tax status different fourth */ --Only diff authors first select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Authors {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' union -- Authors and Family different second followed by select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found'--30 and gid = @GID and Comments like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' union --authors and tax. Status third select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' --and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Authors {%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' union --Authors, Family and tax status different fourth select ScientificName from WFOMatchFileInputData where Comments like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and gid = @GID and Comments not like '%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like '%'+', Not Match: Authors {'+'%'+', Not Match: TaxonomicStatus {'+'%' union --------------------%%%%%%%%%%%%%%%%%%%%% /*3. Only diff family first, followed by only diff tax. Status second and followed by family and tax. Status third*/ -- Only diff family first select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: Family {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Family {%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%'--86 --order by ScientificName--2 union --only diff tax. Status select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: TaxonomicStatus {%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: TaxonomicStatus {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like '%'+', Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%'--86 --order by ScientificName--2 union --family and tax. Status select ScientificName from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID and Comments like '%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like 'May Match: ScientificName {'+'%'+'Not Match: Family {%'+', Not Match: TaxonomicStatus {'+'%' and Comments not like'%'+', Not Match: Authors {'+'%'+'}, Not Match: Family {'+'%'+', Not Match: TaxonomicStatus {'+'%' ) and isnull(wfoid,'')='' and Comments<>'Match not found' and gid = @GID order by ScientificName ---------------%%%%%%%%%%%%%%%%%%%%%%% /*5. Match not found */ select WFOID,InputColumns,Comments from WFOMatchFileInputData where isnull(wfoid,'')='' and Comments='Match not found'--31 and gid = @GID order by ScientificName */ --*************************************************************** /* Gumillea auriculata Picramnia antidesma subsp. carpinterae Picramnia glazioviana var. glazioviana Picramnia sellowii var. spruceana */