This article describes how to setup an Analytical view to keep track of number of sick self-certifications

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