| Version 42 (modified by , 3 years ago) ( diff ) |
|---|
Напредни извештаи од базата (SQL и складирани процедури)
За секое прифатилиште, идентификациски број, име, електронска пошта, телефонски број, име на организацијата во која припаѓа тоа прифатилиште и просечен број на посвојувачи кои се верификувани од страна на вработени во тоа прифатилиште.
select
q2.id_shelter,
q2.name_shelter,
q2.email_shelter,
q2.telephone_shelter,
org.name_organisation as name_of_organisation,
q2.average_verified_adopters
from
(
select
shelter.id_shelter,
shelter.name_shelter,
shelter.email_shelter,
shelter.telephone_shelter,
avg(adopters_verified) as average_verified_adopters
from
shelter
left join
(
select
employee.id_user as employee_id,
employee.id_shelter as shelter_id,
count(distinct adopter.id_user) as adopters_verified
from
employee
join adopter
on adopter.verified_by_employee = employee.id_user
group by
employee.id_user
) as q1
on q1.shelter_id = shelter.id_shelter
group by
shelter.id_shelter
) as q2
left join shelter as sh
on sh.id_shelter = q2.id_shelter
left join organisation as org
on org.id_organisation = sh.id_organisation
order by
q2.average_verified_adopters desc
За секое посвојување, идентификациски број, почетен и краен датум и статус. За посвојувачот се прикажуваат името, електронската пошта и телефонскиот број. За посвојувањето се пресметуваат број на миленици, број на различни видови (мачки, кучиња, птици) и севкупен број на терапии и интервенции потребни на сите миленици./
select
q2.id_adoption as id,
q2.start_date,
q2.end_date_foster,
q2.status,
user_table.name_user as adopter_name,
user_table.email_user as adopter_email,
user_table.telephone_user as adopter_phone,
q2.number_of_pets,
q2.number_of_species,
q2.total_therapies,
q2.total_interventions
from
(
select
adoption.id_adoption,
adoption.start_date,
adoption.end_date_foster,
case
when adoption.approved = false then 'under review'
when adoption.approved = true then 'approved'
end
as status,
count(distinct q1.id_pet) as number_of_pets,
count(distinct q1.species) as number_of_species,
sum(q1.therapies) as total_therapies,
sum(q1.interventions) as total_interventions
from
(
select
pet.id_pet,
pet.name_pet,
pet.id_adoption,
pet.species,
count(distinct pnt.id_therapy) as therapies,
count(distinct description) as interventions
from
pet
left join
pet_needs_therapy pnt
on pnt.id_pet = pet.id_pet
left join
pet_needs_intervention_in_vet_clinic pni
on pni.id_pet = pet.id_pet
group by
pet.id_pet
) as q1
right join
adoption
on q1.id_adoption = adoption.id_adoption
group by
adoption.id_adoption
) as q2
left join
adoption
on adoption.id_adoption = q2.id_adoption
left join
adopter
on adopter.id_user = adoption.id_adopter
left join
user_table
on user_table.id_user = adopter.id_user
Приказ на секоја храна која ја јадат миленици со ниска физичка активност кои примаат терапии. За секоја од нив да се прикаже и вкупниот број такви миленици.
select
food.name_food as food_name,
sum(q2.has_therapies) as total_pets
from
(
select
q1.pet_id,
q1.has_therapies
from
(
select
pet.id_pet as pet_id,
count(therapy.id_therapy) as has_therapies
from
pet
join
pet_needs_therapy pnt
on pnt.id_pet = pet.id_pet
join
therapy
on therapy.id_therapy = pnt.id_therapy
group by
pet.id_pet
) as q1
join personal_profile
on q1.pet_id = personal_profile.id_pet
where
personal_profile.physical_activity = 0
) as q2
join pet_preferably_eats_food ppef
on ppef.id_pet = q2.pet_id
join food
on food.id_food = ppef.id_food
group by
food.id_food
order by
total_pets
За секој посвојувач, вкупен број на миленици од прифатилиште и вкупен број на миленици огласени од граѓани. За посвојувачите се прикажуваат име презиме и контакт информации (електронска пошта и телефон).
select
q3.id_user,
user_table.name_user,
user_table.email_user,
user_table.telephone_user,
q3.pets_from_surendee,
q3.pets_from_shelter
from
(
select
adopter.id_user,
sum(q1.surendee_count) as pets_from_surendee,
sUm(q2.shelter_count) as pets_from_shelter
from
adopter
left join
(
select
adoption.id_adoption,
adoption.id_adopter,
count(surendee.id_user) as surendee_count
from
adoption
left join
pet
on pet.id_adoption = adoption.id_adoption
left join
post
on post.id_pet = pet.id_pet
left join
surendee
on surendee.id_user = post.id_surendee
group by
adoption.id_adoption
) as q1
on q1.id_adopter = adopter.id_user
left join
(
select
adoption.id_adoption,
adoption.id_adopter,
count(employee.id_user) as shelter_count
from
adoption
left join
pet
on pet.id_adoption = adoption.id_adoption
left join
post
on post.id_pet = pet.id_pet
left join
employee
on employee.id_user = post.id_employee
group by
adoption.id_adoption
) as q2
on q2.id_adopter = adopter.id_user
group by
adopter.id_user
order by
adopter.id_user
) as q3
left join
user_table
on user_table.id_user = q3.id_user
Приказ за секоја година, тип на милениче со најголем број на посвојувања
select q2.god as godina, q2.maximum as max,
case
when q3.species = 0 then 'cat'
when q3.species = 1 then 'dog'
when q3.species = 2 then 'bird'
end as species
from
( select q1.godina as god, max(broj_posvojuvanja) as maximum from
(
select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
from adoption a
join pet p2 on a.id_adoption = p2.id_adoption
where (end_date_foster is null) and (a.approved is true)
group by godina, p2.species
order by godina
) q1
group by godina
)q2
join
(
select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
from adoption a
join pet p2 on a.id_adoption = p2.id_adoption
where (end_date_foster is null) and (a.approved is true)
group by godina, p2.species
order by godina
) q3
on q2.god = q3.godina and q2.maximum = q3.broj_posvojuvanja
Извештаи за организации - број на вработени, број на верифицирани посвојувачи од страна на вработени во таа организација и вкупен број на посвојувања направени од посвојувачи кои биле верифицирани од страна на вработени во таа организација
select
o.name_organisation,
sum(q3.number_of_employees) as number_of_employees,
sum(q2.verified_adopters) as verified_adopters,
sum (q2.number_of_adoptions_under_employee) as number_of_adoptions_under_employee
from shelter s
join
(
select
e.id_user,
e.id_shelter,
count(distinct q1.id_user) as verified_adopters,
sum(q1.number_of_adoptions) as number_of_adoptions_under_employee
from
(
select
a.id_user,
a.verified_by_employee,
count(distinct a2.id_adoption) as number_of_adoptions
from adopter a
join adoption a2 on a.id_user = a2.id_adopter
group by a.id_user
) q1
join employee e on e.id_user = q1.verified_by_employee
group by e.id_user
order by e.id_user asc
) q2 on s.id_shelter = q2.id_shelter
join (
select
s.id_shelter,
count(distinct e2.id_user) as number_of_employees
from shelter s
join employee e2 on e2.id_shelter = s.id_shelter
group by s.id_shelter
) q3 on q3.id_shelter = s.id_shelter
join organisation o on s.id_organisation = o.id_organisation
group by o.id_organisation
order by o.name_organisation
За секој посвојувач, број на посвоени миленичиња од секој вид (мачки, кучиња, птици) и вкупен број посвоени миленици. Табелата е подредена во опаѓачки редослед според бројот на посвојувања т.ш. посвојувачот со највеќе посвојувања е во првиот ред.
select
ut.name_user as posvojuvac,
count(distinct pm.id_pet) as broj_posvoeni_macki,
count(distinct pd.id_pet) as broj_posvoeni_kucinja,
count(distinct pp.id_pet) as broj_posvoeni_ptici,
count(distinct p.id_pet) as vkupno_posvoeni_milenici
from
adopter a
join
user_table ut on a.id_user = ut.id_user
left join
adoption ad on ad.id_adopter = a.id_user
left join
pet pm on pm.id_adoption = ad.id_adoption
and
pm.species = 0
left join
pet pd on pd.id_adoption = ad.id_adoption
and
pd.species = 1
left join
pet pp on pp.id_adoption = ad.id_adoption
and
pp.species = 2
left join
pet p on p.id_adoption = ad.id_adoption
group by
a.id_user, ut.name_user
order by
vkupno_posvoeni_milenici desc
Направени донации во секоја организација
select o.name_organisation,
count (d.id_user) as broj_donacii
from organisation o
left join donor_donates_to_organisation ddto on o.id_organisation = ddto.id_organisation
left join donor d on ddto.id_user = d.id_user
group by o.id_organisation
order by o.id_organisation
Note:
See TracWiki
for help on using the wiki.
