USE Poliklinika;
GO
----------------
SELECT * FROM Navstevy ORDER BY idL
--0. Kolko zarobili jednotlivi lekari? (Opakovanie)
SELECT N.idL, krstne, SUM(N.poplatok) as 'Spolu' FROM
Navstevy N JOIN Lekari L ON N.idL = L.idL GROUP BY N.idL, krstne
-- Kvoli GROUP BY som prisiel o jednotlive zaznamy navstev.  


--1. Co ak by som chcel vidiet� vsetky navstevy a v novom stlpci vidiet� aj kolko zarobil lekar? ODPOVED Window funkcie.
--OVER() klauzula definuje particiovanie a poradie riadkov predtym ako sa aplikuje asociovana window funkcia, napr. nejaka agregacna.
--PARTITION BY definuje okno na ktorom sa ma window funkcia aplikova�, treba da� stlpec alebo viacero stlpcov PARTITION BY stlpec1...

SELECT N.idL, krstne, SUM(N.poplatok) OVER(PARTITION BY N.idL) as 'Spolu zarobil' FROM
Navstevy N JOIN Lekari L ON N.idL = L.idL

--2. Podobne nech je zobrazeny pocet navstev, ktory lekar mal.
SELECT N.idL, krstne, COUNT(*) OVER(PARTITION BY N.idL) as 'pocet navstev' FROM
Navstevy N JOIN Lekari L ON N.idL = L.idL

--3.  Kolko roznych pacientov navstivilo kazdeho lekara?  
-- ZLA ODPOVED (lebo zapocitalo rovnake idP viac krat lebo ti pacienti boli viac krat na navsteve):
SELECT idL, COUNT(idP) as 'pocet pacientov' FROM Navstevy  GROUP BY idL

--DOBRA ODPOVED:
SELECT idL, COUNT(DISTINCT idP) as 'pocet pacientov' FROM Navstevy GROUP BY idL


--3. Skuste pomocou Window funkcie
--POZOR POZOR: S window funkciami DISTINCT V COUNT NIE JE PODPOROVANE!!!
-- Msg 10759: Use of DISTINCT is not allowed with the OVER clause.
-- obist sa to da zlozitymi konstruktmi, ktore si nebudeme ukazovat
SELECT idL, COUNT(DISTINCT idP) OVER(PARTITION BY idL) as 'pocet navstev' FROM Navstevy


--4. Ak dane dva dopyty vratia vsetky rozne mozne pocty navstev polikliniky jednotlivymi  
--pacientami, bez rozdielu na to, co vystupuje v COUNTe:
	--SELECT distinct COUNT(*) OVER(PARTITION BY idP) pocet FROM Navstevy N
	--SELECT distinct COUNT(idN) OVER(PARTITION BY idP) pocet FROM Navstevy N

--Sformulujte, co vratia nasledujuce 4 dopyty:
SELECT distinct COUNT(*) OVER(PARTITION BY krstne) pocet FROM
Navstevy N JOIN Pacienti P ON N.idP = P.idP
-- spravi okna podla krstneho mena pacienta
-- spocita pre kazde meno pocet riadkov
-- distinct sa aplikuje na pocty riadkov, teda ake vselijake pocty mien sa tam vyskytuju

SELECT krstne, COUNT(*) OVER(PARTITION BY krstne) pocet FROM
Navstevy N JOIN Pacienti P ON N.idP = P.idP
-- spravi okna podla krstneho mena pacienta
-- ku kazdemu krstnemu menu pre navstevu zobrazi pocet navstev pre meno

SELECT distinct krstne, COUNT(*) OVER(PARTITION BY krstne) pocet FROM
Navstevy N JOIN Pacienti P ON N.idP = P.idP
-- to co v predoslom, len zmiznu duplikaty mien
-- teda prideme o riadky navstev, co v podstate velmi nechceme pri window funkcii(lebo na to je dobra WF aby sme neprisli o riadky)


SELECT N.idP, COUNT(*) OVER(PARTITION BY N.idP) pocet FROM
Navstevy N JOIN Pacienti P ON N.idP = P.idP
-- Posledne dva dopyty zistia, ktory pacient absolvoval kolko navstev
-- (v DB Poliklinika kazdy pacient ma ine krstne, ale v inej poliklinike to tak asi nebude platit, preto radsej cez idP )


GO

--5. a) Pre kazdeho pacienta zobrazte jeho krstne meno, pocet navstev u lekara a plat pacienta
SELECT krstne, mesPrijem, COUNT(krstne) pocet FROM
	Navstevy N JOIN Pacienti P ON N.idP = P.idP	
	GROUP BY krstne, mesPrijem
	ORDER BY krstne;
--5. b) Spravte to iste pomocou window funkcie
SELECT DISTINCT krstne, mesPrijem, COUNT(krstne) OVER(PARTITION BY krstne) pocet FROM
	Navstevy N JOIN Pacienti P ON N.idP = P.idP
	ORDER BY krstne;


--6. a) Zobrazte meno pacienta, specializaciu lekara a den navstevy, pre kazdu navstevu.
SELECT P.krstne pacient, den, L.spec FROM Navstevy N JOIN Pacienti P ON N.idP = P.idP
	JOIN Lekari L ON N.idL = L.idL
	ORDER BY P.krstne;


--6. b) Zobrazte aj den predoslej navstevy a specializaciu predoslej navstevy.
SELECT pacient,spec, den,
		(SELECT TOP(1) spec FROM Navstevy N 
			JOIN Lekari L ON N.idL = L.idL
			JOIN Pacienti P ON P.idP = N.idP
			WHERE P.krstne = pacient AND
			N.den < t1.den
			ORDER BY den DESC
		) as 'predosla spec',
		(SELECT TOP(1) den FROM Navstevy N 
			JOIN Lekari L ON N.idL = L.idL
			JOIN Pacienti P ON P.idP = N.idP
			WHERE P.krstne = pacient AND
			N.den < t1.den
			ORDER BY den DESC
		) as 'predosla navsteva'
	FROM
	(SELECT P.krstne pacient, den, L.spec FROM Navstevy N JOIN Pacienti P ON N.idP = P.idP
	JOIN Lekari L ON N.idL = L.idL) 
	AS t1
	ORDER BY pacient;

--6. c) Spravte to iste ako b) pomocou window funkcie LAG. Porovnajte zlozitos� dopytu a cas vykonania oboch dopytov
SELECT P.krstne pacient, L.spec, den,
	LAG(L.spec) OVER (PARTITION BY P.krstne ORDER BY den) AS 'predosla spec',
	LAG(den) OVER (PARTITION BY P.krstne ORDER BY den) AS 'predosla navsteva'
FROM Navstevy N 
	JOIN Pacienti P ON N.idP = P.idP
	JOIN Lekari L ON N.idL = L.idL
	ORDER BY pacient;

SET statistics time on;
--Tu vlozit� prvy dopyt
--Tu vlozit� druhy dop
SET statistics time off;
--Vysledky statistik vidno v karte Messages, vedla karty Results