SA-20358

Summary

The usage summary report is not working after Pega 7.1.5 migration. The required agents are running fine, however the usage summary reports are not correct.

Error Messages

Not Applicable

Steps to Reproduce

Rule usage summary report from System > Tools

Root Cause

A defect in Pegasystems’ code or rules.  There was a bug in the function that aggregated the daily data to the monthly data.

Resolution

Provided corrected SQL function to properly populated the monthly data from the daily data.

Ensure the following registers are set:

currentSchema=[data schema name];
currentFunctionPath=SYSISM,SYSFUN,[data schema name];

Set the command delimiter to "@".

Replace "prpcdataschema" in the script below with your local data schema name.

=================
create or replace procedure prpcdataschema.sppr_aggregate_usage
(  )
 DYNAMIC RESULT SETS 10
 LANGUAGE SQL
--
-- $Id$
--
-- Copyright (c) 2001  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--
--
-- Name: sppr_aggregate_usage
--
-- Aggregate usage data
--
--
                                    
P1: BEGIN

DECLARE peakHour timestamp;
DECLARE peakHourUsers int;
DECLARE pevPeakHour timestamp;
DECLARE pevPeakHourUsers int;

/* Generic SQL Stmt buffers */
DECLARE sqlStmt varchar(5000);
DECLARE periodSql varchar(1024);
DECLARE periodEndSql varchar(1024);
DECLARE periodHourSql varchar(1024);
DECLARE periodSqlpr varchar(1024);
DECLARE periodCalcEnd varchar(1024);
/*Variables for summary calculations */
DECLARE processPeriod varchar(32);
DECLARE regUserMaxDailyHours int;
DECLARE regUserMinDailyHours int;
DECLARE regUserMinPeriodHours int;
DECLARE occUserMaxDailyHours int;
DECLARE occUserMinDailyHours int;
DECLARE occUserMinPeriodHours int;
DECLARE occUserMaxPeriodHours int;
DECLARE sprUserMaxPeriodHours int;
DECLARE interactionsNamedUsers INT;
DECLARE invocationsNamedUsers INT;
DECLARE interactionsServices INT;
DECLARE invocationsServices INT;
DECLARE interactionsWebUsers INT;
DECLARE invocationsWebUsers INT;
DECLARE ruleUserRegular INT;
/* Set the first day of the week if needed */
DECLARE firstDayOfWeek INT;
DECLARE workingPeriod timestamp;
DECLARE retainDaily INT;
DECLARE retainHourly INT;
DECLARE purgeTableName varchar(32);
DECLARE purgeFieldName varchar(32);


DECLARE modifiedRulesClause varchar(512);
DECLARE modifiedRulesNotClause varchar(512);
DECLARE modifiedRulesNotClause1 varchar(512);
DECLARE cur_error varchar(128);
DECLARE v_stmt STATEMENT;
DECLARE c1 CURSOR FOR v_stmt;
DECLARE EXIT HANDLER FOR SQLSTATE '02000'
BEGIN
        RESIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = cur_error;
END;

