Table of Contents

HR (VEMA) Preparing DB view for time slices

There is an example of each sub-views for UNIONed final view time_slices.

Goal of this article

Requirement

Process of creating time slices

  1. Check/create tables in MSSQL for receiving data from VEMA (for example osobyt, smlouva, pzaraz, tutv, todz…)
  2. Create 3 main views pzaraz slices, smlouva slices and vyneti slices based on the examples below
  3. Check and correct views with your table names and columns there might be differenes and each company can use slightly different tables in VEMA
  4. Manually check few users and their exclusion from being active/enabled (marked by column vyneti true)
  5. Check for duplicates of time slices - unique dates in column slice for one person

Explanation of the result

  1. Each row is a time slice. Each row means a snapshot of the current state of the employee at that time. In other words it is a date when there is any change within its contract(start, end), organization changes or temporary leave(maternity).

Column slice is unique date for that specific employee and only one change happens on one day. That also means beginning of the work contract one row, end of the work contract new separate row.

  1. columns ppvza and ppvko - means start and date of work contract
  2. column vyneti is true if the employee is on maternity leave(or other temporary leave)
  3. column zamest/povfct is organizational information for position name.
Explanation of the picture:
Each row - different date:
  1. Start of work contract
  2. Change in organization
  3. Start of temporary leave (Exclusion from work - usually no access in IDM, but keep account)
  4. End of temporary leave+contract end/break
  5. Start on new position, start of contract
  6. Change of the position
  7. End of the contract

This picture is simplified version of DB view timeslices

Source data

Source data are the data exported in the requirements tasks.

table smlouvax

table pzarazx

table vynetix(or obrvyn)

table osobyx(or osobyt)

table todz and czisco or table todp

table tutv

Creating DB views and extra information on time slices data

IDM Entity basic columns, these should be selected in your SQL:

As mentioned before time slices are made from 3 main views/tables(smlouvax\_slices, vynetix\_slices and pzarazx\_slices) and connected with "oscis" which is unique personal number. All views are connected with UNION command, therefore all views must have same columns in the same order.

  1. Pzarazx - most of the columns are connected to this view. Table is about, where the person works and linking columns to other tables.
  2. Smlouvyx - contracts info mainly start and end of contract
  3. Vynetix - exclusion date ranges from work, usually maternity leave dates - start and end

Notes for code example below:

DB view smlouvyx - contracts

Goal of this DB view is to get rows for each start and end date of contracts 

SELECT
s.slice,        -- valid from of slice
s.slice_id,     -- unique ID, you can see below it is concatenated String
s.contract_id,  -- Code of the parent contract
s.oscis,        -- Owner - personal number or username
s.cicin,        -- not mapped, but it is person's contract number used for making slice_id and contract_id
z.ppvdr,        -- Main relation - type of contract
s.ppvza,        -- valid from of contract
s.ppvko,        -- valid till of contract
f.povfct,       -- Name of position
f.povfct1,      -- EAV abbreviation for name of position
f.povfct2,      -- EAV extended name of position
z.utvar,        -- Work position(code) IdmTreeNode
p.sk_pzar,      -- EAV zarazeni extra work position and segmentation
CASE WHEN v.oscis IS NULL THEN 'false' ELSE 'true' END AS vyneti, -- if true contract is not active during this period of time slice
z.kateg,        -- EAV
z.pzar,         -- EAV id for extra work position and segmentation
z.pracvz,       --EAV extra work position and segmentation
o.greatest_timestamp -- timestamp for changes
-- creating slice_id and contract_id with for the time slice. In this case any start(valid from) of the contract.
FROM (
   SELECT
      sm.ppvza AS slice,
      concat(sm.oscis, '_', sm.cicin, '_', YEAR(sm.ppvza), '_', MONTH(sm.ppvza), '_',DAY(sm.ppvza)) AS slice_id, -- beginning of contract as a slice_id, notice smlouvy.ppvza - which is the valid from of contract
      concat(sm.oscis, '_', sm.cicin) AS contract_id, -- combination of personal number and number of work contract
      sm.oscis, sm.cicin, sm.ppvza, sm.ppvko
   FROM smlouvax sm
) AS s
-- pzarazx LEAD() function for next date comparassion and other info for connecting other tables
LEFT JOIN (
   SELECT p.oscis, p.cicin, p.zauc, povfc, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc, p.utvar, p.ppvdr, pzar, kateg, pracvz 
   FROM pzarazx p
   ) z
