1 Reply by DGERC

  • DGERC
  • DGERC
  • Veteran
  • Offline
  • From: SWSEIC
  • Registered: 31-05-2006
  • Posts: 232

Re: Extracting records of species with BoCC3 status using report wizard

I posted this some time ago as part of another thread on designation reporting issues.  The same problem seems to still exist when extracting information using the report wizard.

I've also noticed another anomaly with the BoCC3 listings.  If I request just amber listed species the extract picks up Lapwing and Scaup both of which were formerly Amber and are now Red following the current 2009 review.  I would expect it would pick up others that have changed in the last review too.  It appears that both the old 2002 and 2009 designation statuses on the JNCC list are being picked up.

Those species which have changed statuses between 2002 and 2009 (i.e. Red -> Amber or vice versa) appear to have both statuses 'current' and are picked under both categories.

More importantly, is there a way of correcting this or working round it?

MARK

Mark Pollitt
SWSEIC (formerly DGERC)

DGERC's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Extracting records of species with BoCC3 status using report wizard

Mark

I have done some investigations on this and something is certainly wrong with the data in the tables and it needs looking at by JNCC. However,  I will investigate further to see if  there is a way of working round this while it is fixed.

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Extracting records of species with BoCC3 status using report wizard

Mark

This seems to be caused by the fact the the 2002 designations have not been given an end date in the Taxon_designation_table. The effect of this is to include both the 2002 ans 2009 designation in Index_taxon_Designation. The following query run in  SQL Mangement Studio, followed by a rebuild of Index_taxon_designation would be a broad brush fix for the issue. My concern is that if this is run there are 22 taxa on the previous amber list whcih no longer appear on this or the red list. This may be correct, but it needs checking in detail.

update taxon_designation set date_to = '01/05/2009' from taxon_designation td where
datediff(d,TD.date_from, '08/01/2002') = 0 and taxon_designation_type_key in ('NHMSYS0020443051', 'NHMSYS00203052')

JNCC are aware of the problem and it should be fixed in a dictionary uograde in the not too distance future. For this reason I suggest that the above change is only made if there is an urgent need for a temporary fix.


Mike

Mike Weideli

MikeWeideli's Website

4 Reply by DGERC

  • DGERC
  • DGERC
  • Veteran
  • Offline
  • From: SWSEIC
  • Registered: 31-05-2006
  • Posts: 232

Re: Extracting records of species with BoCC3 status using report wizard

Thanks for following this up Mike and your helpful option of a temporary fix

MARK

Mark Pollitt
SWSEIC (formerly DGERC)

DGERC's Website