"Inactive" sites haven't had events submitted within the past 365 days. If new events are submitted, they will become active again.
All sites
Adopt-A- Stream
Rivers Alive
Both programs
No events
Click a photo for a large version
select Monitoring_Site_ID,
WaterbodyName,
c as Events,
format(mind,"mm/dd/yyyy") as MinDate,
format(maxd,"mm/dd/yyyy") as MaxDate,
iif(maxd<now()-365,"Inactive","") as Active,
Program as Type
from (select Monitoring_Site_ID,WaterbodyName&" "&SiteName as WaterbodyName,Program from Sites where Site_Lat and Group_ID=XXXX) as S
left join (
select site,c,mind,maxd from (
select site,sum(c) as c,min(mind) as mind,max(maxd) as maxd from (
select site,count(*) as c,min(date) as mind,max(date) as maxd from qrec where Date group by site
union all
select site,count(*),min(Date) as mind,max(Date) as maxd from events where Date group by site
)
group by site
)
) as E
on S.Monitoring_Site_ID=E.site
where #cond#
order by Program,1
Adopt-A-
