Sääntöjoukon esimerkit - palkkaliittymä

Huomoithan, että poimintasääntöjen luominen ja muokkaaminen edellyttää perustason ymmärrystä SQL:stä.

Tähän artikkeliin on koottu tyypillinen palkkaliittymän malli. Tässä mallissa on esitetty parhaat käytännöt palkkaliittymän määrittelyyn, jossa yhdellä säännöllä poimitaan kerralla kokonaisuus - esimerkiksi kaikki ylityöt. Säännöt voidaan hyödyntää sellaisenaan palkkaliittymissä, mutta niihin tulee huomioida kohdejärjestelmän vaatimukset esimerkiksi tiedon ryhmittelyn ja mahdollisten järjestelmäkohtaisten tietokenttien osalta.

• Tässä artikkelissa käydään läpi sääntöjoukon poiminnan perusperiaatteet.
  Lisäksi artikkelissa käydään läpi poimintasääntöihin useimmiten tarvittavat lisäsäännöt.
  
  
• Tietokantataulujen sarakekuvaukset löydät täältä.
  
  
• Muita sääntöjoukon esimerkkejä löydät seuraavista artikkeleista:
  
  Poissaololiittymän poiminnan malli
  Proketirakenteen ja projektien huomiointi poiminnassa
  Henkilötietojen poiminnan malli

Henkilötietojen (UserInfoData) poiminta

Henkilötietojen poimintaan ei ole suositeltavaa käyttää JOIN-lauseketta. Jos henkilötieto liitetään JOIN-lausekkeella väärin, se helposti johtaa palkka-aineiston tuntimäärien moninkertaistumiseen.

Tämän välttämiseen on suositeltavaa käyttää seuraavaa tapaa, jolla on myös helpompi poimia useampi henkilötieto:

SELECT *, SUM(Amount) AS OverrideAmount,
-- Henkilöryhmän koodi, valittu CustomData1 -tietoon
IFNULL(
  (
    SELECT ui.Value FROM UserInfoData ui
    WHERE ui.InfoTypeName = 'UserGroupCode_SDSQL' AND
    datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
    LIMIT 1
  ),
  ''
) AS CustomData1,
-- Maksuryhmän koodi, valittu CustomData2 -tietoon
IFNULL(
  (
    SELECT ui.Value FROM UserInfoData ui
    WHERE ui.InfoTypeName = 'NeptonPaymentGroupCode_SDSQL' AND
    datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
    LIMIT 1
  ),
  ''
) AS CustomData2
FROM UserSalaryData
GROUP BY SalaryRenderingDate, CompensationType, ProjectCode1

Sääntöjoukon malli

Sääntöjoukolle perustetaan mallin mukaisesti seuraavat poimintasäännöt. Poimintasäännöt ovat ryhmitelty siten, että kukin sääntö poimii aineistokokonaisuuden kerralla, kuten ylityöt tai sairauspoissaolot. Mallin mukaista sääntöä tulee muokata siten, että se sisältää oikeat järjestelmä- tai asiakaskohtaiset palkkalajit ja halutun ryhmittelyn.

Mikäli poimittu aineisto sisältää sellaisia rivejä, joiden palkkalajia ei ole otettu huomioon aineistossa niin ne tulevat palkkalajilla 'NOT_DEFINED'

Toteutunut työ ilman ylitöitä

Poimitaan kaikki tapahtumat, jotka huomioidaan säännölliseen työaikaan (CompensationType = 'BasicTime'), sekä saldosta vähennetty aika.

SELECT *, 
  SUM(Amount) As OverrideAmount
FROM 
  UserSalaryData
WHERE
  ActivityTypeCategoryId = 1
  AND CompensationType = 'BasicTime'
  AND SickLeaveTypeId IS NULL
  AND AbsenceTypeID IS NULL
  AND BasePartTarget IN ('salary')
  AND (OvertimeTarget IS NULL OR OvertimeTarget = 'uncompensated')
GROUP BY 
  SalaryRenderingDate

Jos tuloksia on tarve ryhmitellä päivän (SalaryRenderingDate) lisäksi projektin mukaan, GROUP BY -osioon lisätään tarpeen mukaan ProjectCode1, ProjectCode2, ProjectCode3, ProjectCode4, ProjectCode5, ProjectCode6.

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, 
ProjectCode2, ProjectCode3, ProjectCode4, ProjectCode5, ProjectCode6

Mikäli palkkajärjestelmässä on tärkeää eritellä tapahtumalle tehty kuvaus, niin siinä tapauksessa ryhmitellään vielä per kuvaus (Comment).

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1,
ProjectCode2, ProjectCode3, ProjectCode4, ProjectCode5, ProjectCode6, Comment

