SQL - Rozbor hrubých mezd
Contents
SQL/NexusDB - Rozbor hrubých mezd
Vytvoreni pracovni tabulky s poctem odpracovanych hodin po mesicich za rodne cislo
DROP TABLE IF EXISTS ###RC;
CREATE TABLE ###RC
( Hodiny01,Hodiny02,Hodiny03,Hodiny04,Hodiny05,Hodiny06,Hodiny07,Hodiny08,Hodiny09,Hodiny10,Hodiny11,Hodiny12, RodneCislo ) AS
(
SELECT
*
FROM
(
SELECT
SUM(CASE WHEN MesicVypoctu=01 THEN V3Hodiny ELSE 0 END) AS Hodiny01,
SUM(CASE WHEN MesicVypoctu=02 THEN V3Hodiny ELSE 0 END) AS Hodiny02,
SUM(CASE WHEN MesicVypoctu=03 THEN V3Hodiny ELSE 0 END) AS Hodiny03,
SUM(CASE WHEN MesicVypoctu=04 THEN V3Hodiny ELSE 0 END) AS Hodiny04,
SUM(CASE WHEN MesicVypoctu=05 THEN V3Hodiny ELSE 0 END) AS Hodiny05,
SUM(CASE WHEN MesicVypoctu=06 THEN V3Hodiny ELSE 0 END) AS Hodiny06,
SUM(CASE WHEN MesicVypoctu=07 THEN V3Hodiny ELSE 0 END) AS Hodiny07,
SUM(CASE WHEN MesicVypoctu=08 THEN V3Hodiny ELSE 0 END) AS Hodiny08,
SUM(CASE WHEN MesicVypoctu=09 THEN V3Hodiny ELSE 0 END) AS Hodiny09,
SUM(CASE WHEN MesicVypoctu=10 THEN V3Hodiny ELSE 0 END) AS Hodiny10,
SUM(CASE WHEN MesicVypoctu=11 THEN V3Hodiny ELSE 0 END) AS Hodiny11,
SUM(CASE WHEN MesicVypoctu=12 THEN V3Hodiny ELSE 0 END) AS Hodiny12,
RodneCislo
FROM
HR7_QZ40
WHERE
(SLOM>=100) and (SLOM<=159) and
(DruhVety=3) // mesicni slozky mzdy
GROUP BY
RodneCislo
) a1
)
WITH DATA;
CREATE INDEX RodneCislo ON ###RC ( RodneCislo );
// SELECT * FROM ###RC
//####################################################################
// Hlavni SQL - je treba nastavit parametry HR7_QZ40 a a.MesicVypoctu=05
// pro odpovidajici agendu a rok HR7 a mesic vypoctu 05
// POZOR - data pro Kc a kalendarni dny pro hrubou mzdu jsou dostupna
// vzdy do predchoziho mesice
// proto napr. pro MesicVypoctu=05 jsou pocitana data za mesice 1-4
// ostatni data odpovidaji 5. mesici
// Tabulka ###HM75 je globalni pracovni tabulka a je dostupna
// v dalsich SQL dotazech jako kazda jina tabulka
//####################################################################
DROP TABLE IF EXISTS ###HM75;
CREATE TABLE ###HM75 AS
(
SELECT
b.Hodiny01,
b.Hodiny02,
b.Hodiny03,
b.Hodiny04,
b.Hodiny05,
b.Hodiny06,
b.Hodiny07,
b.Hodiny08,
b.Hodiny09,
b.Hodiny10,
b.Hodiny11,
b.Hodiny12,
*
FROM
(
SELECT
*
FROM
(
SELECT
a.OSCP, // osobni cislo
a.Jmeno,
a.RodneCisloASCII,
a.STRK, // stredisko kmenove
a.STRV, // stredisko vyplatni
a.FormaMzdy,
a.TarifniStupen,
a.Plat,
a.Premie, // % premii
CAST(a.DatumNastupu AS Date) AS NastupniDatum,
CAST(a.DatumUkonceniPP AS DATE) AS VystupniDatum,
a.Pohlavi,
a.UvazekDenniSjednany,
a.TydenniFond,
a.Prescas,
a.OdpracovanoDnu,
a.DNP, // denni davka nemocenskeho pojisteni
a.CN, // hodinova casova nahrada
a.PREMODM, // statistika - premie a odmeny
a.PRIPLAT, // statistika - priplatky
a.PRIPPCAS, // statistika - priplatky casove
a.PRUMVYD, // statistika - prumerny vydelek
(a.HMKcBR01+a.HMKcBR02+a.HMKcBR03+a.HMKcBR04+a.HMKcBR05+a.HMKcBR06+a.HMKcBR07+a.HMKcBR08+a.HMKcBR09+a.HMKcBR10+a.HMKcBR11+a.HMKcBR12) AS SumaHMKcBR,
// Hruba mzda Kc celkem - bezny rok
(case when FormaMzdy<>'6' then a.HMKalBR01+a.HMKalBR02+a.HMKalBR03+a.HMKalBR04+a.HMKalBR05+a.HMKalBR06+
a.HMKalBR07+a.HMKalBR08+a.HMKalBR09+a.HMKalBR10+a.HMKalBR11+a.HMKalBR12 else 0 end) AS SumaHMKalBR,
// odpracovano kalendarnich dni v beznem roce celkem
a.HMKcBR01,a.HMKcBR02,a.HMKcBR03,a.HMKcBR04,a.HMKcBR05,a.HMKcBR06,a.HMKcBR07,a.HMKcBR08,a.HMKcBR09,a.HMKcBR10,a.HMKcBR11,a.HMKcBR12,
// Hruba mzda Kc po mesicich - bezny rok
(case when a.FormaMzdy<>'6' then a.HMKalBR01 ELSE 0 END) AS HMDnyBR01,
(case when a.FormaMzdy<>'6' then a.HMKalBR02 ELSE 0 END) AS HMDnyBR02,
(case when a.FormaMzdy<>'6' then a.HMKalBR03 ELSE 0 END) AS HMDnyBR03,
(case when a.FormaMzdy<>'6' then a.HMKalBR04 ELSE 0 END) AS HMDnyBR04,
(case when a.FormaMzdy<>'6' then a.HMKalBR05 ELSE 0 END) AS HMDnyBR05,
(case when a.FormaMzdy<>'6' then a.HMKalBR06 ELSE 0 END) AS HMDnyBR06,
(case when a.FormaMzdy<>'6' then a.HMKalBR07 ELSE 0 END) AS HMDnyBR07,
(case when a.FormaMzdy<>'6' then a.HMKalBR08 ELSE 0 END) AS HMDnyBR08,
(case when a.FormaMzdy<>'6' then a.HMKalBR09 ELSE 0 END) AS HMDnyBR09,
(case when a.FormaMzdy<>'6' then a.HMKalBR10 ELSE 0 END) AS HMDnyBR10,
(case when a.FormaMzdy<>'6' then a.HMKalBR11 ELSE 0 END) AS HMDnyBR11,
(case when a.FormaMzdy<>'6' then a.HMKalBR12 ELSE 0 END) AS HMDnyBR12,
// odpracovano kalendarnich dni v beznem roce po mesicich
(a.HMKcMR01+a.HMKcMR02+a.HMKcMR03+a.HMKcMR04+a.HMKcMR05+a.HMKcMR06+a.HMKcMR07+a.HMKcMR08+a.HMKcMR09+a.HMKcMR10+a.HMKcMR11+a.HMKcMR12) AS SumaHMKcMR,
// Hruba mzda Kc celkem - minuly rok
(case when FormaMzdy<>'6' then a.HMKalMR01+a.HMKalMR02+a.HMKalMR03+a.HMKalMR04+a.HMKalMR05+a.HMKalMR06+
a.HMKalMR07+a.HMKalMR08+a.HMKalMR09+a.HMKalMR10+a.HMKalMR11+a.HMKalMR12 else 0 end) AS SumaHMKalMR,
// odpracovano kalendarnich dni v minulem roce celkem
a.HMKcMR01,a.HMKcMR02,a.HMKcMR03,a.HMKcMR04,a.HMKcMR05,a.HMKcMR06,a.HMKcMR07,a.HMKcMR08,a.HMKcMR09,a.HMKcMR10,a.HMKcMR11,a.HMKcMR12,
// Hruba mzda Kc po mesicich - minuly rok
(case when a.FormaMzdy<>'6' then a.HMKalMR01 ELSE 0 END) AS HMDnyMR01,
(case when a.FormaMzdy<>'6' then a.HMKalMR02 ELSE 0 END) AS HMDnyMR02,
(case when a.FormaMzdy<>'6' then a.HMKalMR03 ELSE 0 END) AS HMDnyMR03,
(case when a.FormaMzdy<>'6' then a.HMKalMR04 ELSE 0 END) AS HMDnyMR04,
(case when a.FormaMzdy<>'6' then a.HMKalMR05 ELSE 0 END) AS HMDnyMR05,
(case when a.FormaMzdy<>'6' then a.HMKalMR06 ELSE 0 END) AS HMDnyMR06,
(case when a.FormaMzdy<>'6' then a.HMKalMR07 ELSE 0 END) AS HMDnyMR07,
(case when a.FormaMzdy<>'6' then a.HMKalMR08 ELSE 0 END) AS HMDnyMR08,
(case when a.FormaMzdy<>'6' then a.HMKalMR09 ELSE 0 END) AS HMDnyMR09,
(case when a.FormaMzdy<>'6' then a.HMKalMR10 ELSE 0 END) AS HMDnyMR10,
(case when a.FormaMzdy<>'6' then a.HMKalMR11 ELSE 0 END) AS HMDnyMR11,
(case when a.FormaMzdy<>'6' then a.HMKalMR12 ELSE 0 END) AS HMDnyMR12,
// odpracovano kalendarnich dni v minulem roce po mesicich
a.MesicVypoctu,
a.DruhVety
FROM
HR7_QZ40 a
WHERE
(a.MesicVypoctu=05) and
(a.DruhVety=11)
ORDER BY
a.OSCP
) a1
WHERE
(SumaHMKcBR>0) and
(SumaHMKalBR>0)
) a2
LEFT OUTER JOIN
###RC b
ON
a2.RodneCisloASCII = b.RodneCislo
ORDER BY
a2.OSCP
)
WITH DATA;
SELECT * FROM ###HM75;