Напредни извештаи од базата (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.user_id) as broj_vraboteni
		 from beautycenter as bc 
 		join vraboteni as v on bc.salon_id = v.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 
 		 join vraboteni as v on bc.salon_id = v.salon_id
 		  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 
 		  join vraboteni as v on bc.salon_id = v.salon_id
 		  join termini as t on v.user_id = t.user_id_vraboten
 		  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 
 		   join vraboteni as v on bc.salon_id = v.salon_id
 		   join termini as t on v.user_id = t.user_id_vraboten
 		   join rezervacija as r on t.termin_id = r.termin_id
 		   join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
 		   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 
 		      join vraboteni as v on bc.salon_id = v.salon_id
 		      join termini as t on v.user_id = t.user_id_vraboten
 		      join rezervacija as r on t.termin_id = r.termin_id
 		      join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
 		      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,v.user_id, v.first_name, v.prezime, (current_date - v.raboti_od) as vremetraenje
from beautycenter as b
left join vraboteni as v on b.salon_id = v.salon_id
where v.raboti_do is null -- and b.salon_id = 'P1106'
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
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
join termini as t on v.user_id = t.user_id_vraboten 
join rezervacija as r on t.termin_id = r.termin_id 
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(
select  bc.salon_id, extract (quarter from t.pocetok) as kvartal, count(r.br_rez) as broj_rezervacii
  from beautycenter as bc 
  	join vraboteni as v on v.salon_id = bc.salon_id
  	join termini as t on v.user_id = t.user_id_vraboten
    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