Toteutunut ylityö

Poimitaan kaikki tapahtumat, joissa muodostuu ylityötä, jonka perusosa sekä korotusosa maksetaan palkkana.

SELECT *, SUM(Amount) As OverrideAmount

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

,CASE 
-- TES ylityöt
WHEN CompensationType = 'AdditionalWork' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ_PALKKALAJI' -- lisätyö
WHEN CompensationType = 'AdditionalWork' AND overtimepercentage = 50 AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ50%_PALKKALAJI' -- lisätyö50%
WHEN CompensationType = 'OvertimeDaily50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VRK50_PALKKALAJI' -- vrk50
WHEN CompensationType = 'OvertimeDaily100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VRK100_PALKKALAJI' -- vrk100
WHEN CompensationType = 'OvertimeWeekly50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VK50_PALKKALAJI' -- vk50
WHEN CompensationType = 'OvertimeWeekly100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_VK100_PALKKALAJI' -- vk100
WHEN CompensationType = 'OvertimeSunday50' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_SUNNUNTAI50_PALKKALAJI' -- Sunnuntai50
WHEN CompensationType = 'OvertimeSunday100' AND IsTesOvertime = 'Y' AND OvertimeTarget = 'salary' THEN 'TES_SUNNUNTAI100_PALKKALAJI' -- Sunnuntai100
WHEN CompensationType = 'TESOvertime50' AND OvertimeTarget = 'salary' THEN 'TES50_PALKKALAJI' -- TES50
WHEN CompensationType = 'TESOvertime100' AND OvertimeTarget = 'salary' THEN 'TES100_PALKKALAJI' -- TES100

-- Muut kuin TES-ylityöt
WHEN CompensationType = 'AdditionalWork' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ_PALKKALAJI' -- lisätyö
WHEN CompensationType = 'AdditionalWork' AND overtimepercentage = 50 AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'LISÄTYÖ50%_PALKKALAJI' -- lisätyö50%
WHEN CompensationType = 'OvertimeDaily50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN  'VRK50_PALKKALAJI' -- vrk50
WHEN CompensationType = 'OvertimeDaily100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VRK100_PALKKALAJI' -- vrk100
WHEN CompensationType = 'OvertimeWeekly50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VK50_PALKKALAJI' -- vk50
WHEN CompensationType = 'OvertimeWeekly100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'VK100_PALKKALAJI' -- vk100
WHEN CompensationType = 'OvertimeSunday50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'SUNNUNTAI50_PALKKALAJI' -- Sunnuntai50
WHEN CompensationType = 'OvertimeSunday100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'SUNNUNTAI100_PALKKALAJI' -- Sunnuntai100
WHEN CompensationType = 'OvertimeBaseFixed' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'BASEFIXED_PALKKALAJI' -- Base fixed
WHEN CompensationType = 'OvertimeExtraFixed' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'EXTRAFIXED_PALKKALAJI' -- Extra fixed
WHEN CompensationType = 'LevellingPeriodAdditional' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOLISATYO_PALKKALAJI' -- Levelling period additional work
WHEN CompensationType = 'LevellingPeriodOvertime50' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOYLITYO50_PALKKALAJI' -- Levelling period overtime 50
WHEN CompensationType = 'LevellingPeriodOvertime100' AND IsTesOvertime = 'N' AND OvertimeTarget = 'salary' THEN 'TASOITTUMISJAKSOYLITYO100_PALKKALAJI' -- Levelling period overtime 100

-- put below this line any customer specific rules

--put above this line any customer specific rules

ELSE
'NOT_DEFINED'
END AS OverrideSalaryCode

FROM UserSalaryData

WHERE CompensationType IN ('AdditionalWork','OvertimeDaily50','OvertimeDaily100',
'OvertimeWeekly50','OvertimeWeekly100','OvertimeSunday50','OvertimeSunday100',
'OvertimeBaseFixed','OvertimeExtraFixed','TESOvertime50','TESOvertime100',
'LevellingPeriodAdditional','LevellingPeriodOvertime50','LevellingPeriodOvertime100')

AND BasePartTarget IN ('salary')
AND IsApproved = 'Y'

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Työkorotukset

Poimitaan kaikki työkorotukset ja asetetaan työkorotuksen palkkalajiksi korotukselle asetettu palkkalajikoodi. Työkorotuksen koodiksi on mahdollista asettaa palkkalaji / - viivalla jaoteltuna siten, että

• ensimmäinen osa kuvaa palkkalajia säännöllisellä työajalla
• toinen osa kuvaa palkkalajia 50% ylityön ajalta
• kolmas osa kuvaa palkkaljia 100% ylityön ajalta

