SQL - Mzdy
Contents
Hrube mzdy 2007+2008 po mesicich, podle STRK, OSCP
DROP TABLE IF EXISTS ###Mzdy;
CREATE TABLE ###Mzdy
(
STRK,
OSCP,
HM701,
HM702,
HM703,
HM704,
HM705,
HM706,
HM707,
HM708,
HM709,
HM710,
HM711,
HM712,
HM801,
HM802,
HM803,
HM804,
HM805,
HM806,
HM807,
HM808,
HM809,
HM810,
HM811,
HM812
) AS
(
SELECT
STRK,
OSCP,
SUM(xHM701) AS HM701,
SUM(xHM702) AS HM702,
SUM(xHM703) AS HM703,
SUM(xHM704) AS HM704,
SUM(xHM705) AS HM705,
SUM(xHM706) AS HM706,
SUM(xHM707) AS HM707,
SUM(xHM708) AS HM708,
SUM(xHM709) AS HM709,
SUM(xHM710) AS HM710,
SUM(xHM711) AS HM711,
SUM(xHM712) AS HM712,
SUM(xHM801) AS HM801,
SUM(xHM802) AS HM802,
SUM(xHM803) AS HM803,
SUM(xHM804) AS HM804,
SUM(xHM805) AS HM805,
SUM(xHM806) AS HM806,
SUM(xHM807) AS HM807,
SUM(xHM808) AS HM808,
SUM(xHM809) AS HM809,
SUM(xHM810) AS HM810,
SUM(xHM811) AS HM811,
SUM(xHM812) AS HM812
FROM
(
SELECT
STRK,
OSCP,
MesicVypoctu,
(case when MesicVypoctu= 1 then V5HrubaMzda ELSE 0 END) AS xHM701,
(case when MesicVypoctu= 2 then V5HrubaMzda ELSE 0 END) AS xHM702,
(case when MesicVypoctu= 3 then V5HrubaMzda ELSE 0 END) AS xHM703,
(case when MesicVypoctu= 4 then V5HrubaMzda ELSE 0 END) AS xHM704,
(case when MesicVypoctu= 5 then V5HrubaMzda ELSE 0 END) AS xHM705,
(case when MesicVypoctu= 6 then V5HrubaMzda ELSE 0 END) AS xHM706,
(case when MesicVypoctu= 7 then V5HrubaMzda ELSE 0 END) AS xHM707,
(case when MesicVypoctu= 8 then V5HrubaMzda ELSE 0 END) AS xHM708,
(case when MesicVypoctu= 9 then V5HrubaMzda ELSE 0 END) AS xHM709,
(case when MesicVypoctu=10 then V5HrubaMzda ELSE 0 END) AS xHM710,
(case when MesicVypoctu=11 then V5HrubaMzda ELSE 0 END) AS xHM711,
(case when MesicVypoctu=12 then V5HrubaMzda ELSE 0 END) AS xHM712,
0 AS xHM801,
0 AS xHM802,
0 AS xHM803,
0 AS xHM804,
0 AS xHM805,
0 AS xHM806,
0 AS xHM807,
0 AS xHM808,
0 AS xHM809,
0 AS xHM810,
0 AS xHM811,
0 AS xHM812
FROM
HR7_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,OSCP,MesicVypoctu
UNION ALL
SELECT
STRK,
OSCP,
MesicVypoctu,
0 AS xHM701,
0 AS xHM702,
0 AS xHM703,
0 AS xHM704,
0 AS xHM705,
0 AS xHM706,
0 AS xHM707,
0 AS xHM708,
0 AS xHM709,
0 AS xHM710,
0 AS xHM711,
0 AS xHM712,
(case when MesicVypoctu= 1 then V5HrubaMzda ELSE 0 END) AS xHM801,
(case when MesicVypoctu= 2 then V5HrubaMzda ELSE 0 END) AS xHM802,
(case when MesicVypoctu= 3 then V5HrubaMzda ELSE 0 END) AS xHM803,
(case when MesicVypoctu= 4 then V5HrubaMzda ELSE 0 END) AS xHM804,
(case when MesicVypoctu= 5 then V5HrubaMzda ELSE 0 END) AS xHM805,
(case when MesicVypoctu= 6 then V5HrubaMzda ELSE 0 END) AS xHM806,
(case when MesicVypoctu= 7 then V5HrubaMzda ELSE 0 END) AS xHM807,
(case when MesicVypoctu= 8 then V5HrubaMzda ELSE 0 END) AS xHM808,
(case when MesicVypoctu= 9 then V5HrubaMzda ELSE 0 END) AS xHM809,
(case when MesicVypoctu=10 then V5HrubaMzda ELSE 0 END) AS xHM810,
(case when MesicVypoctu=11 then V5HrubaMzda ELSE 0 END) AS xHM811,
(case when MesicVypoctu=12 then V5HrubaMzda ELSE 0 END) AS xHM812
FROM
HR8_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,OSCP,MesicVypoctu
) AS x
GROUP BY
STRK,OSCP
)
WITH DATA;
CREATE INDEX OSCP ON ###Mzdy ( OSCP );
CREATE INDEX STRK ON ###Mzdy ( STRK );
SELECT * FROM ###Mzdy;
Hrube mzdy + pocty zamestnancu 2007+2008 po mesicich, podle STRK
DROP TABLE IF EXISTS ###Mzdy;
CREATE TABLE ###Mzdy
(
STRK,
HM701,
HM702,
HM703,
HM704,
HM705,
HM706,
HM707,
HM708,
HM709,
HM710,
HM711,
HM712,
HM801,
HM802,
HM803,
HM804,
HM805,
HM806,
HM807,
HM808,
HM809,
HM810,
HM811,
HM812,
P701,
P702,
P703,
P704,
P705,
P706,
P707,
P708,
P709,
P710,
P711,
P712,
P801,
P802,
P803,
P804,
P805,
P806,
P807,
P808,
P809,
P810,
P811,
P812
) AS
(
SELECT
STRK,
SUM(xHM701) AS HM701,
SUM(xHM702) AS HM702,
SUM(xHM703) AS HM703,
SUM(xHM704) AS HM704,
SUM(xHM705) AS HM705,
SUM(xHM706) AS HM706,
SUM(xHM707) AS HM707,
SUM(xHM708) AS HM708,
SUM(xHM709) AS HM709,
SUM(xHM710) AS HM710,
SUM(xHM711) AS HM711,
SUM(xHM712) AS HM712,
SUM(xHM801) AS HM801,
SUM(xHM802) AS HM802,
SUM(xHM803) AS HM803,
SUM(xHM804) AS HM804,
SUM(xHM805) AS HM805,
SUM(xHM806) AS HM806,
SUM(xHM807) AS HM807,
SUM(xHM808) AS HM808,
SUM(xHM809) AS HM809,
SUM(xHM810) AS HM810,
SUM(xHM811) AS HM811,
SUM(xHM812) AS HM812,
SUM(xP701) AS P701,
SUM(xP702) AS P702,
SUM(xP703) AS P703,
SUM(xP704) AS P704,
SUM(xP705) AS P705,
SUM(xP706) AS P706,
SUM(xP707) AS P707,
SUM(xP708) AS P708,
SUM(xP709) AS P709,
SUM(xP710) AS P710,
SUM(xP711) AS P711,
SUM(xP712) AS P712,
SUM(xP801) AS P801,
SUM(xP802) AS P802,
SUM(xP803) AS P803,
SUM(xP804) AS P804,
SUM(xP805) AS P805,
SUM(xP806) AS P806,
SUM(xP807) AS P807,
SUM(xP808) AS P808,
SUM(xP809) AS P809,
SUM(xP810) AS P810,
SUM(xP811) AS P811,
SUM(xP812) AS P812
FROM
(
SELECT
STRK,
MesicVypoctu,
(case when MesicVypoctu= 1 then V5HrubaMzda ELSE 0 END) AS xHM701,
(case when MesicVypoctu= 2 then V5HrubaMzda ELSE 0 END) AS xHM702,
(case when MesicVypoctu= 3 then V5HrubaMzda ELSE 0 END) AS xHM703,
(case when MesicVypoctu= 4 then V5HrubaMzda ELSE 0 END) AS xHM704,
(case when MesicVypoctu= 5 then V5HrubaMzda ELSE 0 END) AS xHM705,
(case when MesicVypoctu= 6 then V5HrubaMzda ELSE 0 END) AS xHM706,
(case when MesicVypoctu= 7 then V5HrubaMzda ELSE 0 END) AS xHM707,
(case when MesicVypoctu= 8 then V5HrubaMzda ELSE 0 END) AS xHM708,
(case when MesicVypoctu= 9 then V5HrubaMzda ELSE 0 END) AS xHM709,
(case when MesicVypoctu=10 then V5HrubaMzda ELSE 0 END) AS xHM710,
(case when MesicVypoctu=11 then V5HrubaMzda ELSE 0 END) AS xHM711,
(case when MesicVypoctu=12 then V5HrubaMzda ELSE 0 END) AS xHM712,
0 AS xHM801,
0 AS xHM802,
0 AS xHM803,
0 AS xHM804,
0 AS xHM805,
0 AS xHM806,
0 AS xHM807,
0 AS xHM808,
0 AS xHM809,
0 AS xHM810,
0 AS xHM811,
0 AS xHM812,
0 AS xP701,
0 AS xP702,
0 AS xP703,
0 AS xP704,
0 AS xP705,
0 AS xP706,
0 AS xP707,
0 AS xP708,
0 AS xP709,
0 AS xP710,
0 AS xP711,
0 AS xP712,
0 AS xP801,
0 AS xP802,
0 AS xP803,
0 AS xP804,
0 AS xP805,
0 AS xP806,
0 AS xP807,
0 AS xP808,
0 AS xP809,
0 AS xP810,
0 AS xP811,
0 AS xP812
FROM
HR7_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,MesicVypoctu
UNION ALL
SELECT
STRK,
MesicVypoctu,
0 AS xHM701,
0 AS xHM702,
0 AS xHM703,
0 AS xHM704,
0 AS xHM705,
0 AS xHM706,
0 AS xHM707,
0 AS xHM708,
0 AS xHM709,
0 AS xHM710,
0 AS xHM711,
0 AS xHM712,
(case when MesicVypoctu= 1 then V5HrubaMzda ELSE 0 END) AS xHM801,
(case when MesicVypoctu= 2 then V5HrubaMzda ELSE 0 END) AS xHM802,
(case when MesicVypoctu= 3 then V5HrubaMzda ELSE 0 END) AS xHM803,
(case when MesicVypoctu= 4 then V5HrubaMzda ELSE 0 END) AS xHM804,
(case when MesicVypoctu= 5 then V5HrubaMzda ELSE 0 END) AS xHM805,
(case when MesicVypoctu= 6 then V5HrubaMzda ELSE 0 END) AS xHM806,
(case when MesicVypoctu= 7 then V5HrubaMzda ELSE 0 END) AS xHM807,
(case when MesicVypoctu= 8 then V5HrubaMzda ELSE 0 END) AS xHM808,
(case when MesicVypoctu= 9 then V5HrubaMzda ELSE 0 END) AS xHM809,
(case when MesicVypoctu=10 then V5HrubaMzda ELSE 0 END) AS xHM810,
(case when MesicVypoctu=11 then V5HrubaMzda ELSE 0 END) AS xHM811,
(case when MesicVypoctu=12 then V5HrubaMzda ELSE 0 END) AS xHM812,
0 AS xP701,
0 AS xP702,
0 AS xP703,
0 AS xP704,
0 AS xP705,
0 AS xP706,
0 AS xP707,
0 AS xP708,
0 AS xP709,
0 AS xP710,
0 AS xP711,
0 AS xP712,
0 AS xP801,
0 AS xP802,
0 AS xP803,
0 AS xP804,
0 AS xP805,
0 AS xP806,
0 AS xP807,
0 AS xP808,
0 AS xP809,
0 AS xP810,
0 AS xP811,
0 AS xP812
FROM
HR8_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,MesicVypoctu
UNION ALL
SELECT
STRK,
MesicVypoctu,
0 AS xHM701,
0 AS xHM702,
0 AS xHM703,
0 AS xHM704,
0 AS xHM705,
0 AS xHM706,
0 AS xHM707,
0 AS xHM708,
0 AS xHM709,
0 AS xHM710,
0 AS xHM711,
0 AS xHM712,
0 AS xHM801,
0 AS xHM802,
0 AS xHM803,
0 AS xHM804,
0 AS xHM805,
0 AS xHM806,
0 AS xHM807,
0 AS xHM808,
0 AS xHM809,
0 AS xHM810,
0 AS xHM811,
0 AS xHM812,
(case when MesicVypoctu= 1 then 1 ELSE 0 END) AS xP701,
(case when MesicVypoctu= 2 then 1 ELSE 0 END) AS xP702,
(case when MesicVypoctu= 3 then 1 ELSE 0 END) AS xP703,
(case when MesicVypoctu= 4 then 1 ELSE 0 END) AS xP704,
(case when MesicVypoctu= 5 then 1 ELSE 0 END) AS xP705,
(case when MesicVypoctu= 6 then 1 ELSE 0 END) AS xP706,
(case when MesicVypoctu= 7 then 1 ELSE 0 END) AS xP707,
(case when MesicVypoctu= 8 then 1 ELSE 0 END) AS xP708,
(case when MesicVypoctu= 9 then 1 ELSE 0 END) AS xP709,
(case when MesicVypoctu=10 then 1 ELSE 0 END) AS xP710,
(case when MesicVypoctu=11 then 1 ELSE 0 END) AS xP711,
(case when MesicVypoctu=12 then 1 ELSE 0 END) AS xP712,
0 AS xP801,
0 AS xP802,
0 AS xP803,
0 AS xP804,
0 AS xP805,
0 AS xP806,
0 AS xP807,
0 AS xP808,
0 AS xP809,
0 AS xP810,
0 AS xP811,
0 AS xP812
FROM
HR7_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,MesicVypoctu
UNION ALL
SELECT
STRK,
MesicVypoctu,
0 AS xHM701,
0 AS xHM702,
0 AS xHM703,
0 AS xHM704,
0 AS xHM705,
0 AS xHM706,
0 AS xHM707,
0 AS xHM708,
0 AS xHM709,
0 AS xHM710,
0 AS xHM711,
0 AS xHM712,
0 AS xHM801,
0 AS xHM802,
0 AS xHM803,
0 AS xHM804,
0 AS xHM805,
0 AS xHM806,
0 AS xHM807,
0 AS xHM808,
0 AS xHM809,
0 AS xHM810,
0 AS xHM811,
0 AS xHM812,
0 AS xP701,
0 AS xP702,
0 AS xP703,
0 AS xP704,
0 AS xP705,
0 AS xP706,
0 AS xP707,
0 AS xP708,
0 AS xP709,
0 AS xP710,
0 AS xP711,
0 AS xP712,
(case when MesicVypoctu= 1 then 1 ELSE 0 END) AS xP801,
(case when MesicVypoctu= 2 then 1 ELSE 0 END) AS xP802,
(case when MesicVypoctu= 3 then 1 ELSE 0 END) AS xP803,
(case when MesicVypoctu= 4 then 1 ELSE 0 END) AS xP804,
(case when MesicVypoctu= 5 then 1 ELSE 0 END) AS xP805,
(case when MesicVypoctu= 6 then 1 ELSE 0 END) AS xP806,
(case when MesicVypoctu= 7 then 1 ELSE 0 END) AS xP807,
(case when MesicVypoctu= 8 then 1 ELSE 0 END) AS xP808,
(case when MesicVypoctu= 9 then 1 ELSE 0 END) AS xP809,
(case when MesicVypoctu=10 then 1 ELSE 0 END) AS xP810,
(case when MesicVypoctu=11 then 1 ELSE 0 END) AS xP811,
(case when MesicVypoctu=12 then 1 ELSE 0 END) AS xP812
FROM
HR8_QZ40
WHERE
(DruhVety=11) // karta zamestnance
AND (V5HrubaMzda>0)
ORDER BY
STRK,MesicVypoctu
) AS x
GROUP BY
STRK
)
WITH DATA;
CREATE INDEX STRK ON ###Mzdy ( STRK );
SELECT * FROM ###Mzdy;
FAQ - SQL pro pole tabulky Mzdy - Číselník složek mezd - ____QZ33
SELECT
Cislo AS SLOM_Cislo,
Cislo32 AS SLOM_Retezec,
Nazev,
NazevASCII, // pro fulltext
Poznamka,
Int32_01 AS UcetMD,
Int32_02 AS UcetDAL,
Int32_03 AS StrediskoMD,
Int32_04 AS StrediskoDAL,
"AutoInc"
FROM
____QZ33
FAQ - SQL pro pole tabulky Mzdy - Měsíční složky mezd - aarmQZ22
SELECT
OSCP AS CisloZamestnance,
SLOM AS SlozkaMzdy,
ROKZ AS Rok,
CMES AS Mesic,
ZACI AS DenOd,
KONI AS DenDo,
HOZA AS Hodiny,
SAZI AS Sazba,
KCSV AS Kc,
KLIC,
"AutoInc",
Tabulka,
Stav,
ZmenaDatumServer,
ZmenaKod,
ZmenaKdo,
ZmenaPC,
DatumVytvoreni
FROM
HR73QZ22
ORDER BY
OSCP,SLOM
FAQ - SQL pro pole tabulky Mzdy - Trvalé složky mezd - aarmQZ24
SELECT
OSCP AS CisloZamestnance,
SLOM AS SlozkaMzdy,
PRSR AS Prijemce,
UPRS AS UcetPrijemce,
KCSV AS CastkaKc,
VS AS VariabilniSymbol,
KS AS KonstantniSymbol,
SPECS AS SpecifickySymbol,
KONS AS ZustatekDluhu,
KCSZ AS PrevodMzdyNaUcet999999_9,
Poznamka,
KC1 AS SrazkaDluhu92x01,
KC2 AS SrazkaDluhu92x02,
KC3 AS SrazkaDluhu92x03,
KC4 AS SrazkaDluhu92x04,
KC5 AS SrazkaDluhu92x05,
KC6 AS SrazkaDluhu92x06,
KC7 AS SrazkaDluhu92x07,
KC8 AS SrazkaDluhu92x08,
KC9 AS SrazkaDluhu92x09,
KC10 AS SrazkaDluhu92x10,
KC11 AS SrazkaDluhu92x11,
KC12 AS SrazkaDluhu92x12,
PORCIS AS PoradiSrazky,
"AutoInc",
Tabulka,
Stav,
ZmenaDatumServer,
ZmenaKod,
ZmenaKdo,
ZmenaPC,
DatumVytvoreni
FROM
HR73QZ24
ORDER BY
OSCP,SLOM
FAQ - SQL pro pole tabulky Mzdy - Rodinní příslušníci - aarmQZ13
SELECT
OSCP AS CisloZamestnance,
JMRP AS JmenoRodinnehoPrislusnika,
NARP AS DatumNarozeniRodinnehoPrislusnika,
BYRP AS RodneCisloRodinnehoPrislusnika,
DVRP AS DruhVztahuRodinnehoPrislusnika,
NAZJ AS Poznamka,
DAND AS PoznamkaSlevaNaDan,
RPIN AS InvalidniDite,
"AutoInc",
Tabulka,
Stav,
ZmenaDatumServer,
ZmenaKod,
ZmenaKdo,
ZmenaPC,
DatumVytvoreni
FROM
HR73QZ13
ORDER BY
OSCP,NARP
FAQ - SQL pro pole karty zaměstnanců - aarmQZ11
SELECT
// Cislo
OSCP AS OsobniCisloPracovnika,
OSHL AS OsobniCisloHlavnihoPracovnihoPomeru,
OSC1 AS OsobniCisloVPP,
// Jmeno
JMNO AS Jmeno,
JMNOASCII AS JmenoProFulltext,
NAZEV AS Jmeno,
NAZEVS AS Prijmeni,
FIELD_TEXT AS Titul,
// Rodne
RODC AS RodneCislo,
JMRO AS RodnePrijmeni,
MNAR AS MistoNarozeni,
POHLAVI,
// Stredisko
STRK AS StrediskoKmenove,
STRV AS StrediskoVyplatni,
// Bydliste
NAZP AS Ulice,
POLE AS CisloDomu,
JTABMS AS Obec,
PSCT AS PSC,
BTRU AS BydlisteTrvaleUlice,
BTRM AS BydlisteTrvaleMisto,
TELB AS BydlisteTrvaleTelefon,
BPRU AS BydlistePrechodneUlice,
BPRM AS BydlistePrechodneMisto,
PSCP AS BydlistePrechodnePSC,
TELN AS BydlistePrechodneTelefon,
// Stav
CCCC AS StatniObcanstvi,
STAR AS RodinnyStav,
STAD AS RodinnyStavDatumZmeny,
// Doklady
RIPR AS RidicskyPrukaz,
COPR AS CisloObcanskehoPrukazu,
// Uvazek
UZAD AS UvazekHodinZakonnyDenni,
USJE AS UvazekHodinSjednanyDenni,
UZAT AS TydenniPracovniUvazek,
// Doba zamestnani
DATA AS DatumNastupu,
DAUP AS DatumUkonceniPracovnihoPomeru,
PDUV AS KonecPracovnihoPomeruDuvod,
// Vyneti
DRVJ AS DruhVynetiZEvidencnihoPoctu,
DAVI AS DatumVyneti,
DANA AS DatumNavratuZVyneti,
// Mzda
FORM AS FormaMzdy,
TROS AS TarifniStupen,
SAZ1 AS MzdovaSazbaPremieProc,
SAZM AS MzdovaSazbaPlat,
OHOS AS OsobniOhodnoceni,
JVYP AS KodOdlisnostiVypoctu,
ZNAK AS ZakladDanePoplatnik,
Index1 AS ProcentoPenzijnihoPripojisteni,
DAN1 AS VymerovaciZakladMzdaVPP,
DSKZ AS ZakladDaneInvelidita,
// Pracovni pomer
POMD AS DruhPracovnihoPomeru,
NAFU AS NazevFunkce,
DCIN AS DruhCinnosti,
// Vzdelani
VZDELANI,
// Pomocne
"AutoInc",
Stav AS BlokaceDat,
Zarazka AS BlokaceVety,
ZmenaDatumServer,
ZmenaKod,
ZmenaKdo,
ZmenaPC,
DatumVytvoreni,
// Duchod
DADU AS DatumNarokuNaDuchod,
DASK AS DatumNarokuNaDuchodSkutecny,
DDPD AS DruhDuchodu,
DUCH AS VyseDuchodu,
NAR2 AS DatumVznikuDuchodu,
// SP MO
OSOBY AS DruhCinnosti116SPMO,
MUZ AS OmluvenaAbsence116SPMO,
OSVY AS VylouceneDoby116SPMO,
// SP
DAN3 AS ZaraditDoZmenaCSSZ,
DATUM AS DatumZmenyCSSZ,
// ZP
CIPO AS ZdravotniPojistovna,
Index4 AS ZPZDoplatkuDoMinimalniMzdyPlati,
FIELD_ALGO AS ZPHromadneOznameniKod1,
FIELD_ANO AS ZPHromadneOznameniKod2,
FIELD_CIS AS ZPHromadneOznameniKod3,
FIELD_DIL AS ZPHromadneOznameniKod4,
CAST(FIELD_DEC AS DateTime) AS ZPHromadneOznameniDatum1,
CAST(FIELD_LEN AS DateTime) AS ZPHromadneOznameniDatum2,
CAST(FIELD_OEV AS DateTime) AS ZPHromadneOznameniDatum3,
CAST(FIELD_PORC AS DateTime) AS ZPHromadneOznameniDatum4,
// ELDP
Index2 AS ELDPZaraditVMesici,
NARD AS ELDPDnyNemoci,
// Preventivni prohlidky
ODNEM AS DatumPosledniPreventivniProhlidky,
ZVMS AS PreventivniProhlidkaIntervalMesicu,
"Text" AS PreventivniProhlidkaPoznamka,
// Nocni prohlidky
CROH AS NocniProhlidka,
DATZ AS DatumNocniProhlidky,
UROH AS NocniProhlidkyDatum,
// Dovolena
NADO AS NarokNaDovolenouBR,
DODO AS NarokNaDodatecnouDovolenouBR,
CEDO AS CerpaniDovoleneBR,
ZUDO AS ZustatekDovoleneBR,
PRED AS PrevodLonskeDovolene,
CEZU AS CerpaniDovoleneMR,
ZUDL AS ZustatekDovoleneMR,
KC1 AS CerpaniDovolene100BR01,
KC2 AS CerpaniDovolene100BR02,
KC3 AS CerpaniDovolene100BR03,
KC4 AS CerpaniDovolene100BR04,
KC5 AS CerpaniDovolene100BR05,
KC6 AS CerpaniDovolene100BR06,
KC7 AS CerpaniDovolene100BR07,
KC8 AS CerpaniDovolene100BR08,
KC9 AS CerpaniDovolene100BR09,
KC10 AS CerpaniDovolene100BR10,
KC11 AS CerpaniDovolene100BR11,
KC12 AS CerpaniDovolene100BR12,
MV1 AS CerpaniDovolene100MR01,
MV10 AS CerpaniDovolene100MR02,
MV100 AS CerpaniDovolene100MR03,
MV1000 AS CerpaniDovolene100MR04,
MV2 AS CerpaniDovolene100MR05,
MV20 AS CerpaniDovolene100MR06,
MV200 AS CerpaniDovolene100MR07,
MV2000 AS CerpaniDovolene100MR08,
MV5 AS CerpaniDovolene100MR09,
MV50 AS CerpaniDovolene100MR10,
MV500 AS CerpaniDovolene100MR11,
MV5000 AS CerpaniDovolene100MR12,
// DNP
DNPN AS DavkaDNPKZacatkuNemoci,
PRZN AS VydelekPrumernyProDNPRucneKlic9,
// Nemoc
POLE1 AS NemocenskaKc01,
POLE2 AS NemocenskaKc02,
POLE3 AS NemocenskaKc03,
POLE4 AS NemocenskaKc04,
POLE5 AS NemocenskaKc05,
POLE6 AS NemocenskaKc06,
POLE7 AS NemocenskaKc07,
POLE8 AS NemocenskaKc08,
POLE9 AS NemocenskaKc09,
POLE10 AS NemocenskaKc10,
POLE11 AS NemocenskaKc11,
POLE12 AS NemocenskaKc12,
POLE1_Q AS NemocenskaDnyPracKal01,
POLE2_Q AS NemocenskaDnyPracKal02,
POLE3_Q AS NemocenskaDnyPracKal03,
POLE4_Q AS NemocenskaDnyPracKal04,
POLE5_Q AS NemocenskaDnyPracKal05,
POLE6_Q AS NemocenskaDnyPracKal06,
POLE7_Q AS NemocenskaDnyPracKal07,
POLE8_Q AS NemocenskaDnyPracKal08,
POLE9_Q AS NemocenskaDnyPracKal09,
POLE10_Q AS NemocenskaDnyPracKal10,
POLE11_Q AS NemocenskaDnyPracKal11,
POLE12_Q AS NemocenskaDnyPracKal12,
// CN
PRUC AS VydelekPrumernyProCN,
VYN1 AS HodinyProCN,
VYN4 AS MzdyProCN,
MS593 AS CN100Mzda1Q,
MS722 AS CN100Mzda2Q,
MS922 AS CN100Mzda3Q,
MS923 AS CN100Mzda4Q,
MS924 AS CN100Hodiny1Q,
MS925 AS CN100Hodiny2Q,
MS926 AS CN100Hodiny3Q,
MS935 AS CN100Hodiny4Q,
MS940 AS CN100Q1,
MS942 AS CN100Q2,
MS951 AS CN100Q3,
MS971 AS CN100Q4,
// Prumerny vydelek
DVZM01 AS MRHrubaMzda01,
DVZM02 AS MRHrubaMzda02,
DVZM03 AS MRHrubaMzda03,
DVZM04 AS MRHrubaMzda04,
DVZM05 AS MRHrubaMzda05,
DVZM06 AS MRHrubaMzda06,
DVZM07 AS MRHrubaMzda07,
DVZM08 AS MRHrubaMzda08,
DVZM09 AS MRHrubaMzda09,
DVZM10 AS MRHrubaMzda10,
DVZM11 AS MRHrubaMzda11,
DVZM12 AS MRHrubaMzda12,
DVZA01 AS BRHrubaMzda01,
DVZA02 AS BRHrubaMzda02,
DVZA03 AS BRHrubaMzda03,
DVZA04 AS BRHrubaMzda04,
DVZA05 AS BRHrubaMzda05,
DVZA06 AS BRHrubaMzda06,
DVZA07 AS BRHrubaMzda07,
DVZA08 AS BRHrubaMzda08,
DVZA09 AS BRHrubaMzda09,
DVZA10 AS BRHrubaMzda10,
DVZA11 AS BRHrubaMzda11,
DVZA12 AS BRHrubaMzda12,
I_01 AS MRKalendarnichDni01,
I_02 AS MRKalendarnichDni02,
I_03 AS MRKalendarnichDni03,
I_04 AS MRKalendarnichDni04,
I_05 AS MRKalendarnichDni05,
I_06 AS MRKalendarnichDni06,
I_07 AS MRKalendarnichDni07,
I_08 AS MRKalendarnichDni08,
I_09 AS MRKalendarnichDni09,
I_10 AS MRKalendarnichDni10,
I_11 AS MRKalendarnichDni11,
I_12 AS MRKalendarnichDni12,
F_01 AS BRKalendarnichDni01,
F_02 AS BRKalendarnichDni02,
F_03 AS BRKalendarnichDni03,
F_04 AS BRKalendarnichDni04,
F_05 AS BRKalendarnichDni05,
F_06 AS BRKalendarnichDni06,
F_07 AS BRKalendarnichDni07,
F_08 AS BRKalendarnichDni08,
F_09 AS BRKalendarnichDni09,
F_10 AS BRKalendarnichDni10,
F_11 AS BRKalendarnichDni11,
F_12 AS BRKalendarnichDni12,
// Statistika ISPV
ABSENCE AS CelkovyPocetNeodpracovanychHodinOdPocatkuRoku,
ABSNAHR AS PocetNeodpracovanychHodinSNahradouMzdy,
CZICSE AS PostaveniVZamestnani,
DOBAZAM AS DobaZamestnani,
DOVOLENA,
EVIDDNY AS PocetDnuVEvidencnimPoctuZamestnancu,
MZDA,
NEMOC AS PocetPracovnichHodinNemoci,
NEZCAST,
ODPRACD AS OdpracovanaDoba,
PREMODM AS PremieAOdmenyPravidelne,
PRESCAS,
PRIPLAT,
PRIPPCAS,
TYDFOND,
VEDOUCI,
(ZAMEST+VOLI) AS Zamestnani,
ROKNAR AS RokNarozeni,
ZENA AS CistaMzda,
ZP_C AS Nahrady,
ZP_P AS OdmenyZaPracovniPohotovost,
// Srazky
SKSC AS PocetVychovavanychDeti,
SVSK AS PocetOsobProZakonneSrazky,
// Sestavy
DATC AS DatumZmeny181DodatekKPracSmlouve,
// Datovy sklad
HOD_SAZBA AS V2CN,
HOMU AS V2Dovolena,
RVY1 AS V4ZakladDane,
RVY2 AS V4Osoba,
RVY3 AS V4Deti,
RVY4 AS V4Invalidita,
SP_C AS V4Student,
MES_SAZBA AS V5DNP,
RVY5 AS V5HrubaMzda,
RVY6 AS V5CistaMzda,
RVY7 AS V5KVyplate,
RVY8 AS V6Dobirka,
MPV2 AS V7CID,
MPV4 AS V7ID,
MPV6 AS V7ZTP,
MPV7 AS V7Student,
NEKA AS V7VPP,
ODPD AS V7DPC,
OSDA AS V7DPP,
POJI AS V7HPP,
PPREPM AS V7DPVZ,
PPREPZ AS V7DPDan,
PREPM AS V7DPSrazkova,
PREPZ AS V7DPPoplatnik,
PSAZI AS V7Dite,
SAZI AS V7DiteZTP,
TDAN AS V7Manzelka,
TDETI AS V7ManzelkaZTP,
HOSU AS V8ZPVZ,
HOVI AS V8ZPVZ135,
HOZA AS V8ZPZamestnanec,
HRUBA AS V8ZPZamestnavatel,
RVY9 AS V8ZPIndex,
NEPR AS V9SPVZ,
PHOZA AS V9SPZamestnanec,
PKCSV AS V9SPZamestnavatel,
//
1
FROM
HR73QZ11
2005.10.01a / 09:00 BETA
18) Pracovni SQL pro nemocenske z datoveho skladu
- spusti se v FFE
- vysledek lze z FFE exportovat a tisknout
Prehled nemocenskych Kc v jednotlivych mesicich
SELECT
DruhVety,MesicVypoctu,OSCP,Jmeno
,NKc01,NKc02,NKc03,NKc04,NKc05,NKc06,NKc07,NKc08,NKc09,NKc10,NKc11,NKc12
// ,NPrac01,NPrac02,NPrac03,NPrac04,NPrac05,NPrac06,NPrac07,NPrac08,NPrac09,NPrac10,NPrac11,NPrac12
// ,NKal01,NKal02,NKal03,NKal04,NKal05,NKal06,NKal07,NKal08,NKal09,NKal10,NKal11,NKal12
// misto Kc lze generovat pracovni nebo kalendarni dny
FROM
HR5_QZ40
// pro rok 2004 staci zmenit na HR4_QZ40
WHERE
(DruhVety=11) and (MesicVypoctu=8)
ORDER BY
Jmeno,OSCP,MesicVypoctu
Prehled jednotlivych nemoci - SLOM 8xx - v roce 2005
SELECT
OSCP,SLOM,DruhVety,Jmeno,Mesic,Rok,Od,Do,Kc,Klic
FROM
HR5_QZ40
// lze pouzit pro rok 2004 - nahradte za HR4_QZ40
WHERE
(DruhVety=22) and (SLOM>=800) and (SLOM<=899)
ORDER BY
Jmeno,OSCP,Mesic,SLOM