SELECT Z.SIFZAP, Z.IME, Z.PREZIME, Z.MLB, PROF.NAZIVPROF
FROM ZAPOSLENI Z LEFT OUTER JOIN OBRAZOVNI_PROFIL PROF
ON Z.SIFPROF=PROF.SIFPROF
WHERE PROF.STEPENSTRUCNESPREME IN ('IV','V','VI','VII') AND
EXTRACT( YEAR FROM (CURRENT_DATE) - EXTRACT(YEAR FTOM CAST( '1'|| SUBSTRING(Z.MLB FROM 5 FOR 3) ||'-' SUBSTRING(Z.MLB FROM 3 FOR 2) || '-' SUBSTRING(Z.MLB FROM 1 FOR 2)) AS DATE)) -
CASE Z.POL
WHEN 'M' THEN 65
ELSE 58
END IN (-1,0);

SELECT RM.SIFRARM, RM.NAZIVRM, PROF.NAZIVPROF
FROM RADNO_MESTO RM LEFT OUTER JOIN PROFIL PROF 
ON RM.SIFRARM=PROF.SIFRARAM
LEFT OUTER JOIN ANGAZOVANJE A
ON RM.SIFRARM=A.SIFRARM
GROUP BY RM.SIFRARM
HAVING COUNT(A.SIFRAZAP) =
MAX(SELECT COUNT(A1.SIFRAZAP) FROM ANGAZOVANJE A1 GROUP BY A1.SIFRARM);

//POD PRETPOSTAVKOM DA ZAPOSLENI MOZE DA BUDE GODISNJE NAJVISE JEDNOM ANGAZOVAN, JER AKO OVA PRETPOSTAVKA NE VAZI, ZADATAK POSTAJE PAKAO
CREATE VIEW STATISTIKA(SIFRA_PROFILA, NAZIV_PROFILA, BR_ZAP_2004, BR_ZAP_2005, TREND, PROCENTUALNO)
AS SELECT PROF.SIFRA, PROF.NAZIVPROF,
SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2004' THEN 1 ELSE 0 END),
SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2005' THEN 1 ELSE 0 END),
CASE WHEN 
SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2004' THEN 1 ELSE 0 END)=SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2005' THEN 1 ELSE 0 END)
THEN 'BEZ_PROMENA'
WHEN 
SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2004' THEN 1 ELSE 0 END)<SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2005' THEN 1 ELSE 0 END)
THEN 'RAST'
ELSE 'PAD'
END,
(SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2005' THEN 1 ELSE 0 END)-SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2004' THEN 1 ELSE 0 END) )/SUM(CASE EXTRACT(YEAR FROM A.DATOD) WHEN '2004' THEN 1 ELSE 0 END)
FROM OBRAZOVNI_PROFIL PROF LEFT OUTTER JOIN RADNO_MESTO RM
ON PROF.SIFRAPROF=RM.SIFRAPROF
LEFT OUTER JOIN ANGAZOVANJE A
ON RM.SIFRARM=A.SIFRARM
GROUP BY PROF.SIFRAPROF

