-- ----------------------------

-- procedure structure for getaggregatechronbydatasetid

-- ----------------------------

create procedure [getaggregatechronbydatasetid](@aggregatedatasetid int)

as

select aggregatechronid, aggregatedatasetid, agetypeid, isdefault, chronologyname, ageboundyounger, ageboundolder, notes

from ndb.aggregatechronologies

where (aggregatedatasetid = @aggregatedatasetid)

go

-- ----------------------------

-- procedure structure for getaggregatedatasetbyname

-- ----------------------------

create procedure [getaggregatedatasetbyname](@name nvarchar(255))

as

select aggregatedatasetid, aggregatedatasetname, aggregateordertypeid, notes

from ndb.aggregatedatasets

where (aggregatedatasetname = @name)

go

-- ----------------------------

-- procedure structure for getanalysisunit

-- ----------------------------

create procedure [getanalysisunit](@collectionuniid int, @analunitname nvarchar(80) = null, @depth float = null, @thickness float = null)

as

if @analunitname is null and @depth is not null and @thickness is not null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname is null) and (depth = @depth) and (thickness = @thickness)

end

else if @analunitname is null and @depth is not null and @thickness is null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname is null) and (depth = @depth) and (thickness is null)

end

else if @analunitname is not null and @depth is not null and @thickness is null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname = @analunitname) and (depth = @depth) and (thickness is null)

end

else if @analunitname is not null and @depth is null and @thickness is null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname = @analunitname) and (depth is null) and (thickness is null)

end

else if @analunitname is not null and @depth is null and @thickness is not null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname = @analunitname) and (depth is null) and (thickness = @thickness)

end

else if @analunitname is not null and @depth is not null and @thickness is not null

begin

select analysisunitid

from ndb.analysisunits

where (collectionunitid = @collectionuniid) and (analysisunitname = @analunitname) and (depth = @depth) and (thickness = @thickness)

end

go

-- ----------------------------

-- procedure structure for getanalysisunitbyid

-- ----------------------------

create procedure [getanalysisunitbyid](@analyunitid int)

as

select collectionunitid, analysisunitname, depth, thickness

from ndb.analysisunits

where (analysisunitid = @analyunitid)

go

-- ----------------------------

-- procedure structure for getanalysisunitsamplecount

-- ----------------------------

-- gets number of samples assigned to analysis unit

create procedure [getanalysisunitsamplecount](@analunitid int)

as

select count(analysisunitid) as count

from ndb.samples

where (analysisunitid = @analunitid)

go

-- ----------------------------

-- procedure structure for getanalysisunitsbycollunitid

-- ----------------------------

create procedure [getanalysisunitsbycollunitid](@collunitid int)

as

select analysisunitid, analysisunitname, depth, thickness

from ndb.analysisunits

where (collectionunitid = @collunitid)

go

-- ----------------------------

-- procedure structure for getbiochemdatasetbyid

-- ----------------------------

create procedure [getbiochemdatasetbyid](@datasetid int)

as

declare @datasettypeid int = (select datasettypeid from ndb.datasets where (datasetid = @datasetid))

if @datasettypeid = 27

begin

select top (100) percent ndb.data.sampleid, ndb.analysisunits.analysisunitname, ndb.samples.samplename, convert(nvarchar(10),ndb.samples.sampledate,120) as sampledate,

ndb.taxa.taxonname, taxa_1.taxonname as variable, ndb.variableelements.variableelement, ndb.variableunits.variableunits, ndb.data.value

from ndb.samples inner join

ndb.taxa on ndb.samples.taxonid = ndb.taxa.taxonid inner join

ndb.analysisunits on ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.variables on ndb.data.variableid = ndb.variables.variableid inner join

ndb.taxa as taxa_1 on ndb.variables.taxonid = taxa_1.taxonid left outer join

ndb.variableunits on ndb.variables.variableunitsid = ndb.variableunits.variableunitsid left outer join

ndb.variableelements on ndb.variables.variableelementid = ndb.variableelements.variableelementid

where (ndb.samples.datasetid = @datasetid)

order by ndb.data.sampleid

end

else

select n'dataset is not biochemistry'

go

-- ----------------------------

-- procedure structure for getchildtaxaforallsites

-- ----------------------------

