CV 10 / 11 SQL IDAS1

  CREATE OR REPLACE FORCE VIEW "ST43249"."CV10_V_VLASTNOSTI_2011" ("JMENO", "POCET") AS
  SELECT UPPER(JMENO) AS JMENO,
 (SELECT COUNT(*)
  FROM A_O_SNEHURCE.POZNATKY
  WHERE POZNATKY.ID_TRPASLIKA = TRPASLICI.ID_TRPASLIKA
  AND   POZNATKY.ZAZNAMENANO < TO_DATE('01.01.12','DD.MM.YY')
  ) AS POCET
FROM A_O_SNEHURCE.TRPASLICI
WHERE ID_TRPASLIKA NOT IN (SELECT ID_TRPASLIKA FROM A_O_SNEHURCE.POZNATKY
JOIN A_O_SNEHURCE.VLASTNOSTI ON POZNATKY.ID_VLASTNOSTI=A_O_SNEHURCE.VLASTNOSTI.ID_VLASTNOSTI
WHERE A_O_SNEHURCE.VLASTNOSTI.VLASTNOST = 'Upovídaný'
AND A_O_SNEHURCE.POZNATKY.ZAZNAMENANO < TO_DATE('01.01.12','DD.MM.YY'))
ORDER BY TRPASLICI.JMENO DESC;


  CREATE OR REPLACE FORCE VIEW "ST43249"."CV10_V_VITEZNA_STREDA" ("DATUM", "POCET", "HODIN") AS
  SELECT TO_CHAR(A_O_SNEHURCE.TEZBY.DEN, 'DD.MM.YYYY') AS
DATUM, COUNT(A_O_SNEHURCE.TEZBY.ID_TRPASLIKA) AS POCET, TO_CHAR(A_O_SNEHURCE.TEZBY.ODPRACOVANO,'HH.H') AS HODIN
FROM A_O_SNEHURCE.TEZBY
JOIN A_O_SNEHURCE.SACHTY ON  A_O_SNEHURCE.TEZBY.ID_SACHTY=SACHTY.ID_SACHTY
JOIN A_O_SNEHURCE.TRPASLICI ON  A_O_SNEHURCE.TEZBY.ID_TRPASLIKA=TRPASLICI.ID_TRPASLIKA
JOIN A_O_SNEHURCE.RUDY ON  A_O_SNEHURCE.SACHTY.ID_RUDY=RUDY.ID_RUDY
WHERE TEZBY.ODPRACOVANO = (SELECT MAX(TEZBY.ODPRACOVANO) FROM A_O_SNEHURCE.TEZBY)
AND  A_O_SNEHURCE.RUDY.RUDA = 'železná'
AND TO_CHAR( A_O_SNEHURCE.TEZBY.DEN, 'D.MM.YYYY')='3.02.2012'
GROUP BY TEZBY.DEN, TEZBY.ID_TRPASLIKA, TEZBY.ODPRACOVANO;


  CREATE OR REPLACE FORCE VIEW "ST43249"."CV10_V_PODIL_TEZBY_BREZ" ("JMENO", "PROCENTA") AS
  SELECT UPPER(TRPASLICI.JMENO) AS JMENO,
to_number(to_char(SUM(SKUTECNOST) / (SELECT SUM(SKUTECNOST)
FROM A_O_SNEHURCE.TEZBY
JOIN A_O_SNEHURCE.TRPASLICI ON A_O_SNEHURCE.TEZBY.ID_TRPASLIKA = TRPASLICI.ID_TRPASLIKA
JOIN A_O_SNEHURCE.SACHTY ON A_O_SNEHURCE.TEZBY.ID_SACHTY = A_O_SNEHURCE.SACHTY.ID_SACHTY
JOIN A_O_SNEHURCE.RUDY ON A_O_SNEHURCE.SACHTY.ID_RUDY = A_O_SNEHURCE.RUDY.ID_RUDY
WHERE RUDY.RUDA = 'železná' AND A_O_SNEHURCE.TEZBY.DEN >= '01.03.12'
AND TEZBY.DEN <= '15.03.12') * 100, '99.9'), '99.9') AS PROCENTA
FROM A_O_SNEHURCE.TEZBY
JOIN A_O_SNEHURCE.TRPASLICI ON A_O_SNEHURCE.TEZBY.ID_TRPASLIKA = A_O_SNEHURCE.TRPASLICI.ID_TRPASLIKA
JOIN A_O_SNEHURCE.SACHTY ON A_O_SNEHURCE.SACHTY.ID_SACHTY = A_O_SNEHURCE.TEZBY.ID_SACHTY
JOIN A_O_SNEHURCE.RUDY ON A_O_SNEHURCE.SACHTY.ID_RUDY = A_O_SNEHURCE.RUDY.ID_RUDY
WHERE A_O_SNEHURCE.TEZBY.DEN >= '01.03.12' AND A_O_SNEHURCE.TEZBY.DEN <= '15.03.12'
AND A_O_SNEHURCE.RUDY.RUDA = 'železná'
GROUP BY A_O_SNEHURCE.TRPASLICI.JMENO, A_O_SNEHURCE.RUDY.RUDA;


  CREATE OR REPLACE FORCE VIEW "ST43249"."CV10_V_PA_NE_PRACE" ("JMENO", "POCET") AS
  SELECT TRPASLICI.JMENO, (SELECT COUNT(*)
        FROM A_O_SNEHURCE.TEZBY
        WHERE to_char(TEZBY.DEN, 'D')>=5
        AND TRPASLICI.ID_TRPASLIKA=TEZBY.ID_TRPASLIKA
        AND TEZBY.DEN BETWEEN to_date('01.03.12','DD.MM.YY')
        AND to_date('31.03.12','DD.MM.YY')) AS POCET
