Changes between Version 14 and Version 15 of AdvancedReports

Timestamp:

01/11/24 08:45:43 (22 months ago)

Author:

212034

Comment:

--

AdvancedReports

@@ -98 +98 @@
 ==== 5. Период од денот (интервали од 1 час) во кој најмногу се купува билети (часовни и дневни)
 
+{{{#!div style="font-size: 80%"
+  {{{#!sql
+select a.tb_ime, a.interval_1h , a.broj_pati
+from(
+        select tb_ime, interval_1h , count(tb_ime||'-'||interval_1h) broj_pati
+        from(
+                select *, 
+                (to_char(extract (hour from b.b_datum_na_kupuvanje), '00') || ':00 -' || to_char(extract (hour from (b.b_datum_na_kupuvanje  + '1 hours'::interval))::int, '00') || ':00') as interval_1h
+                from tipbilet t 
+                left join bilet b on b.tb_id = t.tb_id 
+                where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
+        )
+        group by tb_ime, interval_1h 
+        order by tb_ime, interval_1h
+) a
+where broj_pati = (
+        select max(broj_pati)
+        from(
+                select tb_ime, interval_1h , count(tb_ime||'-'||interval_1h) broj_pati
+                from(
+                        select *, 
+                        (to_char(extract (hour from b.b_datum_na_kupuvanje), '00') || ':00 -' || to_char(extract (hour from (b.b_datum_na_kupuvanje  + '1 hours'::interval))::int, '00') || ':00') as interval_1h
+                        from tipbilet t 
+                        left join bilet b on b.tb_id = t.tb_id 
+                        where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
+                        and tb_ime = a.tb_ime
+                )
+                group by tb_ime, interval_1h 
+                order by tb_ime, interval_1h
+        )
+--        group by tb_ime
+) and tb_ime in ('Часовен', 'Дневен')
+
+  }}}
+}}}
 
 ==== 6. Вкупна заработувачка од билети и казни на претпријатието во минатата календарска година