Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Drop Minutes, Seconds and Miliseconds from Date Timestamp

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Drop Minutes, Seconds and Miliseconds from Date Timestamp
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
Virtuoso
posted Hide Post
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;


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Silver Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Silver Member
posted Hide Post
I already have those fields in SUM, that doesn't do the trick.


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
Virtuoso
posted Hide Post
Do you have any NOPRINT fields in your request? I just tested this on my machine and it returns one row per hour.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Silver Member
posted Hide Post
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;


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
 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
 


WebFOCUS 8
Windows, All Outputs
 
Posts: 29 | Registered: September 05, 2017Report This Post
Virtuoso
posted Hide Post
I'm not sure if I understand why you have SUM SUM.INSERTCOUNT.

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
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... Big Grin Razzer Wink


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Drop Minutes, Seconds and Miliseconds from Date Timestamp

Copyright © 1996-2020 Information Builders