--working hours - all offices

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), 1) 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 = 'Leconfield_House'
	)
	and metric_name = 'temp'
	and (value >= 0 and value < 50)
	and display_value is NULL
) as tablewithcounts	


where 
--tablewithcounts."Day" not in ('Saturday ', 'Sunday   ')
tablewithcounts."Day" not like '%Saturday%'
and tablewithcounts."Day" not like '%Sunday%'
and tablewithcounts."Hour" >= 8 and tablewithcounts."Hour" < 16

and tablewithcounts.time not between '2022-01-01' and '2022-01-01'
and tablewithcounts.time not between '2022-01-02' and '2022-01-02'
and tablewithcounts.time not between '2022-01-03' and '2022-01-03'
and tablewithcounts.time not between '2022-01-04' and '2022-01-04'
and tablewithcounts.time not between '2022-03-17' and '2022-03-17'
and tablewithcounts.time not between '2022-04-15' and '2022-04-15'
and tablewithcounts.time not between '2022-04-18' and '2022-04-18'
and tablewithcounts.time not between '2022-05-02' and '2022-05-02'
and tablewithcounts.time not between '2022-06-02' and '2022-06-02'
and tablewithcounts.time not between '2022-06-03' and '2022-06-03'
and tablewithcounts.time not between '2022-07-12' and '2022-07-12'
and tablewithcounts.time not between '2022-08-01' and '2022-08-01'
and tablewithcounts.time not between '2022-08-29' and '2022-08-29'
and tablewithcounts.time not between '2022-11-30' and '2022-11-30'
and tablewithcounts.time not between '2022-12-25' and '2022-12-25'
and tablewithcounts.time not between '2022-12-26' and '2022-12-26'
and tablewithcounts.time not between '2022-12-27' and '2022-12-27'
and tablewithcounts.time not between '2023-01-01' and '2023-01-01'
and tablewithcounts.time not between '2023-01-02' and '2023-01-02'
and tablewithcounts.time not between '2023-01-03' and '2023-01-03'
and tablewithcounts.time not between '2023-03-17' and '2023-03-17'
and tablewithcounts.time not between '2023-04-07' and '2023-04-07'
and tablewithcounts.time not between '2023-04-10' and '2023-04-10'
and tablewithcounts.time not between '2023-05-01' and '2023-05-01'
and tablewithcounts.time not between '2023-05-29' and '2023-05-29'
and tablewithcounts.time not between '2023-07-12' and '2023-07-12'
and tablewithcounts.time not between '2023-08-07' and '2023-08-07'
and tablewithcounts.time not between '2023-08-28' and '2023-08-28'
and tablewithcounts.time not between '2023-11-30' and '2023-11-30'
and tablewithcounts.time not between '2023-12-25' and '2023-12-25'
and tablewithcounts.time not between '2023-12-26' and '2023-12-26'

group by tablewithcounts.freq_val
order by tablewithcounts.freq_val

--limit 1000