Työkorotuksen koodia ei kuitenkaan tarvitse jakaa tällä tavalla, vaan sääntö poimii kaikissa tapauksissa koodin sellaisenaan, vaikka jaottelua ei ole tehty.

SELECT *, SUM(Amount) As OverrideAmount,

CASE
WHEN instr(WorkRiseSalaryCode,'/') = 0 OR WorkRiseEarnedAtCompensationType = 'BasicTime' THEN substring_part(WorkRiseSalaryCode, '/', 1)
WHEN WorkRiseEarnedAtCompensationType IN ('OvertimeDaily50','OvertimeWeekly50','OvertimeSunday50') THEN substring_part(WorkRiseSalaryCode, '/', 2)
ELSE substring_part(WorkRiseSalaryCode, '/', 3) 

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

END AS OverrideSalaryCode

FROM UserSalaryData

WHERE CompensationType = 'WorkRise' 
AND workrisepaytarget = 'Salary'
AND IsApproved = 'Y'

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Työlisät

Poimitaan kaikki työlisät ja asetetaan työlisän palkkalajiksi korotukselle asetettu palkkalajikoodi.

SELECT *, SUM(Amount) As OverrideAmount,
WorkIncrementCode as OverrideSalaryCode

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

FROM UserSalaryData

WHERE CompensationType = 'WorkIncrement' 
AND IsApproved = 'Y'

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Osapäivän sairauspoissaolot

Poimitaan kaikki osapäivän sairauspoissaolot

SELECT *, SUM(Amount) As OverrideAmount

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

,CASE
WHEN SickLeaveTypeId = 1 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_ITSESAIRAS' -- palkaton itse sairas 
WHEN SickLeaveTypeId = 2 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_LAPSISAIRAS' -- palkaton lapsi sairas 
WHEN SickLeaveTypeId = 3 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TYOTAPATURMA_TOISSA' -- palkaton työtapaturma työpaikalla 
WHEN SickLeaveTypeId = 4 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TYOTAPATURMA_TYOMATKALLA' -- palkaton työtapaturma työmatkalla 
WHEN SickLeaveTypeId = 5 AND CompensationType = 'UnpaidBasicTime' THEN 'PALKATON_TAPATURMA_VAPAALLA' --palkaton vapaa-ajan tapaturma

WHEN SickLeaveTypeId = 1 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_ITSESAIRAS' -- palkallinen itse sairas
WHEN SickLeaveTypeId = 2 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_LAPSISAIRAS' -- palkallinen lapsi sairas
WHEN SickLeaveTypeId = 3 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TYOTAPATURMA_TOISSA' -- palkallinen työtapaturma työpaikalla
WHEN SickLeaveTypeId = 4 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TYOTAPATURMA_TYOMATKALLA' -- palkallinen työtapaturma työmatkalla
WHEN SickLeaveTypeId = 5 AND CompensationType = 'BasicTime' THEN 'PALKALLINEN_TAPATURMA_VAPAALLA' --palkallinen vapaa-ajan tapaturma

-- put below this line any customer specific absence events 

--put above this line any customer specific absence events 

WHEN SickLeaveTypeId IS NOT NULL THEN ('Sairauspoissaolo: ' || SickLeaveTypeId || ' - ' || CompensationType) --this row picks up all sick leaves´types that are not picked up by the rule already

ELSE

'NOT DEFINED'

END AS OverrideSalaryCode

FROM UserSalaryData

WHERE CompensationType IN ('BasicTime','UnpaidBasicTime')
AND 
SickLeaveTypeId IS NOT NULL
AND IsApproved = 'Y'