create procedure [getchildtaxaforallsites](@taxonname nvarchar(80))

as

with taxacte

as

(

select taxonid as basetaxonid, taxonid, taxonname, author, highertaxonid, 0 as level

from ndb.taxa

where taxonname = @taxonname

union all

select parent.basetaxonid, child.taxonid, child.taxonname, child.author, child.highertaxonid, parent.level +1 as level

from taxacte as parent

inner join ndb.taxa as child

on parent.taxonid = child.highertaxonid

)

select taxacte.taxonid, taxacte.taxonname, ndb.sites.sitename

from taxacte inner join

ndb.variables on taxacte.taxonid = ndb.variables.taxonid inner join

ndb.data on ndb.variables.variableid = ndb.data.variableid inner join

ndb.samples on ndb.data.sampleid = ndb.samples.sampleid inner join

ndb.datasets on ndb.samples.datasetid = ndb.datasets.datasetid inner join

ndb.collectionunits on ndb.datasets.collectionunitid = ndb.collectionunits.collectionunitid inner join

ndb.sites on ndb.collectionunits.siteid = ndb.sites.siteid

group by taxacte.taxonid, taxacte.taxonname, ndb.sites.sitename

go

-- ----------------------------

-- procedure structure for getchroncontroltypehighestid

-- ----------------------------

create procedure [getchroncontroltypehighestid](@chroncontroltypeid int)

as

declare @id int

declare @higherid int

set @id = @chroncontroltypeid

set @higherid = (select higherchroncontroltypeid from ndb.chroncontroltypes where (chroncontroltypeid = @id))

while @id <> @higherid

begin

set @id = @higherid

set @higherid = (select higherchroncontroltypeid from ndb.chroncontroltypes where (chroncontroltypeid = @id))

end

select chroncontroltypeid, chroncontroltype, higherchroncontroltypeid

from ndb.chroncontroltypes

where (chroncontroltypeid = @id)

go

-- ----------------------------

-- procedure structure for getchronocontrolsbyanalysisunitid

-- ----------------------------

create procedure [getchronocontrolsbyanalysisunitid](@analunitid int)

as

select chroncontrolid

from ndb.chroncontrols

where (analysisunitid = @analunitid)

go

-- ----------------------------

-- procedure structure for getcontactlinks

-- ----------------------------

create procedure [getcontactlinks](@contactid int)

as

declare @linkedtables table

(

linkid int not null identity(1,1) primary key,

tablename nvarchar(255),

number int

)

declare @count int

set @count = (select count(contactid) as count from ndb.chronologies where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'chronologies', @count)

end

set @count = (select count(contactid) as count from ndb.collectors where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'collectors', @count)

end

set @count = (select count(contactid) as count from ndb.constituentdatabases where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'constituentdatabases', @count)

end

set @count = (select count(contactid) as count from ndb.dataprocessors where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'dataprocessors', @count)

end

set @count = (select count(contactid) as count from ndb.datasetpis where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'datasetpis', @count)

end

set @count = (select count(contactid) as count from ndb.publicationauthors where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'publicationauthors', @count)

end

set @count = (select count(contactid) as count from ndb.sampleanalysts where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'sampleanalysts', @count)

end

set @count = (select count(contactid) as count from ndb.siteimages where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'siteimages', @count)

end

set @count = (select count(contactid) as count from ti.stewards where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'stewards', @count)

end

