-- 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
,p.bottom
(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
|