Напредни извештаи од базата (SQL и складирани процедури)

Извештај за секој салон со бројот на вработени, термини,резервации и вкупната заработувачка

select distinct bc.salon_id , br_vraboteni.broj_vraboteni, br_termini.broj_termini, br_rezervacii.broj_rez, zarabotuvacka.vkupno
       
  from beautycenter as bc 

left join 
(       select bc.salon_id, count(v_bc.user_id_vraboten) as broj_vraboteni
		 from beautycenter as bc 
 		 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
 		 group by bc.salon_id
  ) as br_vraboteni on bc.salon_id = br_vraboteni.salon_id
  
left join 
(       select bc.salon_id, count(t.termin_id) as broj_termini
		 from beautycenter as bc 
 		 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
 		  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
 		  left join termini as t on v.user_id = t.user_id_vraboten
 		 group by bc.salon_id
  ) as br_termini on bc.salon_id = br_termini.salon_id
 
  left join 
(       select bc.salon_id, count(r.br_rez) as broj_rez
		 from beautycenter as bc 
 		 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
 		  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
 		  left join termini as t on v.user_id = t.user_id_vraboten
 		  left join rezervacija as r on t.termin_id = r.termin_id
 		 group by bc.salon_id
  ) as br_rezervacii on bc.salon_id = br_rezervacii.salon_id
     
  left join 
(       select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric)  suma_ceni
		 from beautycenter as bc 
 		 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
 		  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
 		  left join termini as t on v.user_id = t.user_id_vraboten
 		  left join rezervacija as r on t.termin_id = r.termin_id
 		  left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
 		  left join uslugi as u on ru.usluga_id = u.usluga_id
 		 group by bc.salon_id,u.usluga_id,u.cena
  ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id

  left join 
(       select bc.salon_id, sum(suma_uslugi.suma_ceni) vkupno
		 from beautycenter as bc 
 		  left join (
 		     select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric)  suma_ceni
		     from beautycenter as bc 
 		     left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
 		     left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
 		     left join termini as t on v.user_id = t.user_id_vraboten
 		     left join rezervacija as r on t.termin_id = r.termin_id
 		     left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
 		     left join uslugi as u on ru.usluga_id = u.usluga_id
 		    group by bc.salon_id,u.usluga_id,u.cena
 		  ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id
 		 group by bc.salon_id
  ) as zarabotuvacka on bc.salon_id = zarabotuvacka.salon_id

Број на вработени за дадена дејност

create view brVraboteni_zaUslugi as 
 select  u.usluga_id, u.dejnost, count(v.user_id) as br_vraboteni
 from vraboteni_uslugi as vu
 join uslugi as u on vu.usluga_id = u.usluga_id
 join vraboteni as v on vu.user_id_vraboten = v.user_id
 group by 1,2;

 select u_vbr.usluga_id , u_vbr.dejnost, u_vbr.br_vraboteni
 from brVraboteni_zaUslugi u_vbr
 where u_vbr.usluga_id = 'T42lash'

Извештај за секој салон и лојалност на вработените во истиот според најдолго времетраење на работниот однос од почетокот до тој момент

select b.salon_id,vb.user_id_vraboten, v.first_name, v.prezime, (current_date - vb.raboti_od) as vremetraenje
from beautycenter as b
left join vraboteni_beautycenter as vb on b.salon_id = vb.salon_id
left join vraboteni as v on vb.user_id_vraboten = v.user_id
where vb.raboti_do is null
order by vremetraenje desc

Листа на користени услуги со вкупниот број на резервации и просечна оцена како и нивната единечна цена и вкупната заработка по услуга

select u.usluga_id,u.dejnost,
(
  select  cast(u.cena as numeric) 
) cena_za_usluga,
coalesce((
  select count(usluga_id) broj_rezervacii
  from rezervacija_uslugi  
  group by usluga_id
  having usluga_id = u.usluga_id
) ,0) broj_rezervacii,

( 
   select count(usluga_id)*cast(u.cena as numeric)  suma_ceni
   from rezervacija_uslugi  
   group by usluga_id
   having usluga_id = u.usluga_id
   
)   suma_ceni_usluga,
(
select avg(o.vrednost)
 from ocena as o
 group by o.usluga_id
 having o.usluga_id = u.usluga_id
) ocena
from uslugi as u

Приказ на клиенти со број на резервирани термини во опаѓачки редослед и број на реализирани начини на уплата

select k.user_id, k.ime , k.prezime , count(r.br_rez) as br_rezTermini , count(CASE WHEN r.uplata_id isnull THEN 0 END) as uplata_voGotovo,
count(CASE WHEN r.uplata_id notnull THEN 0 END) as uplata_online
from klienti as k
left join rezervacija as r on k.user_id = r.user_id_klient
group by 1,2,3
order by br_reztermini desc;

Вработен кој имал најмногу резервирани термини кои ги извршил во минатата година

create view vkbr_rez_vraboteni as
select v.user_id , v.first_name,v.prezime, count(r.br_rez) as br_rezervacii
from vraboteni as v
left join termini as t on v.user_id = t.user_id_vraboten 
left join rezervacija as r on t.termin_id = r.termin_id 
left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
where extract (year from t.pocetok) = extract (year from now() - interval '1 year')
group by v.user_id
 
 
 select br.user_id , br.first_name , br.prezime , br.br_rezervacii
 from vkbr_rez_vraboteni br
 where br.br_rezervacii = (select max(br_rezervacii) from vkbr_rez_vraboteni)

Извештај за секој салон со број на резервации на годишно ниво (моменталната година) по квартали

select distinct bc.salon_id ,
        case when broj_na_rezervacii.kvartal = 1 then broj_na_rezervacii.broj_rezervacii else 0 end as Kvartal1_broj_rezervacii,
        case when broj_na_rezervacii.kvartal = 2 then broj_na_rezervacii.broj_rezervacii else 0 end as Kvartal2_broj_rezervacii,
        case when broj_na_rezervacii.kvartal = 3 then broj_na_rezervacii.broj_rezervacii else 0 end as Kvartal3_broj_rezervacii,
        case when broj_na_rezervacii.kvartal = 4 then broj_na_rezervacii.broj_rezervacii else 0 end as Kvartal4_broj_rezervacii
  
  from beautycenter as bc 
  left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
  left join termini as t on v.user_id = t.user_id_vraboten
  left join rezervacija as r on t.termin_id = r.termin_id

left join(
select bc.salon_id, extract (quarter from t.pocetok) as kvartal, count(r.br_rez) as broj_rezervacii
  from beautycenter as bc 
  left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
  left join termini as t on v.user_id = t.user_id_vraboten
  left join rezervacija as r on t.termin_id = r.termin_id
 where extract (year from t.pocetok) = extract (year from current_date)
     group by 1,2
) as broj_na_rezervacii   on bc.salon_id = broj_na_rezervacii.salon_id