SET cur_error = 'No data found for variable :pyMeasurementPeriod';
select  pyvalue INTO processPeriod from prpcdataschema.PR_LICENSE_PARAMETERS where pyName = 'pyMeasurementPeriod' and pyUserType = 'SYSTEM';

  SET cur_error = 'No data found for variable :pyMaxHoursPerDay:occasional';
  SELECT CAST(pyValue AS INTEGER) INTO occUserMaxDailyHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'occasional' and pyName = 'pyMaxHoursPerDay';
  SET cur_error = 'No data found for variable :pyMinHoursPerDay:occasional';
  SELECT CAST(pyValue AS INTEGER) INTO occUserMinDailyHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'occasional' and pyName = 'pyMinHoursPerDay';
  SET cur_error = 'No data found for variable :pyMinHoursPerPeriod:occasional';
  SELECT CAST(pyValue AS INTEGER) INTO occUserMinPeriodHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'occasional' and pyName = 'pyMinHoursPerPeriod';
  SET cur_error = 'No data found for variable :pyMaxHoursPerPeriod:occasional';
  SELECT CAST(pyValue AS INTEGER) INTO occUserMaxPeriodHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'occasional' and pyName = 'pyMaxHoursPerPeriod';
  SET cur_error = 'No data found for variable :pyMaxHoursPerPeriod:sporadic';
  SELECT CAST(pyValue AS INTEGER) INTO sprUserMaxPeriodHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'sporadic' and pyName = 'pyMaxHoursPerPeriod';
  SET cur_error = 'No data found for variable :pyMinHoursPerDay:Regular';
  SELECT CAST(pyValue AS INTEGER) INTO regUserMinDailyHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'regular' and pyName = 'pyMinHoursPerDay';
  SET cur_error = 'No data found for variable :pyMinHoursPerPeriod:Regular';
  SELECT CAST(pyValue AS INTEGER) INTO regUserMinPeriodHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'regular' and pyName = 'pyMinHoursPerPeriod';
  SET cur_error = 'No data found for variable :pyMaxHoursPerDay:Regular';
  SELECT CAST(pyValue AS INTEGER) INTO regUserMaxDailyHours FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'regular' and pyName = 'pyMaxHoursPerDay';
  SET cur_error = 'No data found for variable :pyRuleUserForcesRegular';
  SELECT CAST(pyValue AS INTEGER) INTO ruleUserRegular FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'SYSTEM' and pyName = 'pyRuleUserForcesRegular';
    SET cur_error = 'No data found for variable :pyRetainDaily';
  SELECT CAST(pyValue AS INTEGER) INTO retainDaily FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'SYSTEM' and pyName = 'pyRetainDaily';
  SET cur_error = 'No data found for variable :pyRetainHourly';
  SELECT CAST(pyValue AS INTEGER) INTO retainHourly FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'SYSTEM' and pyName = 'pyRetainHourly';

/* Bug in the setup of license parameters - hence fix and backup calculation here */
/* Note ordering of the IF statements below is important - DONOT CHANGE - Anil */
  IF sprUserMaxPeriodHours <> 0 AND sprUserMaxPeriodHours >= regUserMinPeriodHours  THEN
     SET regUserMinPeriodHours = sprUserMaxPeriodHours+1;
  END IF;
  IF occUserMaxPeriodHours <> 0 AND occUserMaxPeriodHours <> 9000 AND occUserMaxPeriodHours >= regUserMinPeriodHours THEN
     SET regUserMinPeriodHours = occUserMaxPeriodHours+1;
  END IF;
  IF occUserMaxPeriodHours = 0 THEN
       SET occUserMaxPeriodHours = 9000;
  END IF;
  /* End ordering of IF statements and licence UI Bug fix */

/* Convert processPeriod to the SQL Server DATEPART equivalent */
  CASE processPeriod
  WHEN 'W' THEN      /* For weekly periods */
        select CAST(pyValue AS INTEGER) INTO firstDayOfWeek FROM prpcdataschema.PR_LICENSE_PARAMETERS where pyUserType = 'SYSTEM' and pyName = 'pyFirstDayOfWeek' ;
        set periodSql = 'pzUsageDay - (DAYOFWEEK(pzUsageDay)-2+' || CHAR(firstdayOfWeek) || ') DAY - MIDNIGHT_SECONDS (pzUsageDay) SECONDS ';
        set periodEndSql = 'pzUsageDay - (DAYOFWEEK(pzUsageDay)-6-' || CHAR(firstdayOfWeek) || ') DAY - MIDNIGHT_SECONDS (pzUsageDay) SECONDS + 86399 SECONDS';
        set periodHourSql = 'pzPeriodEnding - (DAYOFWEEK(pzPeriodEnding)-2+' || CHAR(firstdayOfWeek) || ') DAY - MIDNIGHT_SECONDS (pzPeriodEnding) SECONDS ';
        set retainDaily = 7 + retainDaily;
  WHEN  'M' THEN
        set periodSql = ' pzUsageDay-(day(pzUsageDay)-1) days - MIDNIGHT_SECONDS(pzUsageDay) SECONDS ';
        set periodEndSql =  ' pzUsageDay-(day(pzUsageDay)-1) days - MIDNIGHT_SECONDS(pzUsageDay) SECONDS + 1 MONTH - 1 DAY +86399 seconds ';
        set periodHourSql = ' pzPeriodEnding-(day(pzPeriodEnding)-1) days - MIDNIGHT_SECONDS(pzPeriodEnding) SECONDS ';
        set retainDaily = 31 + retainDaily;
  WHEN 'Q' THEN
        set periodSql = ' TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzUsageDay)))) || ''0101000000'') + ((QUARTER(pzUsageDay)-1)*3) MONTHS ' ;
        set periodEndSql =  ' TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzUsageDay)))) || ''0101000000'') + ((QUARTER(pzUsageDay)-1)*3) MONTHS + 3 MONTHS - 1 second';
        set periodHourSql = ' TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzPeriodEnding)))) || ''0101000000'') + ((QUARTER(pzPeriodEnding)-1)*3) MONTHS ';
        set retainDaily = 92 + retainDaily;
  WHEN 'Y' THEN
        set periodSql = ' TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzUsageDay)))) || ''0101000000'') + 86399 seconds ';
        set periodEndSql =  '  TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzUsageDay)))) || ''0101000000'') + 1 YEAR - 1 second ';
        set periodHourSql = ' TIMESTAMP(LTRIM(RTRIM(CHAR(YEAR(pzPeriodEnding)))) || ''0101000000'') + 86399 seconds ';
        set retainDaily = 366 + retainDaily;
  END CASE;


