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

Извештај за сите кина, на месечно ниво, колку карти се пордадени и вкупниот профит во предходната година

select c.id_cinema,c.name,
coalesce(prv_mesec.karti, 0) as prvi_mesec,
coalesce(vtor_mesec.karti, 0) as vtori_mesec,
coalesce(tret_mesec.karti, 0) as tretti_mesec,
coalesce(cetvrt_mesec.karti,0) as cetvrti_mesec,
coalesce(pet_mesec.karti,0) as petti_mesec,
coalesce(sest_mesec.karti,0) as sesti_mesec,
coalesce(sedum_mesec.karti, 0)  as sedmi_mesec,
coalesce(osum_mesec.karti,0)  as osmi_mesec,
coalesce(devet_mesec.karti,0) as devetti_mesec,
coalesce(deset_mesec.karti, 0) as desetti_mesec,
coalesce(edinaeset_mesec.karti11,0) as edinaesti_mesec,
coalesce(dvanaeset_mesec.karti,0) as dvanaesti_mesec,
sum(t.price) as vkupen_promet
from cinemas c 
join projection_rooms pr on pr.id_cinema=c.id_cinema
join projection_is_played_in_room pipir on pipir.id_room =pr.id_room 
join projections p on p.id_projection = pipir.id_projection 
join tickets t on t.id_projection=p.id_projection 
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=1
        group by c2.id_cinema 
        
) as prv_mesec on prv_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=2
        group by c2.id_cinema 
        
) as vtor_mesec on vtor_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=3
        group by c2.id_cinema 
        
) as tret_mesec on tret_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=4
        group by c2.id_cinema 
        
) as cetvrt_mesec on cetvrt_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=5
        group by c2.id_cinema 
        
) as pet_mesec on pet_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=6
        group by c2.id_cinema 
        
) as sest_mesec on sest_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=7
        group by c2.id_cinema 
        
) as sedum_mesec on sedum_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=8
        group by c2.id_cinema 
        
) as osum_mesec on osum_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=9
        group by c2.id_cinema 
        
) as devet_mesec on devet_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=10
        group by c2.id_cinema 
        
) as deset_mesec on deset_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti11,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=11
        group by c2.id_cinema 
        
) as edinaeset_mesec on edinaeset_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=12
        group by c2.id_cinema 
        
) as dvanaeset_mesec on dvanaeset_mesec.id=c.id_cinema 
where extract(year from t.date_reserved)=extract(year from now()-interval'1 year')
group by c.id_cinema,prv_mesec.karti,vtor_mesec.karti,tret_mesec.karti,cetvrt_mesec.karti,
pet_mesec.karti,sest_mesec.karti,sedum_mesec.karti,osum_mesec.karti,devet_mesec.karti,deset_mesec.karti,
edinaeset_mesec.karti11,dvanaeset_mesec.karti
order by c.id_cinema

Извештај за секое кино, кој филм има направено најмногу профит во последните 3 месеци

select c.id_cinema,c.name,max(sum_cena.sum1) as m
from cinemas c 
join cinema_plays_film cpf on cpf.id_cinema=c.id_cinema 
join films f on f.id_film=cpf.id_film 
join projections p on p.id_film=f.id_film
join tickets t on t.id_projection=p.id_projection and t.date_reserved between now()-interval'3 months' and now()
join (
select distinct(c2.id_cinema) as id,f2.id_film as film,sum(t1.price) as sum1 from tickets t1
join projections p2 on p2.id_projection=t1.id_projection
join films f2 on f2.id_film = p2.id_film 
join cinema_plays_film cpf2 on cpf2.id_film=f2.id_film 
join cinemas c2 on c2.id_cinema=cpf2.id_cinema 
where t1.date_reserved between now()-interval'3 months' and now()
group by c2.id_cinema,f2.id_film 
) as sum_cena on sum_cena.id=c.id_cinema 
group by c.id_cinema
order by c.id_cinema 

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

Се пресметуваат колку количина од деловите имало во завршените нарачки во последните 3 месеци и се додава моменталната количина во нарачки што се уште не

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