Project

General

Profile

« Previous | Next » 

Revision 1300

Added by Aaron Marcuse-Kubitza almost 13 years ago

specimens verification: Added duplicate catalog numbers test

View differences:

inputs/NYBG/verify/specimens.ref.sql
19 19 
SELECT '# catalog numbers' AS ___;
20 20 
SELECT count(DISTINCT CatalogNumber) AS count FROM nybg_raw;
21 21 

  		




  
  22
  
    
SELECT 'duplicate catalog numbers' AS ___;


  		




  
  23
  
    
SELECT CatalogNumber AS catalognumber_dwc, count(*) AS count


  		




  
  24
  
    
FROM nybg_raw


  		




  
  25
  
    
GROUP BY CatalogNumber


  		




  
  26
  
    
HAVING count > 1


  		




  
  27
  
    
;


  		




  
  28
  
    

  		




  22
  29
  
    
SELECT '# counties' AS ___;


  		




  23
  30
  
    
SELECT count(DISTINCT Country, StateProvince, County) AS count FROM nybg_raw;


  		




  24
  31
  
    

  		












  

    
      inputs/NYBG/verify/specimens.ref
    
  





  27
  27
  
    
count


  		




  28
  28
  
    
327637


  		




  29
  29
  
    
___


  		




  
  30
  
    
duplicate catalog numbers


  		




  
  31
  
    
catalognumber_dwc	count


  		




  
  32
  
    
NULL	401


  		




  
  33
  
    
788791	2


  		




  
  34
  
    
799350	2


  		




  
  35
  
    
___


  		




  30
  36
  
    
# counties


  		




  31
  37
  
    
count


  		




  32
  38
  
    
4655


  		












  

    
      mappings/verify.specimens.sql
    
  





  84
  84
  
    
    (SELECT party_id FROM party WHERE organizationname = :datasource)


  		




  85
  85
  
    
;


  		




  86
  86
  
    

  		




  
  87
  
    
SELECT 'duplicate catalog numbers' AS ___;


  		




  
  88
  
    
SELECT catalognumber_dwc, count(*) AS count


  		




  
  89
  
    
FROM specimenreplicate


  		




  
  90
  
    
WHERE datasource_id =


  		




  
  91
  
    
    (SELECT party_id FROM party WHERE organizationname = :datasource)


  		




  
  92
  
    
GROUP BY catalognumber_dwc


  		




  
  93
  
    
HAVING count(*) > 1


  		




  
  94
  
    
;


  		




  
  95
  
    

  		




  87
  96
  
    
SELECT '# counties' AS ___;


  		




  88
  97
  
    
SELECT count(DISTINCT


  		




  89
  98
  
    
    COALESCE(country_namedplace.placename, '')


  		












Also available in:  Unified diff