-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathvwGBIF_INBO_meetnetten_vlinders_transecten_meas.sql
192 lines (95 loc) · 5.31 KB
/
vwGBIF_INBO_meetnetten_vlinders_transecten_meas.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
USE [S0008_00_Meetnetten]
GO
/****** Object: View [ipt].[vwGBIF_INBO_meetnetten_1_vlinders_transecten_Meas] Script Date: 29/10/2020 10:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [ipt].[vwGBIF_INBO_meetnetten_1_vlinders_transecten_Meas]
AS
SELECT --fa.* --unieke kolomnamen
-- [parentEventID] = N'visitID :' + Right( N'000000000' + CONVERT(nvarchar(20) , fA.FieldworkVisitID),6)
---EVENT---
[eventID] = N'INBO:MEETNET:EVENT:' + Right( N'000000000' + CONVERT(nvarchar(20) , fA.FieldworkSampleID),6)
-- , [eventDate] = fwp.VisitStartDate
--- Properties---
-- , [measurementID] = Fco.AttributeID
, [measurementType] = case FCo.AttributeName
WHEN 'wind-force' THEN 'wind force'
ELSE FCo.AttributeName
END
, [measurementValue] = CASE
WHEN FCO.AttributeUnit = 'temperature' AND FCO.AttributeValue = 'onbekend' THEN ''
WHEN FCO.AttributeUnit = 'temperature' AND FCO.AttributeValue = '' THEN ''
WHEN FCO.AttributeValue = 'windstil (0 Bft)' THEN '0'
WHEN FCO.AttributeValue = 'zeer zwakke wind (1 Bft)' THEN '1'
WHEN FCO.AttributeValue = 'zwakke wind (2 Bft)' THEN '2'
WHEN FCO.AttributeValue = 'vrij matige wind (3 Bft)' THEN '3'
WHEN FCO.AttributeValue = 'matige wind (4 Bft)' THEN '4'
WHEN FCO.AttributeValue = 'vrij krachtige wind (5 Bft)' THEN '5'
WHEN FCO.AttributeValue = 'heldere hemel (0/8)' THEN 'clear (0/8)'
WHEN FCO.AttributeValue = 'unclouded' THEN 'clear (0/8)'
WHEN FCO.AttributeValue = 'lichtbewolkt (1 tot 2/8)' THEN 'mostly clear (1/8 - 2/8)'
WHEN FCO.AttributeValue = 'halfbewolkt (3 tot 5/8)' THEN 'partly cloudy (3/8 - 5/8)'
WHEN FCO.AttributeValue = 'half clouded' THEN 'partly cloudy (3/8 - 5/8)'
WHEN FCO.AttributeValue = 'partially clouded' THEN 'partly cloudy (3/8 - 5/8)'
WHEN FCO.AttributeValue = 'zwaarbewolkt (6 tot 7/8)' THEN 'mostly cloudy (6/8 - 7/8)'
WHEN FCO.AttributeValue = 'heavily clouded' THEN 'mostly cloudy (6/8 - 7/8)'
WHEN FCO.AttributeValue = 'betrokken (8/8)' THEN 'cloudy (8/8)'
WHEN FCO.AttributeValue = 'onbekend' THEN 'unknown'
WHEN FCO.AttributeValue = '' THEN 'unknown'
ELSE FCO.AttributeValue
END
, [measurementUnit] = CASE FCO.AttributeUnit
WHEN 'temperature' THEN ' °C'
WHEN 'wind-force' THEN 'Beaufort'
WHEN 'cloudiness' THEN 'okta'
ELSE 'unknown'
END
FROM (SELECT DISTINCT(FieldworkSampleID),FieldworkVisitID,ProjectKey, LocationKey, ProtocolKey, LocationID, ProtocolID, SpeciesActivityID, SpeciesActivityKey, SpeciesLifestageID, SpeciesLifestageKey FROM dbo.FactAantal WHERE FieldworkSampleID > 0) fA
INNER JOIN dbo.dimProject dP ON dP.ProjectKey = fA.ProjectKey
INNER JOIN dbo.DimProtocol dProt ON dProt.ProtocolKey = fA.ProtocolKey
INNER JOIN dbo.DimSpeciesActivity dSA ON dSA.SpeciesActivityKey = fA.SpeciesActivityKey
INNER JOIN dbo.DimSpeciesLifestage dSL ON dSL.SpeciesLifestageKey = fA.SpeciesLifestageKey
-- INNER JOIN dbo.DimSpecies dSP ON dsp.SpeciesKey = fa.SpeciesKey
INNER JOIN (SELECT DISTINCT(FieldworkSampleID), VisitStartDate FROM dbo.FactWerkpakket ) FWp ON FWp.FieldworkSampleID = fa.FieldworkSampleID
INNER JOIN FactCovariabele FCo ON FCo.FieldworkSampleID = fA.FieldworkSampleID
-- LEFT OUTER JOIN dbo.DimModel DMo ON DMo.AttributeID = FCo.AttributeID
WHERE 1=1
--AND ProjectName = '***'
--AND fa.ProjectKey = '16'
AND fa.ProtocolID IN ('1') ---Vlinders transecten * ,'15','28' removed other protocols
--AND Aantal > '0'
AND fwp.VisitStartDate > CONVERT(datetime, '2016-01-01', 120)
AND fwp.VisitStartDate < CONVERT(datetime, '2018-12-31', 120)
--AND projectName = 'Argusvlinder'
--AND fa.FieldworkObservationID = 491520
--ORDER BY speciesName Asc
--ORDER BY fa.FieldworkObservationID
--AND ParentLocationName in ('Groot Schietveld 2','Klein Schietveld')
--AND projectname = 'kommavlinder'
--AND ProjectName = 'heivlinder'
--AND fA.FieldworkSampleID = '190441'
--AND SpeciesLifestageName = 'imago'
--SELECT fa.FieldworkSampleID, count(*) as tel
/***FROM (SELECT DISTINCT(FieldworkSampleID),FieldworkVisitID,ProjectKey, LocationKey, ProtocolKey, LocationID, ProtocolID, SpeciesActivityID, SpeciesActivityKey, SpeciesLifestageID, SpeciesLifestageKey FROM dbo.FactAantal WHERE FieldworkSampleID > 0) fA
INNER JOIN dbo.dimProject dP ON dP.ProjectKey = fA.ProjectKey
INNER JOIN dbo.DimLocation dL ON dL.LocationKey = fA.LocationKey
INNER JOIN dbo.DimProtocol dProt ON dProt.ProtocolKey = fA.ProtocolKey
INNER JOIN dbo.DimSpeciesActivity dSA ON dSA.SpeciesActivityKey = fA.SpeciesActivityKey
INNER JOIN dbo.DimSpeciesLifestage dSL ON dSL.SpeciesLifestageKey = fA.SpeciesLifestageKey
-- INNER JOIN FactCovariabele FCo ON FCo.FieldworkSampleID = fA.FieldworkSampleID
INNER JOIN (SELECT DISTINCT(FieldworkSampleID), VisitStartDate FROM dbo.FactWerkpakket ) FWp ON FWp.FieldworkSampleID = fa.FieldworkSampleID
--INNER JOIN FactCovariabele FCo ON FCo.FieldworkSampleID = fA.FieldworkSampleID
WHERE 1=1
--AND ProjectName = 'Vuursalamander'
--AND ProtocolName = 'Vlinders - Transecten'
--AND fa.ProjectKey = '16'
AND fa.ProtocolID = '1'
-- AND fa.FieldworkSampleID in ('196717','196456','197026','54759','194584')
--AND ParentLocationGeom IS NULL
--ORDER BY FA.FieldworkSampleID DesC
--- Verification by counts ---
-- GROUP BY fa.FieldworkSampleID
-- ORDER BY tel DESC **/
GO