-- 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