Artikkeliin on koottu tyypillisiä henkilötietojen poimintaan liittyviä esimerkkejä.
Löydät tietokantataulujen sarakekuvaukset täältä
Tyypilliset työajan poiminnan esimerkit ovat tässä artikkelissa, poissaolojen poiminnan esimerkit ovat tässä artikkelissa ja projektirakenteen tai projektien huomioiden poiminta tässä artikkelissa.
Liitetään hakuun henkilötiedot ja poimitaan henkilön nimi osaksi kommenttia.
SELECT usersalarydata.*,
userdata.Firstname || ' ' || userdata.Lastname AS OverrideComment
FROM UserSalaryData
LEFT JOIN UserData ON UserData.UserId = UserSalaryData.UserId
Käytetään haussa henkilötietoja.
Mitä tahansa tietokantatauluissa olevaa henkilön tietoa voidaan käyttää osana poimintasääntöä. Poimintasääntö voi käyttäytyä esimerkiksi niin, että palkka-aineistoon muodostuva palkkalaji muuttuu henkilötiedoissa olevan kentän mukaisesti.
UserInfo-taulu sisältää kaikki henkilön henkilötiedot voimassaoloaikoineen. Kun hakuehdossa tätä tietokantataulua niin on tärkeää poimia se arvo, joka on voimassa aineiston käsittelypäivänä. Jos voimassaolon tarkistusta ei tehdä, voi poiminnan tuloksena muodostua moninkertainen tulosten määrä. UserInfo-taulun kuvaus löytyy täältä.
Esimerkissä on kuvattu poiminta siten, että henkilön henkilöryhmän koodi ohjaa säännöllisestä työajasta muodostuvan palkkalajin koodia ja henkilöryhmän koodi muodostuu osaksi aineistoa.
SELECT UserSalaryData.*, SUM(UserSalaryData.amount) AS OverrideAmount,
UserInfoData.Value AS OverrideCustomData1, -- this adds the person group to a column in the exported material
CASE -- the case statement defines the salarycode to be used based on persons group code.
WHEN UserInfoData.InfoTypeName = 'RootUserGroupCode_SDSQL' AND UserInfoData.Value = 'Toimisto' THEN '100'
WHEN UserInfoData.InfoTypeName = 'RootUserGroupCode_SDSQL' AND UserInfoData.Value = 'Tuotanto' THEN '200'
ELSE
'NOT DEFINED'
END AS OverrideSalaryCode
FROM UserSalaryData
LEFT JOIN UserInfoData ON (
UserInfoData.UserId = UserSalaryData.UserId AND
UserInfoData.InfoTypeName = 'RootUserGroupCode_SDSQL' AND
datetime(UserSalaryData.SalaryRenderingDate) BETWEEN
UserInfoData.ValidFrom AND UserInfoData.ValidTo
)
WHERE UserSalaryData.compensationType = 'BasicTime'
GROUP BY UserSalaryData.compensationType,
UserSalaryData.salaryRenderingDate
Henkilön työsuhteen alkupäivän hakeminen päivämäärämuodossa UserInfoData -taulusta
Koneellista päivämäärämuotoa tarvitaan, jos halutaan käyttää automaattista palkka-aineistomallin mukaista päivämäärän muotoilua. UserInfo-taulun kuvaus löytyy täältä.
SELECT
CASE WHEN Value IS NULL OR Value = '' THEN NULL
ELSE
date(
substring_index(Value, '.', -1) || '-' ||
substr('0' || substring_index(substring_index(Value, '.', 2), '.', -1), -2, 2) ||
'-' || substr('0' || substring_index(Value, '.', 1), -2, 2)
)
END AS CustomData1
FROM UserInfoData
WHERE InfoTypeName = 'LastWorkingContractBeginDate'
Poimitaan henkilöryhmän puurakenne
Poimitaan henkilöryhmän puurakenne ja esitetään puurakenteen koodi henkilön kalleusluokka -tiedossa. Koodin osat erotellaan / -merkillä.
WITH RECURSIVE UserGroupRecursive (Id, Depth, Code, Name, ParentId, AssignedGroupId) AS (
SELECT Id, Depth, Code, Name, ParentId, Id AS AssignedGroupId
FROM UserGroupData
UNION ALL
SELECT g.Id, g.Depth, g.Code, g.Name, g.ParentId, r.AssignedGroupId
FROM UserGroupData g
JOIN UserGroupRecursive r ON (r.ParentId = g.Id)
WHERE r.Depth > g.Depth
)
SELECT s.*,
CASE WHEN g1.Code IS NULL THEN '' ELSE g1.Code END ||
CASE WHEN g2.Code IS NULL THEN '' ELSE '/' || g2.Code END ||
CASE WHEN g3.Code IS NULL THEN '' ELSE '/' || g3.Code END ||
CASE WHEN g4.Code IS NULL THEN '' ELSE '/' || g4.Code END AS OverrideUserCostGroup
FROM UserSalaryData s
LEFT JOIN UserInfoData i ON (
i.UserId = s.UserId AND i.InfoTypeName = 'UserGroupId_SDSQL' AND
datetime(s.SalaryRenderingDate) BETWEEN i.ValidFrom AND i.ValidTo
)
LEFT JOIN UserGroupRecursive g1 ON (CAST(i.Value AS INTEGER) = g1.AssignedGroupId AND g1.Depth = 0)
LEFT JOIN UserGroupRecursive g2 ON (CAST(i.Value AS INTEGER) = g2.AssignedGroupId AND g2.Depth = 1)
LEFT JOIN UserGroupRecursive g3 ON (CAST(i.Value AS INTEGER) = g3.AssignedGroupId AND g3.Depth = 2)
LEFT JOIN UserGroupRecursive g4 ON (CAST(i.Value AS INTEGER) = g4.AssignedGroupId AND g4.Depth = 3)
WHERE s.CompensationType = 'BasicTime'
Poimitaan henkilöryhmien ja projektien puurakenne
WITH RECURSIVE UserGroupRecursive (Id, Depth, Code, Name, ParentId, AssignedGroupId) AS (
SELECT Id, Depth, Code, Name, ParentId, Id AS AssignedGroupId
FROM UserGroupData
UNION ALL
SELECT g.Id, g.Depth, g.Code, g.Name, g.ParentId, r.AssignedGroupId
FROM UserGroupData g
JOIN UserGroupRecursive r ON (r.ParentId = g.Id)
WHERE r.Depth > g.Depth
), ProjectRecursive (Id, Depth, Code, Name, ExternalId, TypeInternalName, ParentId, AssignedProjectId)
AS (
SELECT Id, Depth, Code, Name, ExternalId, TypeInternalName, ParentId, Id AS AssignedProjectId
FROM ProjectData WHERE IsAssignedProject = 'Y'
UNION ALL
SELECT p.Id, p.Depth, p.Code, p.Name, p.ExternalId, p.TypeInternalName, p.ParentId, r.AssignedProjectId
FROM ProjectData p
JOIN ProjectRecursive r ON (r.ParentId = p.Id)
WHERE r.Depth > p.Depth
)
SELECT s.*,
CASE WHEN g1.Code IS NULL THEN '' ELSE g1.Code END ||
CASE WHEN g2.Code IS NULL THEN '' ELSE '/' || g2.Code END ||
CASE WHEN g3.Code IS NULL THEN '' ELSE '/' || g3.Code END ||
CASE WHEN g4.Code IS NULL THEN '' ELSE '/' || g4.Code END AS OverrideUserCostGroup,
p1.Id AS OverrideProjectId1,
p2.Id AS OverrideProjectId2,
p3.Id AS OverrideProjectId3,
p4.Id AS OverrideProjectId4,
p1.ExternalId AS OverrideProjectExternalIdentifier1,
p2.ExternalId AS OverrideProjectExternalIdentifier2,
p3.ExternalId AS OverrideProjectExternalIdentifier3,
p4.ExternalId AS OverrideProjectExternalIdentifier4,
p1.TypeInternalName AS OverrideProjectType1,
p2.TypeInternalName AS OverrideProjectType2,
p3.TypeInternalName AS OverrideProjectType3,
p4.TypeInternalName AS OverrideProjectType4,
p1.Code AS OverrideProjectCode1,
p2.Code AS OverrideProjectCode2,
p3.Code AS OverrideProjectCode3,
p4.Code AS OverrideProjectCode4,
p1.Name AS OverrideProjectName1,
p2.Name AS OverrideProjectName2,
p3.Name AS OverrideProjectName3,
p4.Name AS OverrideProjectName4
FROM UserSalaryData s
LEFT JOIN UserInfoData i ON (
i.UserId = s.UserId AND i.InfoTypeName = 'UserGroupId_SDSQL' AND
datetime(s.SalaryRenderingDate) BETWEEN i.ValidFrom AND i.ValidTo
)
LEFT JOIN UserGroupRecursive g1 ON (CAST(i.Value AS INTEGER) = g1.AssignedGroupId AND g1.Depth = 0)
LEFT JOIN UserGroupRecursive g2 ON (CAST(i.Value AS INTEGER) = g2.AssignedGroupId AND g2.Depth = 1)
LEFT JOIN UserGroupRecursive g3 ON (CAST(i.Value AS INTEGER) = g3.AssignedGroupId AND g3.Depth = 2)
LEFT JOIN UserGroupRecursive g4 ON (CAST(i.Value AS INTEGER) = g4.AssignedGroupId AND g4.Depth = 3)
LEFT JOIN ProjectRecursive p1 ON (s.ProjectId1 = p1.AssignedProjectId AND p1.Depth = 0)
LEFT JOIN ProjectRecursive p2 ON (s.ProjectId1 = p2.AssignedProjectId AND p2.Depth = 1)
LEFT JOIN ProjectRecursive p3 ON (s.ProjectId1 = p3.AssignedProjectId AND p3.Depth = 2)
LEFT JOIN ProjectRecursive p4 ON (s.ProjectId1 = p4.AssignedProjectId AND p4.Depth = 3)
WHERE s.CompensationType = 'BasicTime'
Poimitaan henkilöiden ulkoisten palveluiden tunnisteet vientiä varten.
SELECT*, uid.value as overrideintegrationid
FROM UserSalaryData as usd
LEFT JOIN UserInfoData AS uid ON usd.userId = uid.userId
AND datetime(UserSalaryData.SalaryRenderingDate) BETWEEN
UserInfoData.ValidFrom AND UserInfoData.ValidTo
WHERE uid.infotypename = 'IntegrationIdAastra' -- or IntegrationIdKieku or DefaultCostGroupCode etc..
-- AND usd.compensationtype = 'BasicTime'
-- AND jne
Poimitaan henkilöiden maksuryhmien maksuryhmän tunnisteet ja maksukaudet vientiä varten
SELECT
pg.*
FROM
UserSalaryData as usd
LEFT JOIN UserInfoData AS uid ON usd.UserId = uid.UserId
AND datetime(usd.SalaryRenderingDate) BETWEEN uid.ValidFrom
AND uid.ValidTo
LEFT JOIN PaymentGroupDateData as pg ON pg.NeptonPaymentGroupIdentifier = uid.Value
WHERE
uid.InfoTypeName = 'NeptonPaymentGroupIdentifier'
GROUP BY
pg.NeptonPaymentGroupDateIdentifier