set @count = (select count(contactid) as count from ti.stewardupdates where (contactid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'stewardupdates', @count)

end

set @count = (select count(validatorid) as count from ndb.taxa where (validatorid = @contactid))

if (@count > 0)

begin

insert into @linkedtables(tablename, number) values(n'taxa', @count)

end

select tablename, number

from @linkedtables

go

-- ----------------------------

-- procedure structure for getdatasetauthorsdatasettypes

-- ----------------------------

create procedure [getdatasetauthorsdatasettypes](@datasettypeidlist nvarchar(max) = null)

as

if @datasettypeidlist is null

begin

select ndb.contacts.contactid, ndb.datasets.datasettypeid, ndb.datasets.datasetid, ndb.contacts.contactname

from ndb.datasets inner join

ndb.datasetpublications on ndb.datasets.datasetid = ndb.datasetpublications.datasetid inner join

ndb.publications on ndb.datasetpublications.publicationid = ndb.publications.publicationid inner join

ndb.publicationauthors on ndb.publications.publicationid = ndb.publicationauthors.publicationid inner join

ndb.contacts on ndb.publicationauthors.contactid = ndb.contacts.contactid

group by ndb.contacts.contactid, ndb.datasets.datasettypeid, ndb.datasets.datasetid, ndb.contacts.contactname

end

else

begin

select ndb.contacts.contactid, ndb.datasets.datasettypeid, ndb.datasets.datasetid, ndb.contacts.contactname

from ndb.datasets inner join

ndb.datasetpublications on ndb.datasets.datasetid = ndb.datasetpublications.datasetid inner join

ndb.publications on ndb.datasetpublications.publicationid = ndb.publications.publicationid inner join

ndb.publicationauthors on ndb.publications.publicationid = ndb.publicationauthors.publicationid inner join

ndb.contacts on ndb.publicationauthors.contactid = ndb.contacts.contactid

group by ndb.contacts.contactid, ndb.datasets.datasettypeid, ndb.datasets.datasetid, ndb.contacts.contactname

having (ndb.datasets.datasettypeid in (select value from ti.func_nvarcharlisttoin(@datasettypeidlist,',')))

end

go

-- ----------------------------

-- procedure structure for getdatasetcountbycollunittype

-- ----------------------------

create procedure [getdatasetcountbycollunittype](@collunittypeid int)

as

select top (100) percent ndb.datasettypes.datasettype, count(ndb.datasets.datasetid) as count

from ndb.collectionunits inner join

ndb.datasets on ndb.collectionunits.collectionunitid = ndb.datasets.collectionunitid inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid

group by ndb.collectionunits.colltypeid, ndb.datasettypes.datasettype

having (ndb.collectionunits.colltypeid = @collunittypeid)

order by count desc

go

-- ----------------------------

-- procedure structure for getdatasetdata

-- ----------------------------

create procedure [getdatasetdata](@datasetid int)

as

select ndb.data.dataid, ndb.data.sampleid, ndb.data.variableid, ndb.data.value, ndb.summarydatataphonomy.taphonomictypeid

from ndb.samples inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid left outer join

ndb.summarydatataphonomy on ndb.data.dataid = ndb.summarydatataphonomy.dataid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetdatabase

-- ----------------------------

create procedure [getdatasetdatabase](@datasetid int)

as

select ndb.constituentdatabases.databaseid, ndb.constituentdatabases.databasename

from ndb.datasets inner join

ndb.datasetsubmissions on ndb.datasets.datasetid = ndb.datasetsubmissions.datasetid inner join

ndb.constituentdatabases on ndb.datasetsubmissions.databaseid = ndb.constituentdatabases.databaseid

where (ndb.datasets.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetidbycollunitandtype

-- ----------------------------

create procedure [getdatasetidbycollunitandtype](@collunitid int, @datasettypeid int)

as

select datasetid

from ndb.datasets

where (datasettypeid = @datasettypeid) and (collectionunitid = @collunitid)

go

-- ----------------------------

-- procedure structure for getdatasetidsbytaxonid

-- ----------------------------

create procedure [getdatasetidsbytaxonid](@taxonid int)

as

select ndb.datasets.datasetid

from ndb.datasets inner join

ndb.samples on ndb.datasets.datasetid = ndb.samples.datasetid inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.variables on ndb.data.variableid = ndb.variables.variableid

group by ndb.datasets.datasetid, ndb.variables.taxonid

having (ndb.variables.taxonid = @taxonid)

go

-- ----------------------------

-- procedure structure for getdatasetpis

-- ----------------------------

create procedure [getdatasetpis](@datasetid int)

as select ndb.datasetpis.piorder, ndb.contacts.familyname, ndb.contacts.leadinginitials

from ndb.datasetpis inner join

ndb.contacts on ndb.datasetpis.contactid = ndb.contacts.contactid

where (ndb.datasetpis.datasetid = @datasetid)

order by ndb.datasetpis.piorder

go

-- ----------------------------

-- procedure structure for getdatasetpisdatasettypes

-- ----------------------------

create procedure [getdatasetpisdatasettypes](@datasettypeidlist nvarchar(max) = null)

as

if @datasettypeidlist is null

begin

select ndb.datasetpis.contactid, ndb.datasets.datasettypeid, ndb.datasetpis.datasetid, ndb.contacts.contactname

from ndb.datasetpis inner join

ndb.datasets on ndb.datasetpis.datasetid = ndb.datasets.datasetid inner join

ndb.contacts on ndb.datasetpis.contactid = ndb.contacts.contactid

end

else

begin

select ndb.datasetpis.contactid, ndb.datasets.datasettypeid, ndb.datasetpis.datasetid, ndb.contacts.contactname

from ndb.datasetpis inner join

ndb.datasets on ndb.datasetpis.datasetid = ndb.datasets.datasetid inner join

ndb.contacts on ndb.datasetpis.contactid = ndb.contacts.contactid

where (ndb.datasets.datasettypeid in (select value from ti.func_nvarcharlisttoin(@datasettypeidlist,',')))

end

go

-- ----------------------------

-- procedure structure for getdatasetpublicationids

-- ----------------------------

create procedure [getdatasetpublicationids](@datasetid int)

as

select publicationid

from ndb.datasetpublications

where (datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetpublications

-- ----------------------------

create procedure [getdatasetpublications](@datasetid int)

as

select ndb.publications.publicationid, ndb.publications.pubtypeid, ndb.publications.year, ndb.publications.citation, ndb.publications.articletitle,

ndb.publications.journal, ndb.publications.volume, ndb.publications.issue, ndb.publications.pages, ndb.publications.citationnumber, ndb.publications.doi,

ndb.publications.booktitle, ndb.publications.numvolumes, ndb.publications.edition, ndb.publications.volumetitle, ndb.publications.seriestitle,

ndb.publications.seriesvolume, ndb.publications.publisher, ndb.publications.url, ndb.publications.city, ndb.publications.state, ndb.publications.country,

ndb.publications.originallanguage, ndb.publications.notes

from ndb.datasetpublications inner join

ndb.publications on ndb.datasetpublications.publicationid = ndb.publications.publicationid

where (ndb.datasetpublications.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetpublicationstatus

-- ----------------------------

create procedure [getdatasetpublicationstatus](@datasetid int, @publicationid int)

as

select primarypub

from ndb.datasetpublications

where (datasetid = @datasetid) and (publicationid = @publicationid)

go

-- ----------------------------

-- procedure structure for getdatasetrepository

-- ----------------------------

create procedure [getdatasetrepository](@datasetid int)

as

select ndb.repositoryinstitutions.repositoryid, ndb.repositoryinstitutions.acronym, ndb.repositoryinstitutions.repository, ndb.repositoryinstitutions.notes

from ndb.repositoryspecimens inner join

ndb.repositoryinstitutions on ndb.repositoryspecimens.repositoryid = ndb.repositoryinstitutions.repositoryid

where (ndb.repositoryspecimens.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetrepositoryspecimennotes

-- ----------------------------

create procedure [getdatasetrepositoryspecimennotes](@datasetid int)

as

select repositoryid, notes

from ndb.repositoryspecimens

where (datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsampleanalysts

-- ----------------------------

create procedure [getdatasetsampleanalysts](@datasetid int)

as

select ndb.sampleanalysts.sampleid, ndb.sampleanalysts.contactid

from ndb.samples inner join

ndb.sampleanalysts on ndb.samples.sampleid = ndb.sampleanalysts.sampleid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsampledepagents

-- ----------------------------

create procedure [getdatasetsampledepagents](@datasetid int)

as

select ndb.samples.sampleid, ndb.depagenttypes.depagent

from ndb.depagents inner join

ndb.samples on ndb.depagents.analysisunitid = ndb.samples.analysisunitid inner join

ndb.depagenttypes on ndb.depagents.depagentid = ndb.depagenttypes.depagentid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsampleids

-- ----------------------------

create procedure [getdatasetsampleids](@datasetid int)

as

select top (100) percent sampleid

from ndb.samples

where (datasetid = @datasetid)

order by sampleid

go

-- ----------------------------

-- procedure structure for getdatasetsamplekeywords

-- ----------------------------

create procedure [getdatasetsamplekeywords](@datasetid int)

as

select ndb.samplekeywords.sampleid, ndb.keywords.keyword

from ndb.samples inner join

ndb.samplekeywords on ndb.samples.sampleid = ndb.samplekeywords.sampleid inner join

ndb.keywords on ndb.samplekeywords.keywordid = ndb.keywords.keywordid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsamples

-- ----------------------------

create procedure [getdatasetsamples](@datasetid int)

as

select ndb.samples.sampleid, ndb.samples.samplename, convert(nvarchar(10),ndb.samples.sampledate,120) as sampledate,

convert(nvarchar(10),ndb.samples.analysisdate,120) as analysisdate, ndb.samples.labnumber,

ndb.samples.preparationmethod, ndb.samples.notes as samplenotes, ndb.analysisunits.analysisunitid, ndb.analysisunits.analysisunitname,

ndb.analysisunits.depth, ndb.analysisunits.thickness, ndb.analysisunits.faciesid, ndb.faciestypes.facies, ndb.analysisunits.mixed,

ndb.analysisunits.igsn, ndb.analysisunits.notes as analunitnotes

from ndb.samples inner join

ndb.analysisunits on ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid left outer join

ndb.faciestypes on ndb.analysisunits.faciesid = ndb.faciestypes.faciesid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsbycontactid

-- ----------------------------

create procedure [getdatasetsbycontactid](@contactid int)

as select top (100) percent ndb.datasetpis.datasetid, ndb.datasettypes.datasettype, ndb.sites.siteid, ndb.sites.sitename, ti.geopol1.geopolname1,

ti.geopol2.geopolname2, ti.geopol3.geopolname3

from ndb.datasetpis inner join

ndb.datasets on ndb.datasetpis.datasetid = ndb.datasets.datasetid inner join

ndb.collectionunits on ndb.datasets.collectionunitid = ndb.collectionunits.collectionunitid inner join

ndb.sites on ndb.collectionunits.siteid = ndb.sites.siteid inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid left outer join

ti.geopol3 on ndb.sites.siteid = ti.geopol3.siteid left outer join

ti.geopol2 on ndb.sites.siteid = ti.geopol2.siteid left outer join

ti.geopol1 on ndb.sites.siteid = ti.geopol1.siteid

where (ndb.datasetpis.contactid = @contactid)

order by ndb.sites.sitename

go

-- ----------------------------

-- procedure structure for getdatasetsbyid

-- ----------------------------

create procedure [getdatasetsbyid](@datasets nvarchar(max))

as

select ndb.datasets.datasetid, ndb.datasets.collectionunitid, ndb.datasets.datasettypeid, ndb.datasettypes.datasettype, ndb.datasets.datasetname,

ndb.datasets.notes

from ndb.datasets inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid

where (datasetid in (

select value

from ti.func_nvarcharlisttoin(@datasets,'$')

))

go

-- ----------------------------

-- procedure structure for getdatasetsbysiteid

-- ----------------------------

create procedure [getdatasetsbysiteid](@siteid int)

as select ndb.collectionunits.collectionunitid, ndb.collectionunits.collunitname, ndb.datasets.datasetid, ndb.datasettypes.datasettype

from ndb.sites inner join

ndb.collectionunits on ndb.sites.siteid = ndb.collectionunits.siteid inner join

ndb.datasets on ndb.collectionunits.collectionunitid = ndb.datasets.collectionunitid inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid

where (ndb.sites.siteid = @siteid)

go

-- ----------------------------

-- procedure structure for getdatasetsbysitename

-- ----------------------------

create procedure [getdatasetsbysitename](@sitename nvarchar(128))

as

select ndb.sites.siteid, ndb.sites.sitename, ndb.datasets.datasetid, ndb.datasettypes.datasettype

from ndb.sites inner join

ndb.collectionunits on ndb.sites.siteid = ndb.collectionunits.siteid inner join

ndb.datasets on ndb.collectionunits.collectionunitid = ndb.datasets.collectionunitid inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid

where (ndb.sites.sitename like @sitename)

go

-- ----------------------------

-- procedure structure for getdatasetsbytaxon

-- ----------------------------

create procedure [getdatasetsbytaxon](@taxon nvarchar(80))

as

select ndb.taxa.taxonname, ndb.variableelements.variableelement, ndb.datasets.datasetid, ndb.datasettypes.datasettype, ndb.collectionunits.collectionunitid,

ndb.sites.siteid, ndb.sites.sitename

from ndb.taxa inner join

ndb.variables on ndb.taxa.taxonid = ndb.variables.taxonid inner join

ndb.data on ndb.variables.variableid = ndb.data.variableid inner join

ndb.samples on ndb.data.sampleid = ndb.samples.sampleid inner join

ndb.datasets on ndb.samples.datasetid = ndb.datasets.datasetid inner join

ndb.datasettypes on ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid inner join

ndb.collectionunits on ndb.datasets.collectionunitid = ndb.collectionunits.collectionunitid inner join

ndb.sites on ndb.collectionunits.siteid = ndb.sites.siteid left outer join

ndb.variableelements on ndb.variables.variableelementid = ndb.variableelements.variableelementid

group by ndb.taxa.taxonname, ndb.variableelements.variableelement, ndb.datasets.datasetid, ndb.collectionunits.collectionunitid, ndb.sites.siteid,

ndb.sites.sitename, ndb.datasettypes.datasettype

having (ndb.taxa.taxonname = @taxon)

go

-- ----------------------------

-- procedure structure for getdatasetspecimendates

-- ----------------------------

create procedure [getdatasetspecimendates](@datasetid int)

as

select top (100) percent ndb.specimens.specimenid, ndb.analysisunits.analysisunitname, ndb.analysisunits.depth, ndb.analysisunits.thickness,

ndb.taxa.taxonname, ndb.variableunits.variableunits, ndb.variablecontexts.variablecontext, ndb.elementtypes.elementtype,

ndb.elementsymmetries.symmetry, ndb.elementportions.portion, ndb.elementmaturities.maturity, ndb.specimensextypes.sex,

ndb.specimendomesticstatustypes.domesticstatus, ndb.specimens.nisp, ndb.specimens.preservative, ndb.repositoryinstitutions.repository,

ndb.specimens.specimennr, ndb.specimens.fieldnr, ndb.specimens.arctosnr, ndb.specimengenbank.genbanknr, ndb.specimens.notes,

ndb.geochrontypes.geochrontype, ndb.geochronology.labnumber, ndb.fractiondated.fraction, ndb.geochronology.age, ndb.geochronology.errorolder,

ndb.geochronology.erroryounger, ndb.geochronology.infinite, ndb.specimendatescal.calageolder, ndb.specimendatescal.calageyounger,

ndb.calibrationcurves.calibrationcurve, ndb.calibrationprograms.calibrationprogram, ndb.calibrationprograms.version

from ndb.specimengenbank right outer join

ndb.datasets inner join

ndb.samples on ndb.datasets.datasetid = ndb.samples.datasetid inner join

ndb.analysisunits on ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.specimens on ndb.data.dataid = ndb.specimens.dataid inner join

ndb.variables on ndb.data.variableid = ndb.variables.variableid inner join

ndb.taxa on ndb.variables.taxonid = ndb.taxa.taxonid on ndb.specimengenbank.specimenid = ndb.specimens.specimenid left outer join

ndb.calibrationcurves inner join

ndb.specimendatescal on ndb.calibrationcurves.calibrationcurveid = ndb.specimendatescal.calibrationcurveid inner join

ndb.calibrationprograms on ndb.specimendatescal.calibrationprogramid = ndb.calibrationprograms.calibrationprogramid right outer join

ndb.geochrontypes inner join

ndb.geochronology on ndb.geochrontypes.geochrontypeid = ndb.geochronology.geochrontypeid inner join

ndb.fractiondated inner join

ndb.specimendates on ndb.fractiondated.fractionid = ndb.specimendates.fractionid on ndb.geochronology.geochronid = ndb.specimendates.geochronid on

ndb.specimendatescal.specimendateid = ndb.specimendates.specimendateid on

ndb.specimens.specimenid = ndb.specimendates.specimenid left outer join

ndb.repositoryinstitutions on ndb.specimens.repositoryid = ndb.repositoryinstitutions.repositoryid left outer join

ndb.specimendomesticstatustypes on ndb.specimens.domesticstatusid = ndb.specimendomesticstatustypes.domesticstatusid left outer join

ndb.specimensextypes on ndb.specimens.sexid = ndb.specimensextypes.sexid left outer join

ndb.elementsymmetries on ndb.specimens.symmetryid = ndb.elementsymmetries.symmetryid left outer join

ndb.elementmaturities on ndb.specimens.maturityid = ndb.elementmaturities.maturityid left outer join

ndb.elementportions on ndb.specimens.portionid = ndb.elementportions.portionid left outer join

ndb.elementtypes on ndb.specimens.elementtypeid = ndb.elementtypes.elementtypeid left outer join

ndb.variablecontexts on ndb.variables.variablecontextid = ndb.variablecontexts.variablecontextid left outer join

ndb.variableunits on ndb.variables.variableunitsid = ndb.variableunits.variableunitsid

where (ndb.datasets.datasetid = @datasetid)

order by ndb.specimens.specimenid

go

-- ----------------------------

-- procedure structure for getdatasetspecimengenbanknrs

-- ----------------------------

create procedure [getdatasetspecimengenbanknrs](@datasetid int)

as

select ndb.specimens.specimenid, ndb.specimengenbank.genbanknr

from ndb.samples inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.specimens on ndb.data.dataid = ndb.specimens.dataid inner join

ndb.specimengenbank on ndb.specimens.specimenid = ndb.specimengenbank.specimenid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetspecimens

-- ----------------------------

create procedure [getdatasetspecimens](@datasetid int)

as

select ndb.specimens.specimenid, ndb.specimens.dataid, ndb.elementtypes.elementtype, ndb.elementsymmetries.symmetry, ndb.elementportions.portion,

ndb.elementmaturities.maturity, ndb.specimensextypes.sex, ndb.specimendomesticstatustypes.domesticstatus, ndb.specimens.preservative,

ndb.specimens.nisp, ndb.repositoryinstitutions.repository, ndb.specimens.specimennr, ndb.specimens.fieldnr, ndb.specimens.arctosnr,

ndb.specimens.notes, ndb.taxa.taxonname, ndb.taxagrouptypes.taxagroup

from ndb.samples inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.specimens on ndb.data.dataid = ndb.specimens.dataid inner join

ndb.variables on ndb.data.variableid = ndb.variables.variableid inner join

ndb.taxa on ndb.variables.taxonid = ndb.taxa.taxonid inner join

ndb.taxagrouptypes on ndb.taxa.taxagroupid = ndb.taxagrouptypes.taxagroupid left outer join

ndb.repositoryinstitutions on ndb.specimens.repositoryid = ndb.repositoryinstitutions.repositoryid left outer join

ndb.specimendomesticstatustypes on ndb.specimens.domesticstatusid = ndb.specimendomesticstatustypes.domesticstatusid left outer join

ndb.specimensextypes on ndb.specimens.sexid = ndb.specimensextypes.sexid left outer join

ndb.elementmaturities on ndb.specimens.maturityid = ndb.elementmaturities.maturityid left outer join

ndb.elementsymmetries on ndb.specimens.symmetryid = ndb.elementsymmetries.symmetryid left outer join

ndb.elementtypes on ndb.specimens.elementtypeid = ndb.elementtypes.elementtypeid left outer join

ndb.elementportions on ndb.specimens.portionid = ndb.elementportions.portionid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetspecimentaphonomy

-- ----------------------------

create procedure [getdatasetspecimentaphonomy](@datasetid int)

as

select ndb.specimens.specimenid, ndb.taphonomicsystems.taphonomicsystem, ndb.taphonomictypes.taphonomictype

from ndb.samples inner join

ndb.data on ndb.samples.sampleid = ndb.data.sampleid inner join

ndb.specimens on ndb.data.dataid = ndb.specimens.dataid inner join

ndb.specimentaphonomy on ndb.specimens.specimenid = ndb.specimentaphonomy.specimenid inner join

ndb.taphonomictypes on ndb.specimentaphonomy.taphonomictypeid = ndb.taphonomictypes.taphonomictypeid inner join

ndb.taphonomicsystems on ndb.taphonomictypes.taphonomicsystemid = ndb.taphonomicsystems.taphonomicsystemid

where (ndb.samples.datasetid = @datasetid)

go

-- ----------------------------

-- procedure structure for getdatasetsynonyms

-- ----------------------------