FROM A_O_SNEHURCE.TRPASLICI;

  CREATE OR REPLACE FORCE VIEW "ST43249"."CV11_TRPASLICI_PREHLED" ("TRPASLIK", "STRIBRNA", "OLOVENA", "CINOVA", "MANGANOVA", "MEDENA", "MAGNETOVA", "ZELEZNA") AS
  SELECT UPPER(A_O_SNEHURCE.TRPASLICI.JMENO) TRPASLIK,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='stříbrná' THEN 1 END) AS STRIBRNA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='olověná' THEN 1 END) AS OLOVENA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='cínová' THEN 1 END) AS CINOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='manganová' THEN 1 END) AS MANGANOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='měděná' THEN 1 END) AS MEDENA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='magnetová' THEN 1 END) AS MAGNETOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='železná' THEN 1 END) AS ZELEYNA
FROM A_O_SNEHURCE.TRPASLICI, A_O_SNEHURCE.TEZBY , A_O_SNEHURCE.SACHTY , A_O_SNEHURCE.RUDY
WHERE A_O_SNEHURCE.TRPASLICI.ID_TRPASLIKA = A_O_SNEHURCE.TEZBY.ID_TRPASLIKA
AND A_O_SNEHURCE.TEZBY.ID_SACHTY = A_O_SNEHURCE.SACHTY.ID_SACHTY
AND A_O_SNEHURCE.SACHTY.ID_RUDY = A_O_SNEHURCE.RUDY.ID_RUDY
AND A_O_SNEHURCE.TEZBY.PRICHOD IS NOT NULL
GROUP BY A_O_SNEHURCE.TRPASLICI.jmeno
ORDER BY A_O_SNEHURCE.TRPASLICI.jmeno ASC;

  CREATE OR REPLACE FORCE VIEW "ST43249"."CV11_V_HISTOGRAM" ("RUDA", "TRPASLICI") AS
  SELECT INITCAP(A_O_SNEHURCE.RUDY.RUDA) AS RUDA,
RPAD('*', COUNT(DISTINCT TRPASLICI.id_trpaslika),'*') AS TRPASLICI
FROM A_O_SNEHURCE.RUDY
JOIN A_O_SNEHURCE.SACHTY ON A_O_SNEHURCE.RUDY.ID_RUDY = A_O_SNEHURCE.SACHTY.ID_RUDY
JOIN A_O_SNEHURCE.TEZBY ON A_O_SNEHURCE.SACHTY.ID_SACHTY = A_O_SNEHURCE.TEZBY.ID_SACHTY
JOIN A_O_SNEHURCE.TRPASLICI ON A_O_SNEHURCE.TRPASLICI.ID_TRPASLIKA=A_O_SNEHURCE.TEZBY.ID_TRPASLIKA
WHERE TO_CHAR(TEZBY.PRICHOD, 'YYYY')='2013'
GROUP BY A_O_SNEHURCE.RUDY.RUDA
ORDER BY A_O_SNEHURCE.RUDY.RUDA;

  CREATE OR REPLACE FORCE VIEW "ST43249"."CV11_V_TRPASLICI_PREHLED" ("TRPASLIK", "STRIBRNA", "OLOVENA", "CINOVA", "MANGANOVA", "MEDENA", "MAGNETOVA", "ZELEZNA") AS
  SELECT UPPER(A_O_SNEHURCE.TRPASLICI.JMENO) TRPASLIK,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='stříbrná' THEN 1 END) AS STRIBRNA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='olověná' THEN 1 END) AS OLOVENA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='cínová' THEN 1 END) AS CINOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='manganová' THEN 1 END) AS MANGANOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='měděná' THEN 1 END) AS MEDENA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='magnetová' THEN 1 END) AS MAGNETOVA,
COUNT(CASE WHEN A_O_SNEHURCE.RUDY.ruda='železná' THEN 1 END) AS ZELEYNA
FROM A_O_SNEHURCE.TRPASLICI, A_O_SNEHURCE.TEZBY , A_O_SNEHURCE.RUDY,  A_O_SNEHURCE.SACHTY
WHERE A_O_SNEHURCE.TRPASLICI.ID_TRPASLIKA = A_O_SNEHURCE.TEZBY.ID_TRPASLIKA
AND A_O_SNEHURCE.TEZBY.ID_SACHTY = A_O_SNEHURCE.SACHTY.ID_SACHTY
AND A_O_SNEHURCE.SACHTY.ID_RUDY = A_O_SNEHURCE.RUDY.ID_RUDY
AND A_O_SNEHURCE.TEZBY.PRICHOD IS NOT NULL
GROUP BY A_O_SNEHURCE.TRPASLICI.jmeno
ORDER BY A_O_SNEHURCE.TRPASLICI.jmeno ASC;