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