-- 
-- Alla oleva ehto jättää tuloksista pois ne päivät, joissa työaikaa on korvattu jonain muuna kuin sairauspoissaolona
AND
SalaryRenderingDate IN 
(SELECT SalaryRenderingDate 

FROM UserSalaryData 

WHERE SickLeaveTypeId IS NULL 

AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime', 'AdditionalWork', 'OvertimeDaily50', 'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50', 'OvertimeSunday100', 'PublicHoliday', 'YearLeave')) 

--

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Osapäivän poissaolot

Poimitaan kaikki osapäivän poissaolot

SELECT *, SUM(Amount) As OverrideAmount

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

,CASE

WHEN AbsenceTypeId = 25 AND CompensationType = 'UnpaidBasicTime' THEN 'AMMATTIYHDISTYSTOIMINTA_PALKATON' -- Palkaton ammattiyhdistystoiminta 
WHEN AbsenceTypeId = 10 AND CompensationType = 'UnpaidBasicTime' THEN 'ASEPALVELUS_PALKATON' -- Palkaton ase- tai siviilipalvelusaika 
WHEN AbsenceTypeId = 1 AND CompensationType = 'UnpaidBasicTime' THEN 'ERITYISAITIYSVAPAA_PALKATON'-- Palkaton erityisäitiysvapaa 
WHEN AbsenceTypeId = 5 AND CompensationType = 'UnpaidBasicTime' THEN 'HOITOVAPAA_PALKATON'-- Palkaton hoitovapaa 
WHEN AbsenceTypeId = 3 AND CompensationType = 'UnpaidBasicTime' THEN 'ISYYSVAPAA_PALKATON'-- Palkaton isyysvapaa 
WHEN AbsenceTypeId = 11 AND CompensationType = 'UnpaidBasicTime' THEN 'KERTAUS_PALKATON'-- Palkaton kertausharjoitus
WHEN AbsenceTypeId = 27 AND CompensationType = 'UnpaidBasicTime' THEN 'KUNTOUTUS_PALKATON' -- Palkaton kuntoutus 
WHEN AbsenceTypeId = 18 AND CompensationType = 'UnpaidBasicTime' THEN 'KUTSUNTA_PALKATON' -- Palkaton kutsuntatilaisuus 
WHEN AbsenceTypeId = 13 AND CompensationType = 'UnpaidBasicTime' THEN 'MUU_POISSAOLO_PALKATON' -- Palkaton muu poissaolo 
WHEN AbsenceTypeId = 26 AND CompensationType = 'UnpaidBasicTime' THEN 'MUUTTOVAPAA_PALKATON' -- Palkaton muuttovapaa 
WHEN AbsenceTypeId = 452 AND CompensationType = 'UnpaidBasicTime' THEN 'OMAISHOITOVAPAA_PALKATON' -- Palkaton omaishoitovapaa
WHEN AbsenceTypeId = 17 AND CompensationType = 'UnpaidBasicTime' THEN 'OMAT50_60_JUHLAT_PALKATON' -- Palkaton oma 50- tai 60-vuotisjuhla
WHEN AbsenceTypeId = 16 AND CompensationType = 'UnpaidBasicTime' THEN 'PARISUHTEEN_REKISTEROINTI_PALKATON' -- Palkaton omat vihkiäiset / parisuhteen rekisteröinti 
WHEN AbsenceTypeId = 8 AND CompensationType = 'UnpaidBasicTime' THEN 'OPINTOVAPAA_PALKATON' -- Palkaton opintovapaa 
WHEN AbsenceTypeId = 22 AND CompensationType = 'UnpaidBasicTime' THEN 'OSA_AIKAELAKE_PALKATON' -- Palkaton osa-aikaeläke 
WHEN AbsenceTypeId = 31 AND CompensationType = 'UnpaidBasicTime' THEN 'OSA-AIKAISUUS_PALKATON' -- Palkaton osa-aikaisuus 
WHEN AbsenceTypeId = 14 AND CompensationType = 'UnpaidBasicTime' THEN 'PERHEENJASENEN_KUOLEMA_PALKATON' -- Palkaton perheenjäsenen kuolema 
WHEN AbsenceTypeId = 15 AND CompensationType = 'UnpaidBasicTime' THEN 'HAUTAJAISET_PALKATON' -- Palkaton perheenjäsenen tai lähiomaisen hautajaiset 
WHEN AbsenceTypeId = 20 AND CompensationType = 'UnpaidBasicTime' THEN 'PERHEENJASENEN_SAIRASTUMINEN_PALKATON' -- Palkaton perheenjäsenen äkillinen sairastuminen 
WHEN AbsenceTypeId = 7 AND CompensationType = 'UnpaidBasicTime' THEN 'PAKOTTAVAT_PERHESYYT_PALKATON' -- Palkaton poissaolo pakottavien perhesyiden takia 
WHEN AbsenceTypeId = 450 AND CompensationType = 'UnpaidBasicTime' THEN 'RASKAUSVAPAA_PALKATON' -- Palkaton raskausvapaa
WHEN AbsenceTypeId = 6 AND CompensationType = 'UnpaidBasicTime' THEN 'TILAPAINEN_HOITOVAPAA_PALKATON' -- Palkaton tilapäinen hoitovapaa 
WHEN AbsenceTypeId = 19 AND CompensationType = 'UnpaidBasicTime' THEN 'TOIMIVAPAA_PALKATON' -- Palkaton toimivapaa 
WHEN AbsenceTypeId = 12 AND CompensationType = 'UnpaidBasicTime' THEN 'TYONTEKIJAN_PYYNTO_PALKATON' -- Palkaton työntekijän oma pyyntö 
WHEN AbsenceTypeId = 451 AND CompensationType = 'UnpaidBasicTime' THEN 'VANHEMPAINVAPAA_PALKATON' -- Palkaton vanhempainvapaa
WHEN AbsenceTypeId = 4 AND CompensationType = 'UnpaidBasicTime' THEN 'VANHEMPAINVAPAA_PALKATON'-- Palkaton vanhempainvapaa 
WHEN AbsenceTypeId = 23 AND CompensationType = 'UnpaidBasicTime' THEN 'VIRKAPAVAA_PALKATON' -- Palkaton virkavapaa 
WHEN AbsenceTypeId = 9 AND CompensationType = 'UnpaidBasicTime' THEN 'VUOROTTELUVAPAA_PALKATON' -- Palkaton vuorotteluvapaa 
WHEN AbsenceTypeId = 2 AND CompensationType = 'UnpaidBasicTime' THEN 'AITIYSVAPAA_PALKATON' -- Palkaton äitiysvapaa

WHEN AbsenceTypeId = 25 AND CompensationType = 'BasicTime' THEN 'AMMATTIYHDISTYSTOIMINTA_PALKALLINEN' -- Palkallinen ammattiyhdistystoiminta
WHEN AbsenceTypeId = 10 AND CompensationType = 'BasicTime' THEN 'ASEPALVELUS_PALKALLINEN' -- Palkallinen ase- tai siviilipalvelusaika
WHEN AbsenceTypeId = 1 AND CompensationType = 'BasicTime' THEN 'ERITYISAITIYSVAPAA_PALKALLINEN'-- Palkallinen erityisäitiysvapaa
WHEN AbsenceTypeId = 5 AND CompensationType = 'BasicTime' THEN 'HOITOVAPAA_PALKALLINEN'-- Palkallinen hoitovapaa
WHEN AbsenceTypeId = 3 AND CompensationType = 'BasicTime' THEN 'ISYYSVAPAA_PALKALLINEN'-- Palkallinen isyysvapaa 
WHEN AbsenceTypeId = 11 AND CompensationType = 'BasicTime' THEN 'KERTAUS_PALKALLINEN'-- Palkallinen kertausharjoitus
WHEN AbsenceTypeId = 27 AND CompensationType = 'BasicTime' THEN 'KUNTOUTUS_PALKALLINEN' -- Palkallinen kuntoutus
WHEN AbsenceTypeId = 18 AND CompensationType = 'BasicTime' THEN 'KUTSUNTA_PALKALLINEN' -- Palkallinen kutsuntatilaisuus
WHEN AbsenceTypeId = 13 AND CompensationType = 'BasicTime' THEN 'MUU_POISSAOLO_PALKALLINEN' -- Palkallinen muu poissaolo
WHEN AbsenceTypeId = 26 AND CompensationType = 'BasicTime' THEN 'MUUTTOVAPAA_PALKALLINEN' -- Palkallinen muuttovapaa
WHEN AbsenceTypeId = 452 AND CompensationType = 'BasicTime' THEN 'OMAISHOITOVAPAA_PALKALLINEN' -- Palkallinen omaishoitovapaa
WHEN AbsenceTypeId = 17 AND CompensationType = 'BasicTime' THEN 'OMAT50_60_JUHLAT_PALKALLINEN' -- Palkallinen oma 50- tai 60-vuotisjuhla
WHEN AbsenceTypeId = 16 AND CompensationType = 'BasicTime' THEN 'PARISUHTEEN_REKISTEROINTI_PALKALLINEN' -- Palkallinen omat vihkiäiset / parisuhteen rekisteröinti
WHEN AbsenceTypeId = 8 AND CompensationType = 'BasicTime' THEN 'OPINTOVAPAA_PALKALLINEN' -- Palkallinen opintovapaa
WHEN AbsenceTypeId = 22 AND CompensationType = 'BasicTime' THEN 'OSA_AIKAELAKE_PALKALLINEN' -- Palkallinen osa-aikaeläke
WHEN AbsenceTypeId = 31 AND CompensationType = 'BasicTime' THEN 'OSA-AIKAISUUS_PALKALLINEN' -- Palkallinen osa-aikaisuus
WHEN AbsenceTypeId = 14 AND CompensationType = 'BasicTime' THEN 'PERHEENJASENEN_KUOLEMA_PALKALLINEN' -- Palkallinen perheenjäsenen kuolema
WHEN AbsenceTypeId = 15 AND CompensationType = 'BasicTime' THEN 'HAUTAJAISET_PALKALLINEN' -- Palkallinen perheenjäsenen tai lähiomaisen hautajaiset
WHEN AbsenceTypeId = 20 AND CompensationType = 'BasicTime' THEN 'PERHEENJASENEN_SAIRASTUMINEN_PALKALLINEN' -- Palkallinen perheenjäsenen äkillinen sairastuminen
WHEN AbsenceTypeId = 7 AND CompensationType = 'BasicTime' THEN 'PAKOTTAVAT_PERHESYYT_PALKALLINEN' --Palkallinen poissaolo pakottavien perhesyiden takia
WHEN AbsenceTypeId = 450 AND CompensationType = 'BasicTime' THEN 'RASKAUSVAPAA_PALKALLINEN' --Palkallinen raskausvapaa
WHEN AbsenceTypeId = 6 AND CompensationType = 'BasicTime' THEN 'TILAPAINEN_HOITOVAPAA_PALKALLINEN' -- Palkallinen tilapäinen hoitovapaa
WHEN AbsenceTypeId = 19 AND CompensationType = 'BasicTime' THEN 'TOIMIVAPAA_PALKALLINEN' -- Palkallinen toimivapaa
WHEN AbsenceTypeId = 12 AND CompensationType = 'BasicTime' THEN 'TYONTEKIJAN_PYYNTO_PALKALLINEN' -- Palkallinen työntekijän oma pyyntö
WHEN AbsenceTypeId = 451 AND CompensationType = 'BasicTime' THEN 'VANHEMPAINVAPAA_PALKALLINEN' -- Palkallinen vanhempainvapaa
WHEN AbsenceTypeId = 4 AND CompensationType = 'BasicTime' THEN 'VANHEMPAINVAPAA_PALKALLINEN'-- Palkallinen vanhempainvapaa
WHEN AbsenceTypeId = 23 AND CompensationType = 'BasicTime' THEN 'VIRKAPAVAA_PALKALLINEN' -- Palkallinen virkavapaa
WHEN AbsenceTypeId = 9 AND CompensationType = 'BasicTime' THEN 'VUOROTTELUVAPAA_PALKALLINEN' -- Palkallinen vuorotteluvapaa
WHEN AbsenceTypeId = 2 AND CompensationType = 'BasicTime' THEN 'AITIYSVAPAA_PALKALLINEN' -- Palkallinen äitiysvapaa



-- put below this line any customer specific absence events 



--put above this line any customer specific absence events 

WHEN AbsenceTypeId IS NOT NULL THEN ('Poissaolo: ' || AbsenceTypeId || ' - ' || CompensationType) --this row picks up all absence types set up by the customer without a specific salary code set


ELSE

'NOT DEFINED'

END AS OverrideSalaryCode


FROM UserSalaryData


WHERE CompensationType IN ('BasicTime','UnpaidBasicTime')

AND AbsenceTypeID IS NOT NULL

AND IsApproved = 'Y'

-- 

-- Alla oleva ehto jättää tuloksista pois ne päivät, joissa työaikaa on korvattu jonain muuna kuin poissaolona

AND

SalaryRenderingDate IN 

(SELECT SalaryRenderingDate 

FROM UserSalaryData 

WHERE AbsenceTypeId IS NULL 

AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime', 'AdditionalWork', 'OvertimeDaily50', 'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50', 'OvertimeSunday100', 'PublicHoliday', 'YearLeave')) 

--

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Viikkolepo, arkipyhä

Poimitaan viikkolepo, arkipyhä

SELECT *, SUM(Amount) As OverrideAmount,
CASE 
WHEN CompensationType = 'WeeklyRestTime' THEN 'VIIKKOLEPO'
WHEN CompensationType = 'PublicHoliday' THEN 'ARKIPYHÄ'
END AS OverrideSalaryCode

-- put below this line any changes to the columns queried / overrides

-- put above this line any changes to the columns queried / overrides

FROM UserSalaryData

WHERE CompensationType IN ('WeeklyRestTime', 'PublicHoliday')
AND IsApproved = 'Y'

GROUP BY OverrideSalaryCode, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

HAVING Overridesalarycode != 0

Varallaolo vähennettynä varallaoloaikana tehdyllä työllä

Alla oleva kysely poimii varallaoloajan. Varallaoloajan päälle kirjatut tapahtumat vähennetään varallaoloajasta, mutta järjestelmän asetusten kautta on mahdollista määrittää että tietyt tapahtumatyypit eivät vähennä varallaoloaikaa. Varallaolon asetuksista voit lukea lisää täältä 

Tarvittaessa voit lisätä kyselyyn ryhmittelyn (GROUP BY) ja määrän yhteenlaskun mikäli et halua jokaista yksittäistä kestoa ja ajanjaksoa. 

Huom. Tämä esimerkki muuttui järjestelmän 2.12.0 päivityksen myötä. Vanhentunut malli toimii edelleen mutta sen käyttämista uusille liittymille ei suositella. Vanhan mallin mukaisen esimerkin löydät täältä

SELECT *
FROM UserSalaryData
WHERE CompensationType = 'OnCall'

Aloitus- ja lopetustyö

SELECT *
FROM UserSalaryData
WHERE CompensationType = 'StartupWork'

SELECT *
FROM UserSalaryData
WHERE CompensationType = 'TerminationWork'

Muita esimerkkipoimintoja

Esimerkkipoiminnoissa on yksittäisiä esimerkkejä erilaisiin erikoistilanteisiin. Näitä esimerkkejä voi soveltaa mallissa, mikäli asiakaskohtainen toteutus vaatii esimerkissä kuvatun kaltaisia poikkeuksia.

Poimitaan työaika siten, että muodostetaan eri kustannuspaikan aineisto riippuen siitä päivästä milloin aineisto muodostetaan

Where  - ehdossa on kuvattu että 6. päivä poimitaan kustannuspaikan 10 henkilöt ja 14. päivä poimitaan kustannuspaikan 20 henkilöt.

SELECT *, SUM(Amount) As OverrideAmount

FROM UserSalaryData

WHERE CompensationType = 'BasicTime'

AND CASE --in here define the list of day numbers and the corresponding UserCostGroup we wish to pick 
WHEN strftime('%d', 'now') = '06' THEN UserCostGroup = '10' 
WHEN strftime('%d','now') = '14' THEN UserCostGroup = '20'
END


GROUP BY SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment

Poimitaan kertymän arvo kuukauden viimeisenä päivänä

Where  - ehdossa on kuvattu, että laskentapäivän tulee olla kuukauden viimeinen päivä. Vain silloin muodostuu tulos, johon poimitaan saldo-kertymän arvo.

SELECT *, SUM(Amount) As OverrideAmount

FROM UserSalaryData

WHERE 
CompensationType = 'Accrual'
AND AccrualType = 'Balance'
AND SalaryRenderingDate = date(salaryrenderingDate, 'start of month', '+1 month', '-1 day') -- this rule calculates the last day of the month
GROUP BY SalaryRenderingDate

Poimitaan henkilöryhmät mukaan aineistoon

Where  - ehdossa on kuvattu että poimitaan henkilöryhmät A100, B100, C100, D100.

SELECT ...,
IFNULL(
  (
    SELECT ui.Value FROM UserInfoData ui
    WHERE ui.InfoTypeName = 'UserGroupCode_SDSQL' AND
    datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
    LIMIT 1
  ),
  ''
) AS _UserGroupCode,
FROM UserSalaryData
WHERE _UserGroupCode IN ('A100', 'B100', 'C100', 'D100')

Poimitaan määräksi se tieto kuinka monena päivänä henkilö on tehnyt säännöllistä työaikaa tai ylitöitä

SELECT *, COUNT(DISTINCT(SalaryRenderingDate)) * 3600 As OverrideAmount -- kerrotaan tulos arvolla 3600 jotta liittymässä 1 päivä = 1 tunti (3600 sekuntia)

FROM UserSalaryData

WHERE CompensationType IN ('BasicTime', 'AdditionalWork', 'OvertimeDaily50', 
'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50',
'OvertimeSunday100')
AND BasePartTarget IN ('salary','bank','balance','flexiblehours')
AND ActivityTypeCode = '1'

GROUP BY UserId HAVING OverrideAmount > 0

Poimitaan tietoa siten, että projektikoodiksi voidaan määritellä arvo perustuen työlisän numeroon

SELECT *, SUM(amount) AS OverrideAmount,
 CASE 
    WHEN WorkIncrementNumber = 10 THEN '5000'
    WHEN WorkIncrementNumber = 30 THEN '6000'
    WHEN WorkIncrementNumber = 50 THEN '7000'
 ELSE
'0'
 )
 END AS OverrideProjectCode1

FROM UserSalaryData WHERE ActivityTypeCode = 13

Poimitaan tietoa siten, että projektikoodiksi määritellään projektin ulkoinen tunniste

SELECT *, SUM(amount) AS OverrideAmount,
 ProjectExternalIdentifier1 AS OverrideProjectCode1

FROM UserSalaryData WHERE ActivityTypeCode = 13

Poimitaan 'saldon muutos' tapahtumat ja siirretään arvona muutoksen itseisarvo

SELECT *, ABS(amount) AS OverrideAmount

FROM UserEventData WHERE ActivityTypeCode = '13'

Poimitaan matkalasku (myös muut työlisät poimitaan, ellei WorkIncrementType-kohtaa päivitetä, kts. alla)

SELECT 
 *, 
 SUM(amount) as OverrideAmount, 
 WorkIncrementCode as OverrideSalaryCode -- Use the salary code from the Työlisät settings
FROM 
 UserSalaryData 
WHERE 
 CompensationType = 'WorkIncrement' 
 AND WorkIncrementCode IS NOT NULL -- Export all supplements unless they have been explicitly set not to be exported 
 AND WorkIncrementType = ??? -- If you ONLY want travel invoice supplements you can specify the types you wish here
GROUP BY 
 SalaryRenderingDate, 
 Workincrementcode, 
 UnitType -- Ensures that if the supplement type changes, the old and new values aren't summed together

Poimitaan kertymät

SELECT *, Amount / 60 AS OverrideAmount

FROM UserSalaryData
WHERE compensationType = 'Accrual'
AND AccrualType = 'balance' --Tässä poimitaan ainoastaan Saldo

Poimitaan Saldo-kertymän leikattu määrä

Tässä säännössä poimitaan Saldo-kertymän maksimäärän vuoksi henkilön Saldo-kertymästä leikattu määrä. 

SELECT 
usd.*, 
usd.amount + COALESCE(usd2.sumAmount, 0) AS OverrideAmount 
FROM 
UserSalaryData usd 
LEFT JOIN (
SELECT 
salaryRenderingDate, 
COALESCE(
SUM(
CASE WHEN compensationType = 'BasicTime' 
AND basePartTarget = 'balance' 
AND overtimeTarget = 'uncompensated' THEN - amount ELSE amount END
), 
0
) AS SumAmount 
FROM 
UserSalaryData 
WHERE 
compensationType = 'BasicTime' 
AND isTimeBorrowedFromBalance = 'Y' 
AND ActivityTypeId IS NULL 
OR (
compensationType = 'BasicTime' 
AND basePartTarget = 'balance' 
AND overtimeTarget = 'uncompensated'
) 
GROUP BY 
salaryRenderingDate
) usd2 ON usd.salaryRenderingDate = usd2.salaryRenderingDate 
WHERE 
usd.compensationType = 'AccrualChange' 
AND usd.accrualType = 'Balance' 
GROUP BY 
usd.accrualType

Poimitaan lomarahavapaa myös lauantailta, jolla ei työvelvoitetta

Tässä säännössä poimitaan poikkeuksellisesti merkittyä työaikaa (''MarkedTime'') päivinä. Sääntöön on tehty myös poikkeus, että sunnuntaita ei oteta mukaan lukuun eli aineistolle nousee ainoastaan päivät maanantaista lauantaihin. 

SELECT 
  *, 
  COUNT(
    DISTINCT(SalaryRenderingDate)
  ) AS OverrideAmount, 
  'J' AS OverrideUnitType 
FROM 
  UserSalaryData 
WHERE 
  ActivityTypeCode = 164 --Lomarahavapaa-tapahtumatyypin koodi
  AND CompensationType = 'MarkedTime' 
  AND Weekday(StartDateTime) != 6  
GROUP BY 
  userid, 
  ProjectCode1,
  salaryrenderingdate 

Poimitaan työpäivät

Poimitaan määräksi summa niistä palkkapäivistä, joille on syntynyt korvattavaa aikaa. Palkkalajiksi muodostuu arvo '1' silloin kun käsiteltävä päivä on samalla kuukaudella kuin käsiteltävän jakson viimeinen päivä. Muulloin palkkalajiksi muodostuu arvo '2'.

SELECT (  
 CASE   
   WHEN (  SELECT  strftime('%m', MAX(salaryrenderingdate))
           FROM UserSalaryData ) = strftime('%m', salaryrenderingdate) THEN 1 
   ELSE 2 END) as OverrideSalaryCode, 
*, 
COUNT(DISTINCT(SalaryRenderingDate)) As OverrideAmount

FROM UserSalaryData
WHERE compensationtype IN ('BasicTime', 'AdditionalWork', 'OvertimeDaily50',
'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100')
AND BasePartTarget IN ('salary','bank','balance','flexiblehours')
AND EmployeeSalaryType = 'hourly' 
group by overridesalarycode

Poimitaan matkalaskun matkojen tietoja

Poiminnassa oleva char(10) on rivinvaihtomerkki LF.

SELECT e.*, 
  t.StartDateTime AS OverrideStartDatetime, 
  t.EndDateTime AS OverrideEndDatetime, 
  t.StartLocation || ' - ' || t.EndLocation || char(10) || t.ReasonForTrip AS OverrideComment 
FROM UserEventData e 
JOIN UserTravelExpenseTripData t ON (t.ActivityId = e.ActivityId) 
ORDER BY e.StartDateTime, t.TripNumber