-- non-working hours - all schools

select 
	
	--distinct tablewithcounts.project_name
	tablewithcounts.freq_val, count(tablewithcounts.id)
	

--tablewithcounts.id, tablewithcounts.project_name, tablewithcounts.metric_name, tablewithcounts."time", tablewithcounts."Day", tablewithcounts."Hour", tablewithcounts.value, tablewithcounts.freq_val, tablewithcounts.display_value --, geometry_id

from
(
SELECT
	id, project_name, metric_name, "time", value, display_value, geometry_id,
	round(cast(value as numeric), 0) as freq_val,
	extract(hour from "time") as "Hour",
	to_char("time", 'Day') as "Day"
	FROM public.metrics_app_timeandgeometric_historic
	--where project_name in ('Lasswade')
	where 
	(
	project_name = 'Lasswade'
	or project_name = 'Mauricewood_School'
	or project_name = 'Eaglesham_Primaryschool'
	or
	project_name in (
			'Lasswade'
			--'Roman_Baths',
			'Mauricewood_School',
			--'Leconfield_House',
			'Inverkeithing_Nursery',
			'Windmill',
			'Dunfermline_Highschool',
			'Kinross_Primaryschool',
			'Pitlochry_Highschool',
			'Tulloch_Primaryschool',
			'Viewlands_Primaryschool',
			'Busby_Nursery',
			'Carlibar_Primaryschool',
			'Eaglesham_Elc',
			'Eaglesham_Primaryschool',
			'Glen_Family_Centre',
			'Mearns_Castle_Highschool',
			'Williamwood_Highschool',
			'Glenwood_Family_Centre',
			'Isobel_Mair',
			'Maidenhill_Primaryschool',
			'Olm_Primaryschool',
			'Thornliebank_Primaryschool',
			'St_Lukes_Primaryschool',
			'Abroath_Academy',
			'Abroath_Highschool',
			'Eassie_Primaryschool',
			'Maisondue_Primaryschool',
			'Northmuir_Primaryschool',
			'St_Margarets_Primaryschool',
			'Strathmartine_Primaryschool',
			'Tealing_Primaryschool',
			'Websters_Highschool'
			)
	)
	and metric_name = 'temp'
	and (value >= 0 and value < 50)
	and display_value is NULL
) as tablewithcounts	


where 
-- --tablewithcounts."Day" not in ('Saturday ', 'Sunday   ')

-- WEEKENDS
(
(tablewithcounts."Day" in ('Saturday ', 'Sunday   ')
and 
(tablewithcounts."Hour" >= 0 and tablewithcounts."Hour" < 24)
)
-- WEEKDAYS (MON-THU)
or
(
(tablewithcounts."Day" not in ('Saturday ', 'Sunday   '))
and 
(tablewithcounts."Hour" >= 0 and tablewithcounts."Hour" < 8
or tablewithcounts."Hour" >= 16 and tablewithcounts."Hour" < 24)
)
-- FRIDAYS
or
(
(tablewithcounts."Day" in ('Friday   '))
and
(tablewithcounts."Hour" >= 0 and tablewithcounts."Hour" < 8
or tablewithcounts."Hour" >= 12 and tablewithcounts."Hour" < 24)
)
)

-- HOLIDAYS
or
(
(
tablewithcounts.time between '2021-08-01' and '2021-08-18' -- '2021-08-17'
or tablewithcounts.time between '2021-09-20' and '2021-09-21' -- '2021-09-20'
or tablewithcounts.time between '2021-10-18' and '2021-10-26' -- '2021-10-25'
or tablewithcounts.time between '2021-12-23' and '2022-01-11' -- '2022-01-10'
or tablewithcounts.time between '2022-02-14' and '2022-02-19' -- '2022-02-18'
or tablewithcounts.time between '2022-04-11' and '2022-04-23' -- '2022-04-22'
or tablewithcounts.time between '2022-05-02' and '2022-05-03' -- '2022-05-02'
or tablewithcounts.time between '2022-05-23' and '2022-05-24' -- '2022-05-23'
or tablewithcounts.time between '2022-07-01' and '2022-08-01' -- '2022-07-31'
or tablewithcounts.time between '2022-08-01' and '2022-08-17' -- '2022-08-16'
or tablewithcounts.time between '2022-09-16' and '2022-09-20' -- '2022-09-19'
or tablewithcounts.time between '2022-10-17' and '2022-10-25' -- '2022-10-24'
or tablewithcounts.time between '2022-12-21' and '2023-01-05' -- '2023-01-04'
or tablewithcounts.time between '2023-02-13' and '2023-02-18' -- '2023-02-17'
or tablewithcounts.time between '2023-04-03' and '2023-04-15' -- '2023-04-14'
or tablewithcounts.time between '2023-05-01' and '2023-05-02' -- '2023-05-01'
or tablewithcounts.time between '2023-05-22' and '2023-05-23' -- '2023-05-22'
or tablewithcounts.time between '2023-06-29' and '2023-08-01' -- '2023-07-31'
)
and
(tablewithcounts."Hour" >= 0 and tablewithcounts."Hour" < 24)
)


group by tablewithcounts.freq_val
order by tablewithcounts.freq_val

--order by tablewithcounts.time
--limit 10000