--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 ') tablewithcounts."Day" not like '%Saturday%' and tablewithcounts."Day" not like '%Sunday%' and tablewithcounts."Hour" >= 8 and tablewithcounts."Hour" < 16 and tablewithcounts.time not between '2021-08-01' and '2021-08-17' and tablewithcounts.time not between '2021-09-20' and '2021-09-20' and tablewithcounts.time not between '2021-10-18' and '2021-10-25' and tablewithcounts.time not between '2021-12-23' and '2022-01-10' and tablewithcounts.time not between '2022-02-14' and '2022-02-18' and tablewithcounts.time not between '2022-04-11' and '2022-04-22' and tablewithcounts.time not between '2022-05-02' and '2022-05-02' and tablewithcounts.time not between '2022-05-23' and '2022-05-23' and tablewithcounts.time not between '2022-07-01' and '2022-07-31' and tablewithcounts.time not between '2022-08-01' and '2022-08-16' and tablewithcounts.time not between '2022-09-16' and '2022-09-19' and tablewithcounts.time not between '2022-10-17' and '2022-10-24' and tablewithcounts.time not between '2022-12-21' and '2023-01-04' and tablewithcounts.time not between '2023-02-13' and '2023-02-17' and tablewithcounts.time not between '2023-04-03' and '2023-04-14' and tablewithcounts.time not between '2023-05-01' and '2023-05-01' and tablewithcounts.time not between '2023-05-22' and '2023-05-22' and tablewithcounts.time not between '2023-06-29' and '2023-07-31' group by tablewithcounts.freq_val order by tablewithcounts.freq_val --limit 1000
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter