/
SQL views i Geogis2020
SQL views i Geogis2020
Regionerne (Unlicensed)
Owned by Regionerne (Unlicensed)
Nedenstående er nogle sql-views som Tom Birch Hansen har lavet, som kan bruges til diverse udtræk.
Grundlæggende er det tabeldata + basisdata fra overordnede punkt/projekt + kodelisteværdier i læselig form
AnalysisSamples Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2017-08-17
CREATE view [dbo].[TBH_AnalysisSample] AS
SELECT
s.SampleId
,s.sampleno
,s.PointId
,p.PointNo
,p.PublicNo
,pj.ProjectNo
,pj.ProjectId
,s.MediumId as MediumK
,am.Medium as MediumT
,s.Depth1
,s.Depth2
,i.Depth1 as indtagdybde1
,i.Depth2 as indtagdybde2
,s.IntakeNo
,s.SampleDate
,s.ReportDate
,s.ReportNo
,s.LaboratoryId as LaboratoryK
,al.Laboratory as LaboratoryT
,s.[SampleTypeId] as SampleTypeK
,sType.SampleType as SampleTypeT
,pp.PhaseStart
,pp.Description as projektfaseBeskrivelse
,ascompany.CompanyId as SampleCompanyID
,ascompany.Name as SampleCompanyName
-- ,p.Z1-s.depth1 as Dybde1Kote
-- ,p.Z1-s.depth2 as Dybde2Kote
FROM [dbo].[AnalysisSamples] as s
inner join dbo.Points as p
on s.PointId=p.PointId
inner join dbo.Projects as pj
on p.ProjectId=pj.ProjectId
left join dbo.AnalysisMedia as am
on s.MediumId=am.MediumId
left join dbo.Intakes as i
on s.IntakeNo=i.IntakeNo and s.pointid=i.PointId
left join dbo.ProjectPhases as pp
on s.PhaseId=pp.PhaseId
left join dbo.AnalysisLaboratories as al
on s.LaboratoryId=al.LaboratoryId
LEFT JOIN
dbo.Companies as ascompany
on s.SamplingOrgId=ascompany.CompanyId
LEFT JOIN
dbo.AnalysisSampleTypes as sType
on s.SampleTypeId=stype.SampleTypeId
LEFT JOIN
dbo.AnalysisSampleLocationTypes as aslt
on s.SampleLocationTypeId=aslt.SampleLocationTypeId
LEFT JOIN
dbo.AnalysisSampleLocations as asl
on s.SampleLocationId=asl.SampleLocationId
Intake Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2017-07-15
CREATE view [dbo].[TBH_intake] as
SELECT
i.[IntakeId]
,i.[PointId]
,p.PointNo
,p.PublicNo
,p.Synonym
,p.pointtype as pointtypeK
,pj.ProjectNo
,pj.projectid
,i.[Intake]
,i.[IntakeNo]
,i.[ScreenNo]
,i.[StringNo]
,i.[Description]
,i.[Diameter]
,i.[Diametermm]
,i.[Top]
,i.[Bottom]
,i.[IntakeDistance] -- blindrør
,i.[IntakeLength] -- indtagslængde
-- ,i.[IntakeWidth]
,i.[Material] -- materiale af røret
,i.[Depth1]
,i.[Depth2]
,p.Z1 - i.Depth1 as Level1
,p.Z1 - i.Depth2 as Level2
-- ,i.[LayerId]
-- ,i.[LayerIdList]
-- ,i.[TValue]
-- ,i.[SValue]
-- ,i.[Aquifertype]
-- ,i.[SpecificCapacity]
-- ,i.[Efficiency]
-- ,i.[BarometicEfficiency]
,i.[Reference] -- reference i tekst
,i.[ReferenceLevel] -- referencekote
,p.z1+i.[top] as ReferenceLevel_Calculated -- beregnet referencekote ud fra borings z og indtag top
-- ,i.[Correction]
-- ,i.[TriggerLevel]
-- ,i.[AmberLevel]
,i.[VerticalRefId]
,vr.VerticalRef
-- ,i.[StartDate]
-- ,i.[Enddate]
-- ,i.[FRefCom]
-- ,i.[FRefJob]
-- ,i.[FRefBor]
-- ,i.[ScreenDiameter]
-- ,i.[ScreenDiametermm]
-- ,i.[Correction2]
,i.[WaterSounding] -- pejlbar
FROM [dbo].[Intakes] as i
inner join dbo.Points as p
on i.PointId=p.PointId
inner join dbo.Projects as pj
on p.ProjectId=pj.ProjectId
left join dbo.VerticalRefs as vr
on i.VerticalRefId=vr.VerticalRefId
Point Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2017-08-21
CREATE VIEW [dbo].[TBH_point]
AS
SELECT
p.[PointId] -- pointid
,p.[PointNo] -- pointnumer
,p.[PublicNo] -- DGU nummer
,p.[Synonym]
,pr.[ProjectNo] -- projektnummeret
,pr.Location as jarlok -- jar-loknr
,pr.[Title] as ProjectTitle
,p.[ProjectId]
,p.[Description1]
,p.[Description2]
,p.[Description3]
,p.[PointType] as PointTypeK
,pType.[Description] as PointTypeT
,case
when ptype.pointgroup='1. boring' and p.[AbandonDate] is not null then 'Boring, sløjfet'
when ptype.pointgroup='1. boring' and filtersat.pointid is not null then 'Boring, filtersat'
when ptype.pointgroup='1. boring' and filtersat.pointid is null then 'Boring, ikke filtersat'
when ptype.pointtype='O' or ptype.pointtype='OBL' or ptype.pointtype='S' then 'overfladeprøve'
when p.[use]='VA' then 'Boring, afværge' -- ny
when p.[use]='C' then 'Brønd' -- ny
when ptype.pointtype='VA' then 'vandprøve, ikke boring'
when ptype.pointtype='BP' or ptype.pointtype='KP' then 'prøve fra gravning'
when ptype.pointtype='AG' then 'afgravning'
when ptype.pointtype='MI' then 'prøve fra mile/jordbunke'
when ptype.pointtype='A' then 'anlæg' -- ny . til afværgeanlæg
when ptype.pointtype='UL' or ptype.pointtype='ULREF' then 'Luftprøve, udeluft'
when ptype.pointtype='IL' or ptype.pointtype='ILREF' then 'Luftprøve, indendørs'
when ptype.pointtype='MP' or ptype.pointtype='PL' then 'Luftprøve, poreluft'
end as GISprøvestedstype -- En tolket prøvestedstype til brug som signatur på punktet i RM's GIS
,p.[PhaseId] as PhaseK -- kode for hvilken fase datapunktet knytter sig til
,projectphase.Description
as PhaseT -- navnet på projektfasen som punkten tilknytter isg
,p.[Purpose] as PurposeK
,pPurpose.Description as PurpostT
,p.[Use] as UseK
,puse.Description as UseT
,p.[Method] as MethodK
,pmethod.[Description] as MethodT
,p.[WaterSounding] as Pejlbar
,p.[Top] as [Top]
,p.Z1 - p.[Top] as zTop
,p.[Bottom] as Bottom
,p.Z1 - p.[bottom] as zBottom
,p.[MunicipalityNo]
,p.[Address]
,p.[Town]
,p.[Postalcode] as PostNr
,PostalCode.District as PostBy
,p.[DateStart]
,p.[DateEnd] -- boredato, prøvetagningsdato
,p.[AbandonDate] -- sløjfedato
,p.[AbandonCause] as AbandonCauseK -- sløjfeårsagkode
,pAbandonCause.Description
as AbandonCauseT -- sløjfeårsag-tekst
,p.[AbandonContractorId]
,pAbandonContractor.Company
as AbandonContractorT
,p.[Owner]
,p.[DataOwner] as DataOwnerK
,pDataOwner.[Description] as DataOwnerT
,p.[ContractorId] as PointContractorK
,pContractor.Company as PointContractorT
,p.[Contractor] as PointContractorText /* entreprenør i tekst */
,p.[ContractorInitials]
,p.[GeoCompany]
,p.[GeoInitials]
,p.[Status] -- evt. status for hvor langt indtasting / boringen er nået
,p.[ExecutedBy]
,p.[ExecutedDate]
,p.[CheckedBy]
,p.[CheckedDate]
,p.[ApprovedBy]
,p.[ApprovedDate]
-- ,p.[PlanEnclosure] -- ?
-- ,p.[DateClose]
,p.[Projection1] as Projection1K
,pProjection.label as Projection1T
,p.[X1] as x1
,p.[Y1] as y1
,p.[Z1] as z1
,p.AngleX
,p.AngleZ
,p.[VerticalRefId1] as VerticalRefId1 -- højdesystem
,p.[CoordinateMethod1] as CoordinateMethod1
,pCoordinateMethod1.[Description]
as CoordinateMethod1T
,p.[CoordinateSource1] as CoordinateSource1K
,pCoordinateSource.[Description]
as CooridnateSourceT
,p.[ElevationMethod1] as ElevationMethod1 -- højdesystemmetode
,pElevationMethod1.[Description]
as ElevationMethod1T -- højdesystemmetode
,p.[CoordinateQuality1] as CoordinateQuality1K
,pCoordinateQuality1.[Description]
as CoordinateQuality1T
/* ,p.[Projection2]
,p.[X2]
,p.[Y2]
,p.[CoordinateMethod2]
,p.[CoordinateSource2]
,p.[CoordinateQuality2]
,p.[VerticalRefId2]
,p.[Z2]
,p.[ElevationMethod2] */
-- ,p.[XUTM32EUREF89]
-- ,p.[YUTM32EUREF89]
-- ,p.[ZDVR90]
/* ,p.[XLocal]
,p.[YLocal]
,p.[ZLocal] */
-- ,p.[AlignmentId]
-- ,p.[Station]
-- ,p.[Offset]
-- ,p.[AreaId]
-- ,p.[Marking]
,p.[Rig] as RigK
,prig.Description as RigT
-- ,p.[Enclosure]
-- ,p.[LinkId] -- ?
-- ,p.[WKT]
-- ,p.[Geom]
-- ,p.[FRef] -- ?
,p.[FRefCom]
,p.[FRefJob]
,p.[FRefBor]
-- ,p.[FRefBor2]
-- ,p.[FRefDB]
,p.[Active]
,p.geom
,pr.[Locked] as projectLocked
-- , case when (select top 1 1 from dbo.Intakes where dbo.Intakes.pointid=p.pointid)=1 then 1 else 0 end as ErFiltersat
, case when filtersat.PointId is null then 0 else 1 end as ErFiltersat
,p.Sequence as rækkefølge
FROM
dbo.Points as p
INNER JOIN
dbo.Projects as pr
ON p.ProjectId=pr.ProjectId
LEFT JOIN
dbo.projectPhases as projectphase
ON p.PhaseId=projectphase.PhaseId
LEFT JOIN
dbo.Companies as prCompany -- company på projektet
ON pr.CompanyId=prCompany.CompanyId
LEFT JOIN
dbo.Companies as pContractor -- contractor på punktet
ON p.ContractorId=pContractor.CompanyId
LEFT JOIN
dbo.PointTypes as pType
ON p.pointtype = pType.PointType and ptype.Setup='DK'
LEFT JOIN
dbo.PointUses as pUse
ON p.[use] = pUse.[Use] and puse.Setup='DK'
LEFT JOIN
dbo.PostalCodes as PostalCode
ON p.Postalcode = PostalCode.PostalCode
LEFT JOIN
dbo.Rigs as pRig
ON p.Rig = pRig.Rig
LEFT JOIN
dbo.PointAbandonCauses as pAbandonCause
ON p.AbandonCause = pAbandonCause.AbandonCause
LEFT JOIN
dbo.Companies as pAbandonContractor -- contractor på sløjfning
ON p.AbandonContractorId=pAbandonContractor.CompanyId
LEFT JOIN
dbo.PointCoordinateMethods as pCoordinateMethod1
ON p.CoordinateMethod1 = PCoordinateMethod1.CoordinateMethod
LEFT JOIN
dbo.PointCoordinateQualities as pCoordinateQuality1
ON p.CoordinateQuality1 = pCoordinateQuality1.Setup
LEFT JOIN
dbo.PointDataOwners as pDataOwner
ON p.DataOwner = pDataOwner.DataOwner
LEFT JOIN
dbo.PointElevationMethods as pElevationMethod1
ON p.ElevationMethod1 = pElevationMethod1.ElevationMethod
LEFT JOIN
dbo.PointMethods as pMethod
ON p.Method=pmethod.Method and pmethod.Setup='DK'
LEFT JOIN
dbo.PointPurposes as pPurpose
ON p.Purpose=pPurpose.Purpose and pPurpose.Setup='DK'
LEFT join
dbo.Projections as pProjection
ON p.Projection1=pProjection.Epsg
LEFT JOIN
[dbo].[PointDataSources] as pCoordinateSource
on p.CoordinateSource1=pcoordinatesource.Datasource and pCoordinateSource.Setup='DK'
/*LEFT join
dbo.VerticalRefs as pVerticalRef1
on p.VerticalRefId1=pVerticalRef1.VerticalRef
*/
LEFT JOIN
(Select I.pointid from dbo.Intakes as I
group by I.PointId
) as filtersat
on filtersat.PointId=p.PointId
-- where p.[use]='va'
GO
Project Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2017-08-21
CREATE VIEW [dbo].[TBH_project] AS
SELECT
p.[ProjectId]
,p.[ProjectNo]
-- p.[CompanyId] as companyK
-- ,c.Name as companyNameT
-- ,p.[CompanyName]
-- ,p.[sProjectId]
--
-- ,p.[ProjectNo2]
-- ,p.[ProjectType]
,p.[Title]
-- ,p.[LocationId]
-- ,p.[JupiterId]
-- ,p.[DataOwner]
,p.[Location] as jarlok
,p.[Description1]
,p.[Description2]
,p.[Description3]
,p.[Address]
,p.[Town]
,p.[Postalcode]
,p.[RegionNo]
,reg.[Region]
,p.[MunicipalityNo]
,MUN.Municipality
-- ,p.[MunicipalityNo2]
-- ,p.[Client]
-- ,p.[Contractor]
-- ,p.[Initials1]
-- ,p.[Initials2]
-- ,p.[Report]
,p.[Projection1]
,p.[X1]
,p.[Y1]
-- ,p.[Projection2]
-- ,p.[X2]
-- ,p.[Y2]
-- ,p.[XUTM32EUREF89]
-- ,p.[YUTM32EUREF89]
-- ,p.[DateStart]
-- ,p.[DateEnd]
/* ,p.[SyntaxId]
,p.[Interpretation]
,p.[SeriesId]
,p.[AutoGenerateStrata]
,p.[AutoGenerateStrataAtSamples]
,p.[AutoGenerateStrataIgnoreNoMatch]
,p.[MaxInterpolationLength]
,p.[Interpretation2]
,p.[SeriesId2]
,p.[AutoGenerateStrata2]
,p.[AutoGenerateStrataAtSamples2]
,p.[AutoGenerateStrataIgnoreNoMatch2]
,p.[MaxInterpolationLength2]
,p.[Setup]
,p.[LinkId]
*/
,p.[Locked]
,p.[Url]
-- ,p.[ProjectFolder]
,p.[Active]
,p.[Wkt]
-- ,p.[Geom]
-- ,p.[FRef]
,p.[FRefCom]
,p.[FRefJob]
,p.[Status]
FROM [GeoGis2020].[dbo].[Projects] as p
LEFT JOIN
dbo.companies as c ON c.CompanyId = p.CompanyId
LEFT JOIN
[dbo].[Municipalities] as mun
on p.MunicipalityNo=mun.MunicipalityNo
LEFT JOIN
[dbo].[Regions] as reg
on p.RegionNo=reg.RegionNo
GO
Samples Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2015-12-15
CREATE view [dbo].[TBH_Samples]
AS
SELECT
s.[PointId]
,s.[SampleId]
,s.[SampleNo]
,s.[SampleType]
,stype.description as SampleTypeT
,s.[Depth1]
-- ,s.[Depth2]
,s.[Length]
-- ,s.[CoreRun]
-- ,s.[CoreLostFromTop]
-- ,s.[Diameter]
-- ,s.[Recovery]
-- ,s.[RecoverySolid]
-- ,s.[RQD]
,s.[Description]
,s.[Description2]
,s.[Liths]
,s.[Symbols]
-- ,s.[Weathering]
-- ,s.[WaterAbsorption]
-- ,s.[AbrasionValue]
-- ,s.[RockMassRating]
-- ,s.[TestList]
-- ,s.[LabCompanyId]
-- ,s.[LabSampleNo]
,s.[LayerId] as LayerK
,layer.Description as LayerT
-- ,s.[LayerId2]
-- ,s.[EDepth1]
-- ,s.[EDepth2]
-- ,s.[FRef]
-- ,s.[FRefCom]
-- ,s.[FRefJob]
-- ,s.[FRefBor]
FROM
[dbo].[Samples] as s
INNER JOIN
dbo.Points as p
on s.PointId=p.PointId
LEFT JOIN
dbo.SampleTypes as sType
on s.SampleType=stype.SampleType and stype.Setup='DK'
LEFT JOIN
dbo.Layers as layer
on s.LayerId=layer.LayerId
GO
Strata Expand source
/** lavet af Tom Birch Hansen
ALTER view [dbo].[TBH_Strata] AS
SELECT
s.[PointId]
,s.[StratumId]
,p.PointNo
,p.PublicNo
,pj.ProjectNo
,s.[Interpretation]
,s.[Depth1]
,s.[Depth2]
,s.[LayerId] as LayerK
,l.Layer as LayerS
--,l.SymbolNo as SymbolNo
,l.Description as LayerT
,s.[Description]
,s.[Liths]
,s.[Symbols]
,s.[Qac]
,s.[FRef]
FROM [dbo].[Strata] as s
inner join dbo.Points as p
on s.PointId=p.PointId
inner join dbo.Projects as pj
on p.ProjectId=pj.ProjectId
inner join dbo.Layers as l
on s.LayerId=l.LayerId
GO
Waterlevels Expand source
/** lavet af Tom Birch Hansen
/** sidst opdateret d. 2017-08-21
CREATE view [dbo].[TBH_Waterlevel] as
SELECT
w.[IntakeId]
,i.Intake
,i.IntakeNo
,i.ReferenceLevel as indtag_Referencekote
,p.PointNo
,p.pointid
,p.PublicNo
,p.Synonym
,p.X1
,p.Y1
,pj.ProjectId
,pj.ProjectNo
,w.[ValueId]
,w.[Time]
,w.[ExtremeId] as ExtremeK
,wle.Extreme as ExtremeT
,w.[Depth]
,w.[Reading]
,w.[ReferenceLevel]-w.[Depth] as WLevel -- koten for grundvandet
,w.[Depth]-w.[ReferenceLevel]-p.[Z1] as Wdepth -- afstand fra boringens top dvs terræn og ned til vandstanden.
,w.[Temperature]
,w.[TemperatureAir]
,w.[Conductivity]
,w.[Salinity]
,w.[Reference] -- referencetekst for den enkelte pejling
,w.[ReferenceLevel] -- referencekoten for målingen
,w.[Correction]
,w.[VerticalRefId]
,w.[Fall]
,w.[Flow]
,w.[Pressure]
,w.[ProjectCode]
,w.[RoundNo]
,w.[MethodId] as MethodK
,wlm.Method as MethodT
,w.[SituationId] as SituationK
,waterlevelsituation.Situation
as SituationT
,w.[CategoryId] as CategoryK
,wlc.Category as CategoryT
,w.[QualityId] as QualityK
,wlq.Quality as QualityT
,w.[SouCompany]
,w.[SouInitials]
,w.[Description]
,w.[Correction2]
FROM
[dbo].[WaterLevels] as w
inner join dbo.Intakes as i
on w.IntakeId=i.IntakeId
inner join dbo.Points as p
on i.PointId=p.PointId
inner join dbo.Projects as pj
on p.ProjectId=pj.ProjectId
left join dbo.WaterLevelSituations as waterlevelsituation
on w.SituationId=waterlevelsituation.SituationId
left join dbo.WaterLevelExtremes as wle
on w.ExtremeId=wle.ExtremeId
left join dbo.WaterLevelMethods as wlm
on w.MethodId=wlm.Methodid
left join dbo.WaterLevelQualities as wlq
on w.QualityId=wlq.QualityId
left join dbo.WaterLevelCategories as wlc
on w.CategoryId=wlc.CategoryId
Analysisvalue Expand source
CREATE view [dbo].[TBH_Analysisvalue] AS
SELECT
av.[AnalysisId]
,s.SampleId
,s.sampleno
,s.PointId
,p.PointNo
,p.PublicNo
,pj.ProjectNo
,pj.ProjectId
,am.Medium as mediumT
,am.MediumId AS mediumK
,s.Depth1
,s.Depth2
,i.Depth1 as indtagdybde1
,i.Depth2 as indtagdybde2
,s.IntakeNo
,s.SampleDate
,s.ReportDate
,s.ReportNo
,al.Laboratory as LaboratoryT
,pp.PhaseStart
,pp.Description as projektfaseBeskrivelse
-- ,p.Z1-s.depth1 as Dybde1Kote
-- ,p.Z1-s.depth2 as Dybde2Kote
-- ,av.[Distance]
,av.[ParameterId] as ParameterK
,ap.Parameter as ParameterT
,av.[MethodId] as MethodK
,aMethod.Method as MethodT
,av.[Sequence] as Sequence
,av.[Attribute] as AttributeK
,aAtrib.Description as AttributeT
,av.[Value]
,av.[ValueText]
,av.[DetectionLimit]
,av.[Uncertainty]
,av.[UnitId] as UnitK
,aUnit.Unit as UnitT
,av.[Remark]
,av.[AnalysisDate]
,av.[SiteId] as SiteK
,aSite.Site as SiteT
,ag.GroupName as ParameterGroupK
,ag.Description as ParameterGroupT
-- ,av.[LaboratoryId]
-- ,av.[PreparationId]
-- ,av.[PreservationId]
-- ,av.[PackagingId]
-- ,av.[FiltrationId]
-- ,av.[FractionId]
-- ,av.[QualityId]
,av.[Accredited]
,av.[Approved]
,av.[Retest]
-- ,av.[JupiterId] as JupiterID
,av.[FRef] as anaFref
,av.[FRefCom] as anaRefCom
,av.[FRefJob] as anaREFJob
,av.[FRefBor] as anaREFbor
,av.[FRefLoc] as AnarefLOC
,av.[FRefNo] as AnaREFNO
,av.[FRefWSa] as AnaRefWsa
,av.[UncertaintyPct] as UncertaintyPct
-- ,av.[JupiterId2]
FROM [GeoGis2020].[dbo].[AnalysisValues] as av
inner join [dbo].[AnalysisSamples] as s
on av.SampleId=s.SampleId
inner join dbo.Points as p
on s.PointId=p.PointId
inner join dbo.Projects as pj
on p.ProjectId=pj.ProjectId
left join dbo.AnalysisMedia as am
on s.MediumId=am.MediumId
left join dbo.Intakes as i
on s.IntakeNo=i.IntakeNo and s.pointid=i.PointId
left join dbo.ProjectPhases as pp
on s.PhaseId=pp.PhaseId
left join dbo.AnalysisLaboratories as al
on s.LaboratoryId=al.LaboratoryId
left join dbo.AnalysisParameters as ap
on ap.ParameterId=av.ParameterId
left join dbo.analysisGroups as ag
on ap.GroupId=ag.GroupId
left join dbo.AnalysisMethods as aMethod
on aMethod.MethodId=av.MethodId
left join [dbo].[AnalysisAttributes] as aAtrib
on aAtrib.Attribute=av.Attribute
left join dbo.AnalysisUnits as aUnit
on aUnit.UnitId=av.UnitId
left join [dbo].[AnalysisSites] as aSite
on aSite.SiteId=av.siteid
PointStat - Statistik på punkter Expand source
-- forklaring:
-- Cxxxx = count dvs antal
CREATE VIEW [dbo].[TBH_pointStat]
AS
SELECT
p.[PointId] -- pointid
,p.[PointNo] -- pointnumer
,p.[PublicNo] -- DGU nummer
,p.[Synonym]
,pr.[ProjectNo] -- projektnummeret
--,pr.Location as jarlok -- jar-loknr
--,pr.[Title] as ProjectTitle
,p.[ProjectId]
, (select count(str.StratumId) from dbo.strata as str where str.pointid=p.pointid)
as cStrata
, (select count(q.sampleid) from dbo.Samples as q where q.pointid=p.pointid)
as cSoilsample
, (select count(q.sampleid) from dbo.Samples as q where q.pointid=p.pointid and isnull(q.sampletype,'')<>'O' and isnull(q.sampletype,'')<>'0')
as cSoilsampleGeology -- antal poster med andet end typen 'o'=omrørt prøve i jordprøvetabellen
, (select count(q.IntakeId) from dbo.Intakes as q where q.pointid=p.pointid)
as cIntake
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=1)
as cAnalysissampleSoil
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=2)
as cAnalysissampleGW
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=3)
as cAnalysissamplePoreluft
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=4)
as cAnalysissampleRecipient
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=5)
as cAnalysissampleDrikkevand
, (select count(q.sampleid) from dbo.AnalysisSamples as q where q.pointid=p.pointid and q.MediumId=6)
as cAnalysissampleIndeklima
, (select count(q.testid) from dbo.[InsituVaneTests] as q where q.pointid=p.pointid)
as cInsituVaneTests
, (select count(q.pointid) from dbo.[penetrationtests] as q where q.pointid=p.pointid)
as cPenetrationTests
, (select count(q.pointid) from dbo.[cptpush] as q where q.pointid=p.pointid)
as cCPTdata
, (select max(depth1) from dbo.analysissamples as q where q.pointid=p.pointid and q.MediumId=1)
as maxAnalysisDepth
, (select min(depth1) from dbo.analysissamples as q where q.pointid=p.pointid and q.MediumId=1)
as minAnalysisDepth
, (select case when isnull(max(depth1),0)>isnull(max(depth2),0) then isnull(max(depth1),0) else isnull(max(depth2),0) end from dbo.samples as q where q.pointid=p.pointid)
as maxSoilDepth -- max dybde af jordprøver
, (select case when isnull(max(depth1),0)>isnull(max(depth2),0) then isnull(max(depth1),0) else isnull(max(depth2),0) end from dbo.strata as q where q.pointid=p.pointid)
as maxStrataDepth -- max dybde af strata
,p.bottom
, (Select max(bottom) from dbo.intakes as q where q.pointid=p.pointid)
as maxIntakeBottom
FROM
dbo.Points as p
INNER JOIN
dbo.Projects as pr
ON p.ProjectId=pr.ProjectId