Skip to main content
Question

Compare dates in the same column based on activity name

  • January 25, 2023
  • 4 replies
  • 6 views
F
Level 5

Hi all,

 

In my activity table I have two specific activities named subprocess start and subprocess end. Before, between and after this two activities there are other activities and I need to count how many happened before, during and after the subprocess.

Each activitiy has an eventtime, but I'm not able to compare them.

 

Approaches already tried (for now I'm focusing on activities happened BEFORE, since I thought that it would have been easy to add other case when for during and after scenarios afterwards:

 

(CASE WHEN DATEDIFF(ms, "ACTIVITY_TBL"."EVENTTIME",

CASE WHEN "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start'

THEN "ACTIVITY_TBL"."EVENTTIME" END) > 0

THEN '1' ELSE 0 END)

 

(CASE WHEN "ACTIVITY_TBL"."EVENTTIME" <

CASE WHEN "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start'

THEN "ACTIVITY_TBL"."EVENTTIME" END

THEN 1 ELSE 0 END)

 

Both are giving me 0 everywhere, even if I see that in the specific case that I'm using as example, there are activities before, during and after.

 

Where am I wrong here? Thanks

    4 replies

    abhishek.chatu14
    Level 11
    Forum|alt.badge.img+4

    If you are looking for eventime of an activity before and after you can use ACTIVITY_LEAD / ACTIVITY_LAG.

     

    CASE WHEN ""ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start' THEN

    ACTIVITY_LEAD ( "ACTIVITY_TBL"."EVENTTIME",1 ) END

     

    F
    Level 5

    That's actually not what I'm looking for, I've the eventtime of each activity, I need to count the activities happened before, during and after two specific activities, so before subprocess start, between start and end and after subprocess end. I don't think that activity lead or lag can actually help here

    janpeter.van.d
    Level 12
    Forum|alt.badge.img+26

    Hi @federico.spara,

     

    I think that your quest can be easily solved using the INDEX_ACTIVITY_ORDER function together with a PU_COUNT function to count the total number of activities. This function easily returns the position of that activity in the activity table. You can easilly do this using an OLAP table, having the Case ID as dimension column, and add three columns called 'before', 'during' and 'after'.

     

    # Activities before sub-process

    -- Get the first 'subprocess start' activity index available per case

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM") -1, "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')

    -- minus 1, since index 3 means that 2 preceded

     

    # Activities during sub-process

    -- detract index of start activity from end activity

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end') -

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')

     

    # Activities during sub-process

    -- detract end-activity index from total activities

    PU_COUNT(CASE_TABLE, "ACTIVITY_TBL"."ACTIVITY_NM") -

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end')

     

    Hope this helps.

     

    Kind regards,

    Jan-peter

    F
    Level 5

    Hi @federico.spara,

     

    I think that your quest can be easily solved using the INDEX_ACTIVITY_ORDER function together with a PU_COUNT function to count the total number of activities. This function easily returns the position of that activity in the activity table. You can easilly do this using an OLAP table, having the Case ID as dimension column, and add three columns called 'before', 'during' and 'after'.

     

    # Activities before sub-process

    -- Get the first 'subprocess start' activity index available per case

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM") -1, "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')

    -- minus 1, since index 3 means that 2 preceded

     

    # Activities during sub-process

    -- detract index of start activity from end activity

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end') -

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')

     

    # Activities during sub-process

    -- detract end-activity index from total activities

    PU_COUNT(CASE_TABLE, "ACTIVITY_TBL"."ACTIVITY_NM") -

    PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end')

     

    Hope this helps.

     

    Kind regards,

    Jan-peter

    That's a point, but unfortunately it's not working, since I'm aggregating on two other columns in order to count the occurences of specific activities. My activity table is something like this:

     

     

    ACTIVITY_NM COL1 COL2 EVENTTIME

    name1 in op 02/02/2020

    name2 in tel 02/02/2020

    name3 in op 03/02/2020

    subprocess start - - 04/02/2020

    name4 out tel 05/02/2020

    subprocess end - - 05/02/2020

    name4 out tel 06/02/2020

     

    Hence, my OLAP should be something like this:

    COL1 COL2 BEFORE DURING AFTER

    in op 2 0 0

    in tel 1 0 0

    out tel 0 1 1

    Community Code of ConductAccessibility statement