ON s.oscis = z.oscis AND s.cicin = z.cicin AND z.zauc <= s.slice AND (z.next_zauc IS NULL OR z.next_zauc > s.slice)
-- linked with other tables to load the data
LEFT JOIN osobyx o ON s.oscis = o.oscis
LEFT JOIN todp f ON f.povfc = z.povfc 
LEFT JOIN tutv u ON u.utvar = z.utvar 
LEFT JOIN pzar p ON p.pzar = z.pzar
-- for purpose of column "vyneti" state of the time slice.
LEFT JOIN vynetix v ON v.oscis = s.oscis AND v.vynz <= s.slice AND (v.vynk > s.slice OR v.vynk IS NULL)
For the next 2 views of pzarazx\_slices and vynetix\_slices, remember to use the same columns in the selection. Follow the right table s,z,v,o,f to load proper data and replace the conditions and JOINS.
IMPORTANT: DONT COPY the columns part. The columns will be same, but they will not come from the same tables. For example in vynetix\_slices loading slice and slice\_id from table v(vynetix) instead of table smlouvax "s". Vyneti column will be also changing true/false values, especially in vynetix slices.

DB view vynetix - exclusion data

Goal of this view is to generate rows which contain start and enddate of temporary leave. If leave is valid, column vyneti = true. When temporary leave ends, column vyneti = false. This temporary leave is generated for each contract, that means if employee has 2 valid contracts, there will be 4 rows for 1 temporary leave. 2 starts(1 for each contract) and 2 end dates for each contract with same dates. This is to ensure that employees break applies to all of his/her contracts.

Pokud má vynětí stejné datum začátku jako datum konce, pak se vygenerují dva řezy s duplicitním ID. Tato situace není ošetřena, protože to ukazuje spíš na chybu v datech.

If there are rows with duplicate date and same contract(start and end date of temporary leave on the same day). This will create a problem and most probably there is error in data in VEMA. 
CREATE VIEW vynetix_slices AS 
-- rows with start of temporary leaves
SELECT
   vyn.slice, vyn.slice_id, contract_id, vyn.oscis,
   vyn.cicin,
   vyn.ppvza, vyn.ppvko,
   zar.zames,   
   c.praczar, c.praczarzk,
   'true' AS vyneti,
   o.rosu, o.greatest_timestamp
FROM (
   SELECT
      v.vynz AS slice,
      concat(v.oscis, '_', s.cicin, '_', YEAR(v.vynz), '_', MONTH(v.vynz), '_',DAY(v.vynz)) AS slice_id,
      concat(v.oscis, '_', s.cicin) AS contract_id,
      v.oscis, s.cicin, s.ppvza, s.ppvko
   FROM vynetix v
   -- Vygenerovani rezu pro kazdy uvazek, ktery je v dobe vyneti platny
   INNER JOIN smlouvax s ON v.oscis = s.oscis AND v.vynz >= s.ppvza AND (v.vynz <= s.ppvko OR s.ppvko IS NULL)
) vyn
LEFT JOIN (
   -- Vyhledani pracovnich zarazeni - next_zauc odpovida konci platnosti zarazeni (protoze to LEAD najde jako zacatek ucinnosti nasledujiciho zarazeni).
   SELECT p.oscis, p.cicin, p.zauc, zames, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc
   FROM pzarazx p
   ) zar
