Clarity PPM - Queries to provide Time Slice ranges (Start and Finish Dates)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Product: Clarity PPM
Module: Time Slices

The objectives of this KB are as below:
1. The UI and the corresponding database tables do not readily provide the time windows of Time Slices defined in the system. The only readily available information are the date on which the time window starts (From Date) and the number of periods it stretches to (Number of Periods). So, a query is being provided below that can provide this information readily when run against the PPM schema

2. There are Time Slices that determine what the time windows are, for which data can be shown in the UI, in TSV attributes. There is a query being provided to readily obtain what these time windows are. A warning that is often encountered when we go past these time windows, of data display in the UI, is explained in this KB.

Environment

Latest Version: 15.8.0.

Resolution

1. Query to get time slice windows. Uncomment the WHERE clause and provide specific IDs if you have them.

Oracle SQL
-------------------------------------------------
SELECT
    id,
    request_name,
    from_date AS start_date,
    num_periods,
        CASE
            WHEN period = 0 THEN from_date + num_periods -- Daily
            WHEN period = 1 THEN from_date + ( num_periods * 7 ) -- Weekly
            WHEN period = 3 THEN add_months(from_date,num_periods) -- Monthly
            WHEN period = 6 THEN add_months(from_date,num_periods * 3) -- Quarterly
            WHEN period = 8 THEN add_months(from_date,num_periods) -- Annually BUT num_periods is in months
        END
    AS end_date
FROM
    prj_blb_slicerequests
/*WHERE
    id IN (
        10,6,55558
    )*/;

PostgreSQL
-------------------------------------------------
SELECT
    id,
    request_name,
    from_date AS start_date,
    num_periods,
        CASE
            WHEN period = 0 THEN from_date + num_periods * INTERVAL '1 day' -- Daily
            WHEN period = 1 THEN from_date + num_periods * INTERVAL '7 days' -- Weekly
            WHEN period = 3 THEN from_date + num_periods * INTERVAL '1 month' -- Monthly
            WHEN period = 6 THEN from_date + num_periods * INTERVAL '3 months' -- Quarterly
            WHEN period = 8 THEN from_date + num_periods * INTERVAL '1 month' -- Annually BUT num_periods is in months
        END
    AS end_date
FROM
    sc51678p.prj_blb_slicerequests
/*WHERE
    id IN (
        10,6,55558
    )*/;

2. Query to get the time windows of data that can be displayed in the UI:

Oracle SQL
-------------------------------------------------
SELECT DISTINCT
    period,
    start_date,
    end_date
FROM
    (
        SELECT
                CASE
                    WHEN request_name LIKE 'D_%' THEN 'Daily'
                    WHEN request_name LIKE 'M_%' THEN 'Monthly'
                    WHEN request_name LIKE 'W_%' THEN 'Weekly'
                END
            AS period,
            from_date start_date,
                CASE
                    WHEN request_name LIKE 'M_%' THEN add_months(from_date,num_periods)
                    WHEN request_name LIKE 'D_%' THEN from_date + num_periods
                    WHEN request_name LIKE 'W_%' THEN from_date + ( num_periods * 7 )
                END
            AS end_date
        FROM
            prj_blb_slicerequests
        WHERE
            request_name IN (
                'D_AVL','D_ALC','D_TEAMCUSG','D_ETC','D_ACT','D_BASE','D_TENT_ETC','D_HARDALC','D_CAP_ALC','D_CAP_ETC','D_CAP_HARDALC','D_REQCRV'
,'W_AVL','W_ALC','W_TEAMCUSG','W_ETC','W_ACT','W_BASE','W_TENT_ETC','W_HARDALC','W_CAP_ALC','W_CAP_ETC','W_CAP_HARDALC','W_REQCRV'
,'M_AVL','M_ALC','M_TEAMCUSG','M_ETC','M_ACT','M_BASE','M_TENT_ETC','M_HARDALC','M_CAP_ALC','M_CAP_ETC','M_CAP_HARDALC','M_REQCRV'
            )
        ORDER BY request_name
    );


PostgreSQL
-------------------------------------------------
SELECT DISTINCT
       CASE
              WHEN request_name LIKE 'D_%'
                     THEN 'Daily'
              WHEN request_name LIKE 'M_%'
                     THEN 'Monthly'
              WHEN request_name LIKE 'W_%'
                     THEN 'Weekly'
       END       AS period
     , from_date    start_date
     , CASE
              WHEN request_name LIKE 'M_%'
                     THEN from_date + num_periods * INTERVAL '1 month'
              WHEN request_name LIKE 'D_%'
                     THEN from_date + num_periods * INTERVAL '1 day'
              WHEN request_name LIKE 'W_%'
                     THEN from_date + num_periods * INTERVAL '7 days'
       END AS end_date
FROM
       sc51678p.prj_blb_slicerequests
WHERE
       request_name IN (
    'D_AVL','D_ALC','D_TEAMCUSG','D_ETC','D_ACT','D_BASE','D_TENT_ETC','D_HARDALC','D_CAP_ALC','D_CAP_ETC','D_CAP_HARDALC','D_REQCRV'
,'W_AVL','W_ALC','W_TEAMCUSG','W_ETC','W_ACT','W_BASE','W_TENT_ETC','W_HARDALC','W_CAP_ALC','W_CAP_ETC','W_CAP_HARDALC','W_REQCRV'
,'M_AVL','M_ALC','M_TEAMCUSG','M_ETC','M_ACT','M_BASE','M_TENT_ETC','M_HARDALC','M_CAP_ALC','M_CAP_ETC','M_CAP_HARDALC','M_REQCRV'
)
;