Artikkeliin on koottu tyyppillisiä työajan poimintaan liittyviä esimerkkejä
Löydät tietokantataulujen sarakekuvaukset täältä
Tyypilliset poissaolojen poiminnan esimerkit ovat tässä artikkelissa ja projektirakenteen tai projektien huomioiden poiminta tässä artikkelissa.
Poimitaan säännöllinen työaika.
Poiminnassa huomioidaan työ -tapahtuma (activityTypeCode = '1'), joka on tehdy säännöllisenä työaikana (CompensationType = 'BasicTime'), joka korvataan palkkaan tai tunti tunnista kertymiin (BasePartTarget IN ('salary','bank','balance','flexiblehours'))
SELECT *, SUM(Amount) As OverrideAmount
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND BasePartTarget IN ('salary','bank','balance','flexiblehours')
AND (OvertimeTarget IS NULL
OR OvertimeTarget = 'uncompensated')
AND ActivityTypeCode = '1'
GROUP BY SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
Poimitaan korotus
SELECT *, SUM(Amount) AS OverrideAmount
FROM UserSalaryData
WHERE WorkRiseNumber IN (3) AND CompensationType = 'WorkRise'
AND workrisepaytarget = 'Salary'
GROUP BY WorkRiseNumber, SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
Poimitaan lisä
SELECT *, SUM(Amount) As OverrideAmount, WorkIncrementCode AS OverrideSalaryCode
FROM UserSalaryData WHERE CompensationType = 'WorkIncrement'
GROUP BY WorkIncrementNumber, SalaryRenderingDate, ProjectCode1,
Comment HAVING OverrideSalaryCode != 0
Poimitaan työaika siten, että muodostetaan eri maksuryhmän aineisto riippuen siitä päivästä milloin aineisto muodostetaan
Where - ehdossa on kuvattu että 6. päivä poimitaan maksuryhmän 10 henkilöt ja 14. päivä poimitaan maksuryhmän 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 50% vuorokautinen ylityö
SELECT *, SUM(Amount) As OverrideAmount
FROM UserSalaryData
WHERE CompensationType IN ('OvertimeDaily50')
AND Overtimetarget IN ('salary')
AND ActivityTypeCode = '1'
AND IsTesOvertime = 'N'
GROUP BY SalaryRenderingDate, ProjectCode1, ProjectCode2, ProjectCode3,
ProjectCode4, ProjectCode5, ProjectCode6, Comment
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)) As OverrideAmount
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'
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
Liitetään hakuun henkilötietoa sen voimassaolon mukaan.
UserInfo-taulu henkilötiedot voimassaoloaikoineen. On tärkeää huomioida voimassaolon tarkistus säännöllä, kuten esimerkissä on kuvattu. Jos voimassaolon tarkistusta ei tehdä muodostuu poiminnan tuloksena moninkerta palkkalajin määrää. UserInfo-taulun kuvaus löytyy täältä.
SELECT UserSalaryData.*, SUM(UserSalaryData.amount) AS OverrideAmount,
UserInfoData.Value AS OverrideCustomData1
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'
AND UserData.DefaultProjectId1 IS NOT NULL
GROUP BY UserSalaryData.compensationType,
UserSalaryData.salaryRenderingDate
Henkilön työsuhteen alkupäivän hakeminen koneellisessa 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 matkalasku (and other supplement values if you don't check WorkIncrementType)
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 viikkolepo
SELECT *, SUM(Amount) As OverrideAmount
FROM UserSalaryData
WHERE CompensationType = 'WeeklyRestTime'
GROUP BY SalaryRenderingDate, ProjectCode1
Poimitaan kertymät
SELECT *, Amount / 60 AS OverrideAmount
FROM UserSalaryData
WHERE compensationType = 'Accrual'
AND AccrualType = 'balance'
Poimitaan varallaolo vähennettynä varallaoloaikana tehdyllä työllä
Alla olevassa esimerkissä tulee kohtaan OverlapsUnderlyingActivityTypeIds määritellä ne id arvot, joiden kanssa varallaolon päällekkäisyys halutaan poimia. Poimittu varallaolon päällekkäisyys vähennetään tehdyn varallaolon määrästä.
SELECT OnCall.*, OnCall.SumAmount - IFNULL(OnCallWork.SumAmount, 0)
As OverrideAmount
FROM (SELECT *, SUM(Amount) SumAmount
FROM UserSalaryData
WHERE CompensationType = 'MarkedTime'
AND BasePartTarget IS NULL
AND IsApproved = 'N'
AND ActivityTypeCode = '33'
GROUP BY SalaryRenderingDate) OnCall
LEFT JOIN (SELECT *, SUM(Amount) SumAmount
FROM UserSalaryData
WHERE ActivityTypeCode = '33'
AND OverlapsUnderlyingActivityTypeIds LIKE '%,1005,%'
AND CompensationType IN ('BasicTime', 'AdditionalWork', 'OvertimeDaily50',
'OvertimeDaily100','OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50',
'OvertimeSunday100')
GROUP BY SalaryRenderingDate) OnCallWork
ON (OnCallWork.SalaryRenderingDate = OnCall.SalaryRenderingDate)
Poimitaan vuosiloma
SELECT *, COUNT(DISTINCT(SalaryRenderingDate))*(7.5*3600)
As OverrideAmount, MIN(StartDateTime)
AS OverrideStartDateTime, MAX(EndDateTime) AS OverrideEndDateTime
FROM UserSalaryData
WHERE ActivityTypeCode = '3'
AND CompensationType = 'YearLeave'
GROUP BY SalaryRenderingDate
Poimitaan työpäivät
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 ActivityTypeCategoryId=1
AND EmployeeSalaryType = 'hourly'
group by overridesalarycode
Poimitaan sairaspoissaolot (tapahtuma h kk koko)
SELECT *, SUM(Amount) AS OverrideAmount, MIN(StartDateTime)
AS OverrideStartDateTime, MAX(EndDateTime) AS OverrideEndDateTime
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND ActivityTypeInternalName = 'Sick Leave'
AND BasePartTarget = 'salary'
AND SickLeaveTypeId IN (3,4,5)
AND employeesalarytype = 'monthly'
AND SalaryRenderingDate NOT IN (SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE ActivityTypeInternalName != 'Sick Leave'
AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime',
'AdditionalWork', 'OvertimeDaily50', 'OvertimeDaily100', 'OvertimeWeekly50',
'OvertimeWeekly100', 'OvertimeSunday50', 'OvertimeSunday100', 'PublicHoliday',
'YearLeave'))
GROUP BY usercostgroup, userid, salaryrenderingdate
Poimitaan sairaspoissaolot (lapsi sairas palkallinen kk osa+)
SELECT *, SUM(Amount) AS OverrideAmount, MIN(StartDateTime)
AS OverrideStartDateTime, MAX(EndDateTime) AS OverrideEndDateTime
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND ActivityTypeInternalName = 'Sick Leave'
AND BasePartTarget = 'salary'
AND SickLeaveTypeId IN (3,4,5)
AND employeesalarytype = 'monthly'
AND SalaryRenderingDate NOT IN (SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE ActivityTypeInternalName != 'Sick Leave'
AND CompensationType IN ('BasicTime', 'ReducedRateTime', 'UnpaidBasicTime',
'AdditionalWork', 'OvertimeDaily50',
'OvertimeDaily100', 'OvertimeWeekly50', 'OvertimeWeekly100', 'OvertimeSunday50',
'OvertimeSunday100', 'PublicHoliday', 'YearLeave'))
GROUP BY usercostgroup, userid, salaryrenderingdate
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