/* Find the min timestamp between the end of yesterday and the latest timestamp aready inserted in the db, if it does not exist
   then no work need to be processed set it for a day in the future I think */
  set sqlStmt = ' SELECT min(usagePeriod)  from ( Select (' || periodSql || ') as usagePeriod ' ||
                '  FROM ( select pzPeriodEnding as pzUsageDay from prpcdataschema.pr_hourly_usage ) as S ' ||
                '  where ( pzUsageDay < (SELECT CURRENT TIMESTAMP - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS FROM SYSIBM.SYSDUMMY1 ) ) ' ||
                '        AND ( pzUsageDay > ' ||
                '        (select CASE WHEN max(pzUsageDay) IS NULL THEN TIMESTAMP(''19900101000000'') ELSE  max(pzUsageDay) END from prpcdataschema.pr_daily_usage ) ) ' ||
                '  group by (' || periodSql || ') ) AS R ' ;
   PREPARE v_stmt FROM sqlStmt;
   OPEN c1;
   FETCH c1 INTO workingPeriod;
   CLOSE c1;

   IF workingPeriod IS NULL THEN
      SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'No data to process';
      RETURN;
   END IF;

 /* Now insert daily rows into the daily usage table for all new rows in the hourly prior to today */
   INSERT INTO prpcdataschema.PR_DAILY_USAGE (SELECT h.pzPeriodEnding - MIDNIGHT_SECONDS(h.pzPeriodEnding) SECONDS + 86399 SECONDS as pzUsageDay,
       h.pzUserId as pzUserId, h.pzUserType as pzUsertype, max(h.pzMayCheckOutRules) as pzMayCheckOutRules,
       sum(h.pzModifiedRulesCount) as pzModifiedRulesCount, sum(h.pzInteractionCount) as pzInteractionCount,
       sum(h.pzInvocationCount) as pzInvocationCount, count(h.pzUserId) as pzUsageHoursCount,
       sum(h.pzNoInvocationUserCount) as pzNoInvocationUserCount, sum(h.pzNoInvocationFlowCount) as pzNoInvocationFlowCount,
       MAX(h.pzUserAdditionalInfo) as pzUserAdditionalInfo
   from (select  pzPeriodEnding - MINUTE(pzPeriodEnding) MINUTES - SECOND(pzPeriodEnding) SECONDS - MICROSECOND (pzPeriodEnding) MICROSECONDS + 59 MINUTES + 59 SECONDS  pzPeriodEnding, pzUserId, pzUserType, max(pzMayCheckOutRules) as pzMayCheckOutRules,
sum(pzModifiedRulesCount) as pzModifiedRulesCount, sum(pzInteractionCount) as pzInteractionCount,
sum(pzInvocationCount) as pzInvocationCount, sum(pzNoInvocationFlowCount) as pzNoInvocationFlowCount,
sum(pzNoInvocationUserCount) as pzNoInvocationUserCount, pzUserAdditionalInfo from prpcdataschema.pr_hourly_usage
group by pzPeriodEnding - MINUTE(pzPeriodEnding) MINUTES - SECOND(pzPeriodEnding) SECONDS - MICROSECOND (pzPeriodEnding) MICROSECONDS + 59 MINUTES + 59 SECONDS, pzUserId, pzUserType, pzUserAdditionalInfo) h
   GROUP BY pzUserId,pzUserType, (h.pzPeriodEnding - MIDNIGHT_SECONDS(h.pzPeriodEnding) SECONDS + 86399 SECONDS)
   HAVING  (h.pzPeriodEnding - MIDNIGHT_SECONDS(h.pzPeriodEnding) SECONDS + 86399 SECONDS) > (select CASE WHEN max(pzUsageDay) IS NULL THEN TIMESTAMP('19900101000000') ELSE max(pzUsageDay) END from prpcdataschema.pr_daily_usage )
       AND (h.pzPeriodEnding - MIDNIGHT_SECONDS(h.pzPeriodEnding) SECONDS + 86399 SECONDS) < (SELECT CURRENT TIMESTAMP - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS FROM SYSIBM.SYSDUMMY1));


