Rapasoft.eu

/*
Uvazujme polikliniku. Najdite id takych pacientov, ich krstne mena,
mesacne prijmy a id lekara, ktori u lekara nezaplatili ziaden poplatok.
Vratte aj priemernu sumu penazi, ktora vychadza na jedneho prislusnika s tym, 
ze v pripade ak pacientov mes. prijem >= 20 000, tak ma 4 clennu rodinu,
ak je jeho prijem >= 10 000 ale mensi ako 20 000,
tak ma 2-clennu rodinu, inak je to student (staci vratit jeho mes. prijem)
*/
use poliklinika
go

select p.idp,p.krstne,p.mesPrijem,l.idl Lekar,priemNaClenaRodiny = 
case 
	when mesPrijem >=20000 then mesPrijem/4
	when mesPrijem >=10000 and mesPrijem<20000 then mesPrijem/2
	else mesPrijem
end
from navstevy n
	join pacienti p on p.idp=n.idp
	join lekari l on l.idl=n.idl
where poplatok is null

/*
Vedenie polikliniky potrebuje vytvoriť finančnú analýzu tržieb za posledný rok. Chcú zistiť, ktorý mesiac v roku je
 najvyťaženejší a podľa toho určovať poplatky za vyšetrenia. Napriek tomu, že bola prevádzka spustená len v máji 
chcú mať v tabuľke zvlášť riadok pre každý mesiac roka. Takže prvý stĺpec bude tvoriť poradie mesiaca v roku, v 
ďalších bude počet ľudí, ktorí v danom mesiaci navštívili polikliniku, zisk v danom mesiaci, percentuálny podiel zisku v 
danom mesiaci ku celkovému zisku a nakoniec celkový zisk. Keďže je to len predbežný výstup, ktorý sa bude neskôr
 formátovať, sú prípustné aj NULL hodnoty.
*/

use poliklinika
go

create table mesiace (id int);

declare @id int;
set @id = 1;

while @id <= 12 
begin
	insert into mesiace values (@id);
	set @id = @id + 1;
end

select 
	m.id 'Mesiac',
	count(idP) 'Pocet ludi', 
	sum(poplatok) 'Zisk v mesiaci', 
	cast(
			cast(sum(poplatok) as float)
		/
			cast((select sum(poplatok) from navstevy) as float)*100
		as decimal) '%',
	(select sum(poplatok) from navstevy) 'Celkovy zisk' 
from navstevy n1
	right outer join mesiace m on m.id = month(n1.den)
group by m.id

drop table mesiace

/*
Najdite informacie o lekarovi, ktory v mesiaci maj osetril pacienta,
ktory mal najvyssi mesacny prijem spomedzi vsetkych pacientov,
ktori boli v maji u lekara.
*/
select l.* from lekari l
	join navstevy n on n.idl = l.idl
	join pacienti p on p.idp = n.idp
where month(n.den) = 5 and p.mesPrijem = (
select max(mesPrijem) from pacienti p1
	join navstevy n1 on n1.idp = p1.idp
where month(n1.den)=5);

/*
Vratte mena pacientov, ktori navstivili lekara v parny mesiac a zaplatili
poplatok mensi ako 350.
*/
select distinct p.krstne from pacienti p
	join navstevy n on p.idp = n.idp
where month(n.den)%2 = 0 and poplatok < 350
/* 
spanielske veci
*/