/
Analytical Views - Sick-self Certification Overview
Analytical Views - Sick-self Certification Overview
- Sigrid Kviteberg
- Daria Chebotaieva
Owned by Sigrid Kviteberg
1 Description | 2 Sample | 3 Setup
Summary | The view gives statistic data base on sick self-certification activites |
|---|---|
Keywords | Sick Period, Activities |
Description
By Norwegian law, all employees are entitled to use 4 sick self-certifications with a maximum of 3 days per certificate during a calendar year.
The customers should add these as separate activities. To keep track of how many certificates a person has used for the year, and to view the statistics for the previous and current years the following Analytical view can be used.
Sample
Setup
SQL Statement
select
p01.PIN,
p01.NAME,
'1' as Count,
p03.Code,
c12.Text as 'Activity Name',
p03.Code+' '+c12.Text as Activity,
p03.Days as 'Total Days',
dateadd(day, n.num-1, p03.datefrom) selfsickdate,
dateadd(day, n.num-1, p03.datefrom) selfsickYear,
dateadd(day, n.num-1, p03.datefrom) selfsickMonth,
dateadd(day, n.num-1, p03.datefrom) selfsickDay,
dateadd(day, n.num-1, p03.datefrom) selfsickDayOfWeek,
dateadd(day, n.num-1, p03.datefrom) selfsickQuarter,
p03.Datefrom,
p03.Dateto,
p01.client Numorgid,
p01.Employmentstartdate,
p01.Employmentenddate
from pw001p03 p03
left join pw001p01 p01 on p01.pin=p03.pin
left join pw001c12 c12 on c12.code=p03.code
left join (select row_number() over (order by id) as num from syscolumns) n on dateadd(day, n.num-1, p03.datefrom) <= case when p03.dateto is null then getdate() else p03.dateto end
where p03.code in ('SICKS','SICKSV')Analytical Grouping Intervals
