Hello,

I am working on populating a report that shows total number of Insert, Update and Delete counts by date and hour of the day.


Here is what the data looks like in the database


Date InsertCount UpdateCount DeleteCount
06/10/2019 04:23:33:156 0 23 5
06/10/2019 04:27:05:217 78 56 15
06/10/2019 08:35:15:356 57 0 131
06/10/2019 09:15:33:156 217 47 0
06/10/2019 09:20:52:057 146 5 62


I would like to show the sum of Insert update and delete count by date and hour:

Date InsertCount UpdateCount DeleteCount
06/10/2019 04 78 79 20
06/10/2019 08 57 0 131
06/10/2019 09 363 52 62


Which date function should I use to get this level of information?

I tried to use DATECVT function but I get an error.

Please help.

This message has been edited. Last edited by: FP Mod Chuck,

If you can edit the synonym remove the s from HMDYYs for both USAGE and ACTUAL.

If you aren't allowed to modify the synonym, then create a DEFINE INSERT_DATE/HMDYY=INSERT_DATE;

That was my first thought and this works but all it does is drops minutes seconds and milliseconds. I would also like for it to sum the count values by Date and Hour

DateHour/HMDYYH=OPERATION_LOG.DTTM ;

this is the result from the define above:

Date InsertCount UpdateCount DeleteCount
06/10/2019 04 0 23 5
06/10/2019 04 78 56 15
06/10/2019 08 57 0 131
06/10/2019 09 217 47 0
06/10/2019 09 146 5 62


I would like hour 04 and 09 to sum up the count for Insert update and delete

I'll have to see the TABLE request but if you have

SUM INSERT UPDATE DELETE
BY DateHour

you should get what you're asking for.

I already have those fields in SUM, that doesn't do the trick.

Do you have any NOPRINT fields in your request? I just tested this on my machine and it returns one row per hour.

DEFINE FILE SMT/operation_log
DateHour/HMDYYH = DTTM ;
current_time/HYYMDs=HGETC ( 8 , 'HYYMDs' ) ;
Last_Updated_Day_Diff/I9=HDIFF ( current_time , TB_OPERATION_LOG.DTTM , 'DAY' , 'D12.2' ) ;


SUM
INSERTCOUNT
UPDATECOUNT
DELETECOUNT
BY DateHour AS 'Date'
WHERE STOREDPROCEDURE EQ 'usp_Balance_T_Tables' OR 'usp_Balance_H_Tables' OR 'usp_Balance_B_Tables';
WHERE Last_Updated_Day_Diff LE 7;

Please use the code tag when posting sample code/data
Last icon on the ribbon that look like:

</>

Also share the entire valid code because now, what you've shared is not valid FOCUS code

 ENGINE INT CACHE SET ON


-*COMPONENT=Define_tb_operation_log
DEFINE FILE servicemanagement/tb_operation_log
 DateHour/HMDYYH=TB_OPERATION_LOG.TB_OPERATION_LOG.DTTM ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE servicemanagement/tb_operation_log
SUM SUM.TB_OPERATION_LOG.TB_OPERATION_LOG.INSERTCOUNT
SUM.TB_OPERATION_LOG.TB_OPERATION_LOG.UPDATECOUNT
SUM.TB_OPERATION_LOG.TB_OPERATION_LOG.DELETECOUNT
BY DateHour
WHERE TB_OPERATION_LOG.TB_OPERATION_LOG.STOREDPROCEDURE EQ 'usp_Balance_T_Tables' OR 'usp_Balance_H_Tables' OR 'usp_Balance_B_Tables';
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, $
ENDSTYLE
END

-RUN
 

I'm not sure if I understand why you have SUM SUM.INSERTCOUNT.

What happens if you change your code to SUM CNT.INSERTCOUNT?

quote:

Originally posted by BabakNYC:
I just tested this on my machine and it returns one row per hour.

Dang, that is a REALLY SLOW retrieval rate. Might want to optimize that adapter...