We are tracking date of treatment applications and dates of sampling in DRAGNet. To do this, PAW created a view called 'treatment_date_drag'. Currently this view works as intended BUT I am quite sure that if a site ever samples biomass but not cover in a given year, it will fail to print biomass/treatment data for that year. As of now that's not an issue but the view will need to be amended when it does eventually become an issue (because it will). I guess this will have to be some sort of elaborate if/else statement on whether cover or biomass is present in a given year? Here is the current view:
create or replace
algorithm = UNDEFINED view treatment_year_drag as
select
t1.site_ID as site_id,
t1.site_code as site_code,
t4.year as year,
(cast((t4.year + 1) as signed) - cast(t2.first_nutrient_year as signed)) as year_nutrient_trt,
(cast((t4.year + 1) as signed) - cast(t2.first_disturbance_year as signed)) as year_disturbance_trt,
max(t4.date) as max_cover_date,
max(t7.date) as max_biomass_date,
max((case when (t6.treatment_type = 'Nutrients' and to_days(t4.date) - to_days(t5.treatment_date) > 0) then t5.treatment_date else null end)) as nutrient_application_date,
max((case when (t6.treatment_type = 'Disturbance' and to_days(t4.date) - to_days(t5.treatment_date) > 0) then t5.treatment_date else null end)) as disturbance_application_date
from
plot_dragnet t2
left join sitet1 on
(t1.site_ID = t2.site_ID)
left join subplott3 on
(t2.plot_ID = t3.plot_ID)
left join covert4 on
(t3.subplot_ID = t4.subplot_ID)
left join biomasst7 on
(t3.subplot_ID = t7.subplot_ID and t7.year = t4.year)
left join treatmentt5 on
(t1.site_ID = t5.site_ID)
left join treatment_typet6 on
(t5.treatment_type_id = t6.treatment_type_ID)
group by
t1.site_code,
t4.year;
We are tracking date of treatment applications and dates of sampling in DRAGNet. To do this, PAW created a view called 'treatment_date_drag'. Currently this view works as intended BUT I am quite sure that if a site ever samples biomass but not cover in a given year, it will fail to print biomass/treatment data for that year. As of now that's not an issue but the view will need to be amended when it does eventually become an issue (because it will). I guess this will have to be some sort of elaborate if/else statement on whether cover or biomass is present in a given year? Here is the current view:
create or replace algorithm = UNDEFINED view
treatment_year_dragas selectt1.site_IDassite_id,t1.site_codeassite_code,t4.year as year, (cast((t4.year+ 1) as signed) - cast(t2.first_nutrient_yearas signed)) asyear_nutrient_trt, (cast((t4.year+ 1) as signed) - cast(t2.first_disturbance_yearas signed)) asyear_disturbance_trt, max(t4.date) asmax_cover_date, max(t7.date) asmax_biomass_date, max((case when (t6.treatment_type= 'Nutrients' and to_days(t4.date) - to_days(t5.treatment_date) > 0) then t5.treatment_date else null end)) asnutrient_application_date, max((case when (t6.treatment_type= 'Disturbance' and to_days(t4.date) - to_days(t5.treatment_date) > 0) then t5.treatment_date else null end)) asdisturbance_application_datefrom plot_dragnet
t2left joinsitet1on (t1.site_ID=t2.site_ID) left joinsubplott3on (t2.plot_ID=t3.plot_ID) left joincovert4on (t3.subplot_ID=t4.subplot_ID) left joinbiomasst7on (t3.subplot_ID=t7.subplot_IDand t7.year = t4.year)left join
treatmentt5on (t1.site_ID=t5.site_ID) left jointreatment_typet6on (t5.treatment_type_id=t6.treatment_type_ID) group byt1.site_code,t4.year;