-- Vyfiltrovani platneho zarazeni k datu rezu. Zacatek zarazeni je pred datem rezu, konec neni definovan nebo je po datu rezu.
ON vyn.oscis = zar.oscis AND vyn.cicin = zar.cicin AND zar.zauc <= vyn.slice AND (zar.next_zauc IS NULL OR zar.next_zauc > vyn.slice)
-- Dotahneme zbyvajici informace z ciselniku
LEFT JOIN osobyx o ON vyn.oscis = o.oscis
LEFT JOIN todz t ON zar.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
-- Tento union zajisti i odmazani duplicit, pokud ve stejny den zacina vic ruznych druhu vyneti
UNION
-- rows with end dates of leaves
SELECT
   vyn.slice, vyn.slice_id, contract_id, vyn.oscis,
   vyn.cicin,
   vyn.ppvza,
   vyn.ppvko,
   zar.zames,   
   c.praczar,
   c.praczarzk,
   'false' AS vyneti,
   o.rosu,
   o.greatest_timestamp
FROM (
   SELECT
      v.vynk AS slice,
      concat(v.oscis, '_', s.cicin, '_', YEAR(v.vynk), '_', MONTH(v.vynk), '_',DAY(v.vynk)) AS slice_id,
      concat(v.oscis, '_', s.cicin) AS contract_id,
      v.oscis, s.cicin, s.ppvza, s.ppvko
   FROM vynetix v
   INNER JOIN smlouvax s ON v.oscis = s.oscis AND v.vynk >= s.ppvza AND (v.vynk <= s.ppvko OR s.ppvko IS NULL)
) vyn
LEFT JOIN (
   SELECT p.oscis, p.cicin, p.zauc, zames, LEAD(zauc, 1) OVER (PARTITION BY oscis, cicin ORDER BY zauc ASC) AS next_zauc
   FROM pzarazx p
   ) zar
ON vyn.oscis = zar.oscis AND vyn.cicin = zar.cicin AND zar.zauc <= vyn.slice AND (zar.next_zauc IS NULL OR zar.next_zauc > vyn.slice)
LEFT JOIN osobyx o ON vyn.oscis = o.oscis
LEFT JOIN todz t ON zar.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
-- Konce vyneti mohou byt null, proto je odfiltrujeme
WHERE vyn.slice IS NOT NULL;

DB view for pzarazx - organizational position

Cílem je získat řez pro každý den, kdy nastává začátek účinnosti (zauc) nějakého pracovního zařazení.

Pokud ke dni zauc neexistuje žádná platná smlouva, takový řez se nebude publikovat (není smlouva = není úvazek). Až v den začátku smlouvy se objeví řez, který pochází z řezů pro smlouvy (viz výše).

K řezu se pak dotáhnou zbývající informace (organizační zařazení, zda je zaměstnanec zrovna vyňatý). 

CREATE VIEW pzarazx_slices AS 
SELECT 
    z.zauc AS slice,
    concat(z.oscis, '_', z.cicin, '_', YEAR(z.zauc), '_', MONTH(z.zauc), '_',DAY(z.zauc)) AS slice_id,
    concat(z.oscis, '_', z.cicin) AS contract_id,
    z.oscis,
    z.cicin,
    s.ppvza,
    s.ppvko,
    z.zames,   
    c.praczar,
    c.praczarzk,
    CASE WHEN v.oscis IS NULL THEN 'false' ELSE 'true' END AS vyneti,
    o.rosu,
    o.greatest_timestamp
FROM pzarazx z
-- Vyhledani platne smlouvy k datu rezu. Pokud zadna platna smlouva neni, takovy rez nebudeme publikovat
INNER JOIN smlouvax s ON z.oscis = s.oscis AND z.cicin = s.cicin AND z.zauc >= s.ppvza AND (z.zauc <= s.ppvko OR s.ppvko IS NULL)
LEFT JOIN osobyx o ON z.oscis = o.oscis
LEFT JOIN todz t ON z.zames = t.zames
LEFT JOIN czisco c ON t.czisco = c.czisco
LEFT JOIN vynetix v ON v.oscis = z.oscis AND v.vynz <= z.zauc AND (v.vynk > z.zauc OR v.vynk IS NULL)

Possible issues

Use columns povfc/povfct from table pzaraz + todp

Use columns zames/zamest from table pzaraz + todz

Tips

Check for duplicated rows with same identifier slice\_id. Duplicates are sometimes created due to 1 day leave or with same start date of contract. Better to check the users after synchronization is finished. 

SELECT COUNT(*), slice_id FROM time_slices GROUP BY slice_id HAVING COUNT(*) > 1