This article describes how to make SQL statements used for statistics reporting in Analytical Views. 

Description

The customer have a need to report statistics based on activities. In this sample, the customer needs to report statistics for all Sick activities. 

Result

If setting up the correct date groupings, the customer can get an analytical view that can be filtered as needed.

In this sample the Analytical is giving a count of all Sick days defined in the group Sicklong or Sickshort, for the first Quarter of a selected year. 

In this sample the Analytical is giving a count of all Onboard days defined in the group Work for the first Quarter of a selected year. 

Solution

In the below statement each activity is divided up into giving one row for each date. So for an activity that spans from 01.03.2020-03.03.2020 the statement will return 3 lines. One for 01.03.2020, one for 02.03.2020 and one for 03.03.2020. We then add fields that will based on this can be setup for different Group Intervals in the Analytical code. 

Analytical Grouping Intervals

Grouping of Activities

In the Activity Categories we define the groups that are needed. 

On each activity we link it up to the correct group. 

SQL Statement

Onboard Statistics

select
p01.client Numorgid,
p01.Employmentstartdate,
p01.Employmentenddate,
dateadd(day, n.num-1, p03.datefrom) onbdate,
dateadd(day, n.num-1, p03.datefrom) onbYear,
dateadd(day, n.num-1, p03.datefrom) onbMonth,
dateadd(day, n.num-1, p03.datefrom) onbDay,
dateadd(day, n.num-1, p03.datefrom) nbDayOfWeek,
dateadd(day, n.num-1, p03.datefrom) onbQuarter,
1 as Count,
cat.Code as Category,
cat.Name as CategoryName,
p03.Code as Activity,
p03.PIN,
p03.Datefrom,
p03.Dateto,
p03.Days,
P01.Name,
Ves.name as Vessel,
cmp.Name as Company,
p01.Sex as Gender
from
pw001p03 p03
left join pw001p01 p01 on p01.pin=p03.pin
left join pworg cmp on cmp.numorgid=p01.client
left join pworg ves on ves.numorgid=p03.vessel
left join pw001c12 act on act.code=p03.code
left join pw001c19 cat on cat.code=act.category
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 act.options like '%S%' and dateto>'2020-12-31'

Sick Statistics

select

p01.Sex as Gender,
p01.client Numorgid,
p01.Employmentstartdate,
p01.Employmentenddate,
dateadd(day, n.num-1, p03.datefrom) sickdate,
dateadd(day, n.num-1, p03.datefrom) sickYear,
dateadd(day, n.num-1, p03.datefrom) sickMonth,
dateadd(day, n.num-1, p03.datefrom) sickDay,
dateadd(day, n.num-1, p03.datefrom) sickDayOfWeek,
dateadd(day, n.num-1, p03.datefrom) sickQuarter,
1 as Count,
cat.Code as Category,
cat.Name as CategoryName,
p03.Code as Activity,
p03.PIN,
p03.Datefrom,
p03.Dateto,
p03.Days,
P01.Name,
Ves.name as Vessel,
cmp.Name as Company
from
pw001p03 p03
left join pw001p01 p01 on p01.pin=p03.pin
left join pworg cmp on cmp.numorgid=p01.client
left join pworg ves on ves.numorgid=p03.vessel
left join pw001c12 act on act.code=p03.code
left join pw001c19 cat on cat.code=act.category
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 act.options like '%K%' and dateto>'2020-12-31'