Activities Statistics

Owned by Sigrid Kviteberg
Last updated: Aug 03, 2022 by Daria Chebotaieva
3 min read

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

Summary

The view gives statistic data base on activites.

Keywords

activities

Description

The view provides statistics based on activities. In this sample, we have statistics for all the 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 set up 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

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'