Changes between Version 6 and Version 7 of AdvancedReports
- Timestamp:
- 03/10/22 23:33:31 (2 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedReports
v6 v7 10 10 11 11 left join 12 ( select bc.salon_id, count(v _bc.user_id_vraboten) as broj_vraboteni12 ( select bc.salon_id, count(v.user_id) as broj_vraboteni 13 13 from beautycenter as bc 14 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id14 join vraboteni as v on bc.salon_id = v.salon_id 15 15 group by bc.salon_id 16 16 ) as br_vraboteni on bc.salon_id = br_vraboteni.salon_id … … 19 19 ( select bc.salon_id, count(t.termin_id) as broj_termini 20 20 from beautycenter as bc 21 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id 22 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id 23 left join termini as t on v.user_id = t.user_id_vraboten 21 join vraboteni as v on bc.salon_id = v.salon_id 22 join termini as t on v.user_id = t.user_id_vraboten 24 23 group by bc.salon_id 25 24 ) as br_termini on bc.salon_id = br_termini.salon_id … … 28 27 ( select bc.salon_id, count(r.br_rez) as broj_rez 29 28 from beautycenter as bc 30 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id 31 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id 32 left join termini as t on v.user_id = t.user_id_vraboten 33 left join rezervacija as r on t.termin_id = r.termin_id 29 join vraboteni as v on bc.salon_id = v.salon_id 30 join termini as t on v.user_id = t.user_id_vraboten 31 join rezervacija as r on t.termin_id = r.termin_id 34 32 group by bc.salon_id 35 33 ) as br_rezervacii on bc.salon_id = br_rezervacii.salon_id … … 38 36 ( select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric) suma_ceni 39 37 from beautycenter as bc 40 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id 41 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id 42 left join termini as t on v.user_id = t.user_id_vraboten 43 left join rezervacija as r on t.termin_id = r.termin_id 44 left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez 45 left join uslugi as u on ru.usluga_id = u.usluga_id 38 join vraboteni as v on bc.salon_id = v.salon_id 39 join termini as t on v.user_id = t.user_id_vraboten 40 join rezervacija as r on t.termin_id = r.termin_id 41 join rezervacija_uslugi as ru on r.br_rez = ru.br_rez 42 join uslugi as u on ru.usluga_id = u.usluga_id 46 43 group by bc.salon_id,u.usluga_id,u.cena 47 44 ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id … … 53 50 select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric) suma_ceni 54 51 from beautycenter as bc 55 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id 56 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id 57 left join termini as t on v.user_id = t.user_id_vraboten 58 left join rezervacija as r on t.termin_id = r.termin_id 59 left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez 60 left join uslugi as u on ru.usluga_id = u.usluga_id 52 join vraboteni as v on bc.salon_id = v.salon_id 53 join termini as t on v.user_id = t.user_id_vraboten 54 join rezervacija as r on t.termin_id = r.termin_id 55 join rezervacija_uslugi as ru on r.br_rez = ru.br_rez 56 join uslugi as u on ru.usluga_id = u.usluga_id 61 57 group by bc.salon_id,u.usluga_id,u.cena 62 58 ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id … … 88 84 89 85 {{{ 90 select b.salon_id,v b.user_id_vraboten, v.first_name, v.prezime, (current_date - vb.raboti_od) as vremetraenje86 select b.salon_id,v.user_id, v.first_name, v.prezime, (current_date - v.raboti_od) as vremetraenje 91 87 from beautycenter as b 92 left join vraboteni_beautycenter as vb on b.salon_id = vb.salon_id 93 left join vraboteni as v on vb.user_id_vraboten = v.user_id 94 where vb.raboti_do is null 88 left join vraboteni as v on b.salon_id = v.salon_id 89 where v.raboti_do is null -- and b.salon_id = 'P1106' 95 90 order by vremetraenje desc 96 91 }}} … … 136 131 count(CASE WHEN r.uplata_id notnull THEN 0 END) as uplata_online 137 132 from klienti as k 138 leftjoin rezervacija as r on k.user_id = r.user_id_klient133 join rezervacija as r on k.user_id = r.user_id_klient 139 134 group by 1,2,3 140 135 order by br_reztermini desc; … … 149 144 select v.user_id , v.first_name,v.prezime, count(r.br_rez) as br_rezervacii 150 145 from vraboteni as v 151 leftjoin termini as t on v.user_id = t.user_id_vraboten152 leftjoin rezervacija as r on t.termin_id = r.termin_id153 leftjoin rezervacija_uslugi as ru on r.br_rez = ru.br_rez146 join termini as t on v.user_id = t.user_id_vraboten 147 join rezervacija as r on t.termin_id = r.termin_id 148 join rezervacija_uslugi as ru on r.br_rez = ru.br_rez 154 149 where extract (year from t.pocetok) = extract (year from now() - interval '1 year') 155 150 group by v.user_id … … 171 166 172 167 from beautycenter as bc 173 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id174 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id175 left join termini as t on v.user_id = t.user_id_vraboten176 left join rezervacija as r on t.termin_id = r.termin_id177 178 168 left join( 179 select bc.salon_id, extract (quarter from t.pocetok) as kvartal, count(r.br_rez) as broj_rezervacii169 select bc.salon_id, extract (quarter from t.pocetok) as kvartal, count(r.br_rez) as broj_rezervacii 180 170 from beautycenter as bc 181 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id 182 left join vraboteni as v on v_bc.user_id_vraboten = v.user_id 183 left join termini as t on v.user_id = t.user_id_vraboten 184 left join rezervacija as r on t.termin_id = r.termin_id 171 join vraboteni as v on v.salon_id = bc.salon_id 172 join termini as t on v.user_id = t.user_id_vraboten 173 join rezervacija as r on t.termin_id = r.termin_id 185 174 where extract (year from t.pocetok) = extract (year from current_date) 186 175 group by 1,2 187 176 ) as broj_na_rezervacii on bc.salon_id = broj_na_rezervacii.salon_id 177 188 178 }}} 189 179
