Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Drop Minutes, Seconds and Miliseconds from Date Timestamp
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Drop Minutes, Seconds and Miliseconds from Date Timestamp
 Login/Join
 
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: 28 | Registered: September 05, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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: 28 | Registered: September 05, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
I already have those fields in SUM, that doesn't do the trick.


WebFOCUS 8
Windows, All Outputs
 
Posts: 28 | Registered: September 05, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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: 28 | Registered: September 05, 2017Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 28 | Registered: September 05, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.