/*
podaj dane osob (nazwisko, imie, klasa, miejscowość, ilość nb) które nie mieszkają w Lubartowie lub w Lublinie, uczeszczaja do klasy 3, maja  min 3 nb w roku 2022, uwzglednij max 3 osoby z każdej klasy z najwieksza iloscia nieobecnosci
*/

with KA as(
select top 3 WITH ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3A'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob desc
)


, KB AS(
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3B'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob desc
), KC AS(
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3C'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
ORDER BY nieob DESC
),KD AS(
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3D'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
), KE AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3E'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
),KF AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3F'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
), KG AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3G'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
),KH AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3H'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
), KT AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3T'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
),KZ AS (
select top 3 with ties
 u.Imie
,u.Nazwisko
,k.Nazwa
,a.Miejscowosc
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia join Klasy as k on k.IdKlasy=u.IdKlasy join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
where u.iducznia not in(
select
u.IdUcznia
from Uczniowie as u join Adresy as a on u.IdUcznia=a.IdUcznia
where a.Miejscowosc in ('Lublin', 'Lubartów')
)AND u.IdUcznia in(
select
u.iducznia
from Uczniowie as u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
and n.DataNieobecnosci between '20220101' and '20221231'
group by u.iducznia
having COUNT(*)>=3
)and u.iducznia in (
select
u.IdUcznia
from Uczniowie as u join Klasy as k on k.IdKlasy=u.IdKlasy
where k.Nazwa like '3Z'
)
group by  u.Imie,u.Nazwisko,k.Nazwa,a.Miejscowosc
order by nieob DESC
), kon as(
select 
*
from KA
UNION
select 
*
from KB
UNION
select 
*
from KC
UNION
select 
*
from KD
UNION
select 
*
from KE
UNION
select 
*
from KF
UNION
select 
*
from KG
UNION
select 
*
from KH
UNION
select 
*
from KT
UNION
select 
*
from KZ
)
select
*
from kon
order by kon.Nazwa