P2: BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN
       set sqlStmt = '';
END;
   set sqlStmt = ' insert into prpcdataschema.pr_usage_summary ' ||
                 ' select  distinct ''' || processPeriod || ''' as pySummarytype, ' || periodSql || ' as PyPeriodStarts, ' || periodEndSql ||
                                 ' as PyPeriodEnds, CURRENT TIMESTAMP - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS - 1 SECONDS as pyPeriodAsOf, ' || periodSql ||
                                 ' PYPEAKHOUROFUSE, 0 PYPEAKHOURUSERS, ' || periodSql || ' PYPEAKDAYOFUSE,0 PYPEAKDAYUSERS,0 PYDISTINCTUSERSTOTAL, ' ||
                                 ' 0 PYDISTINCTUSERSSPORADIC, 0 PYDISTINCTUSERSOCCASIONAL,0 PYDISTINCTUSERSREGULAR,0 PYINTERACTIONSNAMEDUSERS, ' ||
                                 ' 0 PYINTERACTIONSWEBUSERS,0 PYINTERACTIONSSERVICES, 0 PYINVOCATIONSNAMEDUSERS,0 PYINVOCATIONSWEBUSERS, ' ||
                                 ' 0 PYINVOCATIONSSERVICES,0 PYNOINVOCATIONFLOWNAMEDUSERS,0 PYNOINVOCATIONUSERNAMEDUSERS, ' ||
                                 ' 0 PYNOINVOCATIONFLOWWEBUSERS,0 PYNOINVOCATIONUSERWEBUSERS,0 PYNOINVOCATIONFLOWSERVICES,0 PYNOINVOCATIONUSERSERVICES ' ||
                       ' from prpcdataschema.PR_DAILY_USAGE where ' || periodSql || '  > (SELECT CASE WHEN MAX(pyPeriodStarts) IS NULL THEN  TIMESTAMP(''19900101000000'') ELSE MAX(pyPeriodStarts) END from prpcdataschema.pr_usage_summary ) ';
   PREPARE s1 FROM sqlStmt;
   EXECUTE s1;
END P2;

   IF ruleUserRegular = 1 THEN
        set modifiedRulesClause = ' UNION SELECT distinct ' || periodSql || ' periodstart, pzUserId ' ||
                              '       FROM prpcdataschema.PR_DAILY_USAGE ' ||
                              '       WHERE (pzMayCheckOutRules = 1 OR pzModifiedRulesCount > 0) AND (pzUserType = ''H'' ' ||
                              '              AND ' || periodSql || ' >= TIMESTAMP_FORMAT(''' || VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS'') ) ';
    set modifiedRulesNotClause = ' AND pzMayCheckOutRules = 0 AND pzModifiedRulesCount = 0 ';
    set modifiedRulesNotClause1 = ' AND max(pzMayCheckOutRules) = 0 AND max(pzModifiedRulesCount) = 0 ';

   ELSE
    set modifiedRulesClause =   ' ';
    set modifiedRulesNotClause = ' ';
    set modifiedRulesNotClause1 = ' ';
   END IF;

   /* Now construct a string that represents the SQL to calculate the summary information
     Description: Each of the UNION sub clauses calculate the number of users for each type in the period.
                The UNIONED data is pivoted around so that you get one row for each period
                For Regular Users -     Equi-join the users who meet the hourly requirements for the period with
                    the users have days within the period with use greater than specified limits.
                    Also include those users who may have rule checkout privs or have checked out rules
                    Within that period.
        For Occassional Users - Equi-join the users who meet the hourly requirements for the period filtered
                    by only those users who are not rule developers for the period with the users
                    who meet the daily requirements for the period
        For Sporadic Users - Just get the list of users who never have more than the max hourly req
                     for the period


   */

   set sqlStmt = ' update prpcdataschema.pr_usage_summary  set (pyDistinctUsersRegular, pyDistinctUsersOccasional, pyDistinctUsersSporadic, pyDistinctUsersTotal) = ' ||
                    ' (select  max(regular) as pyDistinctUsersRegular, max(occasional) as pyDistinctUsersOccasional, max(sporadic) as pyDistinctUsersSporadic, ' ||
                             ' max(regular)+ max(occasional)+ max(sporadic) as pyDistinctUsersTotal' ||
                     ' FROM ( Select periodstart, count(pzuserid) as regular, 0 as occasional, 0 as sporadic ' ||
                            ' FROM (SELECT d.periodstart periodstart, d.pzUserId pzUserId   ' ||
                                  ' FROM (SELECT distinct ' || periodSql || ' periodstart, pzUserId ' ||
                                        ' FROM prpcdataschema.PR_DAILY_USAGE  ' ||
                                        ' WHERE pzUserType = ''H'' AND ' || periodSql || ' >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                                        ' GROUP BY pzuserid, '  || periodSql ||
                                        ' HAVING sum(pzUsageHoursCount) >= ' || CHAR(regUserMinPeriodHours) || ' ' || modifiedRulesNotClause1 ||
                                       ' ) p, ' ||
                                       ' (SELECT distinct ' || periodSql || ' periodstart, pzUserId ' ||
                                       '  FROM prpcdataschema.pr_daily_usage du ' ||
                                       '  WHERE pzUsageHoursCount >= '  || CHAR(regUserMinDailyHours) || ' and pzUsageHoursCount <= ' || CHAR(regUserMaxDailyHours) ||
                                              ' AND pzUserType = ''H'' '  || modifiedRulesNotClause || ' ) d ' ||
                                   ' WHERE p.periodstart = d.periodstart and  p.pzUserId=d.pzUserId ' || modifiedRulesClause || ' ) s GROUP BY periodstart ' ||
                                   ' UNION ' ||
                                   ' SELECT periodstart, 0 as regular, count(pzuserid) as occasional, 0 as sporadic  ' ||
                                   ' FROM (SELECT distinct d.periodstart, d.pzUserId ' ||
                                        '  FROM (SELECT distinct '  || periodSql || ' periodstart, pzUserId ' ||
                                               ' FROM prpcdataschema.PR_DAILY_USAGE where pzUserType = ''H'' GROUP BY pzuserid, ' || periodSql ||
                                               ' HAVING sum(pzUsageHoursCount) >= ' || CHAR(occUserMinPeriodHours) || ' and sum(pzUsageHoursCount) <=  ' || CHAR(occUserMaxPeriodHours) || ' ' || modifiedRulesNotClause1 ||
                                  ' ) p,  '   ||
                                   ' (SELECT distinct ' || periodSql || ' periodstart, pzUserId '   ||
                                   '  FROM prpcdataschema.pr_daily_usage du  ' ||
                                   '  WHERE pzUserType = ''H'' ' || modifiedRulesNotClause ||
                                   '  GROUP BY ' || periodSql || ', pzUserId ' ||
                                   '  HAVING min(pzUsageHoursCount) > ' || CHAR(occUserMinDailyHours) || ' and max( pzUsageHoursCount) <= ' || CHAR(occUserMaxDailyHours) ||        
                                   ' ) d  ' ||
                                ' WHERE p.periodstart = d.periodstart and  p.pzUserId=d.pzUserId ) s group by periodstart ' ||
                                ' UNION ' ||
                                ' SELECT periodstart, 0 as regular, 0 as occasional, count(pzuserid) as sporadic ' ||
                                ' FROM ( select ' || periodSql || ' periodstart, pzUserId  ' ||
                                      '  from prpcdataschema.PR_DAILY_USAGE WHERE pzUserType = ''H'' ' ||
                                      '  group by pzuserid, ' || periodSql ||
                                      '  having sum(pzUsageHoursCount) <= '  || CHAR(sprUserMaxPeriodHours) || ' ' || modifiedRulesNotClause1 || ')  s ' ||
                                ' GROUP BY periodstart ' ||
                                ' UNION ' ||
                                ' SELECT ' || periodSql || ' periodstart, 0 as regular, 0 as occasional, 0 as sporadic ' ||
                                ' FROM  prpcdataschema.PR_DAILY_USAGE ' ||
                                ' WHERE (pzUserType = ''W'' OR pzUserType = ''S'' OR pzUserType = ''A'') ' ||
                                ' GROUP BY ' || periodSql || ' )  t ' ||
                     '  group by periodstart having pyPeriodStarts = periodstart ) ' ||
                 ' where  pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ;
   PREPARE s2 FROM sqlStmt;
   EXECUTE s2;

   /* Now update the period rows with peak hours  */
  SET sqlStmt =  ' update prpcdataschema.pr_usage_summary  set (pypeakhourofuse, pypeakhourusers) = (select  min(h.pzPeriodEnding) maxhour, s.maxusers ' ||
                      ' from (select DISTINCT (' ||  periodHourSql || ') as periodStart, max(noofusers) as maxusers' ||
                      '       FROM ( select pzPeriodEnding, count(pzuserid) as noofusers  ' ||
                      '              from prpcdataschema.PR_HOURLY_USAGE where pzUserType = ''H'' and  pzPeriodEnding >TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                      '              group by pzPeriodEnding ' ||
                      '              UNION ' ||
                      '              select pyPeakHourOfUse as pzPeriodEnding, pyPeakHourUsers as noofusers from prpcdataschema.PR_USAGE_SUMMARY where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                      '        ) as T ' ||
                      '        group by ' || periodHourSql || ' ) s, '  ||
                      '        (select pzPeriodEnding, count(pzuserid) as noofusers ' ||
                      '         from prpcdataschema.PR_HOURLY_USAGE where pzUserType = ''H'' and  pzPeriodEnding > TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                      '         group by pzPeriodEnding ' ||
                      '         UNION ' ||
                      '         select pyPeakHourOfUse as pzPeriodEnding, pyPeakHourUsers as noofusers from prpcdataschema.PR_USAGE_SUMMARY where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                      '      ) h ' ||
                      '      where s.periodstart = ' || periodHourSql || '  and  maxusers=noofusers ' ||
                      '      group by s.periodStart, maxusers having pyperiodstarts=s.periodstart )' ||
                  ' where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ;

   PREPARE s3 FROM sqlStmt;
   EXECUTE s3;

   set sqlStmt = 'update prpcdataschema.pr_usage_summary  set (pypeakdayofuse, pypeakdayusers) = (select  min(h.pzusageDay) maxday, s.maxusers ' ||
                     ' from (select DISTINCT (' ||periodSql || ') periodStart, max(noofusers) as maxusers ' ||
                          '  FROM (select pzUsageDay, count(pzuserid) as noofusers ' ||
                                '  from prpcdataschema.PR_DAILY_USAGE where pzUserType = ''H'' and  pzUsageDay > TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                                '        group by pzUsageDay) as T  ' ||
                                '  group by ' || periodSql || ' ) s,  ' ||
                                ' (select pzUsageDay, count(pzuserid) as noofusers ' ||
                                '  from prpcdataschema.PR_DAILY_USAGE where pzUserType = ''H'' and  pzUsageDay >  TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ||
                                '  group by pzUsageDay) h ' ||
                                '  where s.periodstart = ' || periodSql || '  and  maxusers=noofusers ' ||
                                '  group by s.periodStart, maxusers having pyperiodstarts=s.periodstart)' ||
                      ' where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ' ;
    PREPARE s4 FROM sqlStmt;
    EXECUTE s4;

    set sqlStmt = 'Update prpcdataschema.pr_usage_summary set (pyInteractionsNamedUsers , pyInvocationsNamedUsers , pyNoInvocationFlowNamedUsers , pyNoInvocationUserNamedUsers ) = ' ||
                  '(Select  sum(du.pzInteractionCount) pyInteractionsNamedUsers, ' ||
          '         sum(du.pzInvocationCount) pyInvocationsNamedUsers, sum(du.pzNoInvocationFlowCount) pyNoInvocationFlowNamedUsers, ' ||
                  '        sum(du.pzNoInvocationUserCount) pyNoInvocationUserNamedUsers  ' ||
          ' from prpcdataschema.pr_daily_usage du ' ||
                 ' where  du.pzUserType = ''H'' ' ||
                 ' group by (' || periodSql ||  ') ' ||
                  '  having pyPeriodStarts = ' || periodSql || ')' ||
                  ' where pyPeriodStarts >=  TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ';
     PREPARE s5 FROM sqlStmt;
     EXECUTE s5;


     set sqlStmt = 'Update prpcdataschema.pr_usage_summary set (pyInteractionsServices , pyInvocationsServices , pyNoInvocationFlowServices , pyNoInvocationUserServices ) = ' ||
                   '(Select  sum(du.pzInteractionCount) pyInteractionsServices, ' ||
           '         sum(du.pzInvocationCount) pyInvocationsServices, sum(du.pzNoInvocationFlowCount) pyNoInvocationFlowServices, ' ||
           '         sum(du.pzNoInvocationUserCount) pyNoInvocationUserServices ' ||
           '   from prpcdataschema.pr_daily_usage du ' ||
                  '    where  du.pzUserType = ''S''  ' ||
                  '    group by (' || periodSql ||  ')  ' ||
                   '    having pyPeriodStarts = ' || periodSql || ')' ||
                   ' where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ';
     PREPARE s6 FROM sqlStmt;
     EXECUTE s6;

     set sqlStmt = 'Update prpcdataschema.pr_usage_summary set (pyInteractionsWebUsers , pyInvocationsWebUsers , pyNoInvocationFlowWebUsers , pyNoInvocationUserWebUsers ) = ' ||
                   '(Select  sum(du.pzInteractionCount) pyInteractionsWebUsers,  ' ||
           '         sum(du.pzInvocationCount) pyInvocationsWebUsers, sum(du.pzNoInvocationFlowCount) pyNoInvocationFlowWebUsers, ' ||
           '      sum(du.pzNoInvocationUserCount) pyNoInvocationUserWebUsers ' ||
           '   from prpcdataschema.pr_daily_usage du ' ||
                  '   where  du.pzUserType = ''A'' OR du.pzUserType = ''W'' ' ||
                  '   group by (' || periodSql ||  ') ' ||
                   '   having pyPeriodStarts = ' || periodSql || ')' ||
                   ' where pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ';
     PREPARE s7 FROM sqlStmt;
     EXECUTE s7;
     
     set sqlStmt = 'Update prpcdataschema.pr_usage_summary set pyPeriodAsOf = CURRENT TIMESTAMP - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS - 1 SECONDS ' ||
                   ' WHERE pyPeriodStarts >= TIMESTAMP_FORMAT(''' ||  VARCHAR_FORMAT(workingperiod,'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'') ';
    PREPARE s8 FROM sqlStmt;
    EXECUTE s8;
    
     set purgeTableName = 'pr_hourly_usage';
     set purgeFieldName = 'pzPeriodEnding';
     call prpcdataschema.sppr_purge_table(purgeTableName, purgeFieldName, retainhourly);
      set purgeTableName = 'pr_daily_usage';
     set purgeFieldName = 'pzUsageDay';
     call prpcdataschema.sppr_purge_table(purgeTableName, purgeFieldName, retaindaily);

END P1

@

truncate table prpcdataschema.pr_daily_usage
@
truncate table prpcdataschema.pr_usage_summary
@

call prpcdataschema.sppr_aggregate_usage()
@

commit
@
=================