Artikkeliin on koottu tyypillisiä työajan poimintaan liittyviä esimerkkejä.
Löydät tietokantataulujen sarakekuvaukset täältä.
Tyypilliset poissaolojen poiminnan esimerkit ovat tässä artikkelissa, projektirakenteen tai projektien huomioiden poiminta tässä artikkelissa ja henkilötietojen poiminnan esimerkit tässä artikkelissa.
Poimitaan säännöllinen työaika
Poiminnassa huomioidaan työ -tapahtuma (activityTypeCode = '1'), joka on tehty 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 (yksittäinen sääntö jokaiselle työkorotukselle)
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 työkorotus siten, että palkkalaji on tallennettu työkorotuksen määrityksissä (yksi sääntö kaikille työkorotuksille)
SELECT *, SUM(Amount) AS OverrideAmount, workrisesalarycode as overridesalarycode FROM UserSalaryData WHERE CompensationType = 'WorkRise' AND workrisepaytarget = 'Salary' GROUP BY OverrideSalarycode, SalaryRenderingDate, ProjectCode1 HAVING Overridesalarycode != 0
Poimitaan työlisä
Poimitaan kaikki työlisät ja ohjataan palkkaljiksi muodostumaan työlisälle asetettu koodi. Ryhmitellään tulokset per työlisä, per päivä per projektikoodi1.
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 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.
FROM UserSalaryData as usd
JOIN UserData as ud ON (usd.userid = ud.userid)
WHERE
ud.UserGroupCode IN ('A100', 'B100', 'C100', 'D100')
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' Jos halutaan poimia TES-ylityö erikseen
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'
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 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' --Tässä poimitaan ainoastaan Saldo
Poimitaan varallaolo vähennettynä varallaoloaikana tehdyllä työllä
Alla olevassa esimerkissä tulee ennen kohtaa OverlapsUnderlyingActivityTypeIds määritellä ne activitytypecode arvot (tässä esimerkissä Työ-tapahtumatyyppi, activitytypecode = '1') , joiden kanssa varallaolon päällekkäisyys halutaan poimia. Poimittu päällekkäisyys vähennetään 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 = '1'
AND OverlapsUnderlyingActivityTypeIds LIKE '%,33,%'
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
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 sairaspoissaolot (tapahtuma h kk koko)
SELECT *, SUM(Amount) AS OverrideAmount, MIN(SalaryRenderingDate)
AS OverrideStartDateTime, MAX(SalaryRenderingDate) AS OverrideEndDateTime
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND ActivityTypeCode = 3
AND BasePartTarget = 'salary'
AND SickLeaveTypeId IN (3,4,5)
AND employeesalarytype = 'monthly'
AND SalaryRenderingDate NOT IN (SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE ActivityTypeCode != 3
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(SalaryRenderingDate)
AS OverrideStartDateTime, MAX(SalaryRenderingDate) AS OverrideEndDateTime
FROM UserSalaryData
WHERE CompensationType = 'BasicTime'
AND ActivityTypeCode = 3
AND BasePartTarget = 'salary'
AND SickLeaveTypeId IN (3,4,5)
AND employeesalarytype = 'monthly'
AND SalaryRenderingDate NOT IN (SELECT SalaryRenderingDate
FROM UserSalaryData
WHERE ActivityTypeCode != 3
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