Jump to content
Changes to the Jaspersoft community edition download ×

IREPORT: operations with timestamp (query sql)


Go to solution Solved by marianol,

Recommended Posts

Hi,
in proc sql  for sas i have this query to calculate TIME_FOR_REPLY (operations with timestamp).

SELECT 

(CASE
WHEN Wcs_dashboard_interaction.END_TIMESTAMP is null AND Wcs_dashboard_interaction.DISPATCH_TIMESTAMP IS NULL AND Wcs_dashboard_interaction.START_TIMESTAMP IS NOT NULL THEN (MAX(Wcs_dashboard_session.END_TIMESTAMP)  - MIN(Wcs_dashboard_session.START_TIMESTAMP))
 WHEN Wcs_dashboard_interaction.END_TIMESTAMP is null AND Wcs_dashboard_interaction.DISPATCH_TIMESTAMP IS NULL AND Wcs_dashboard_interaction.START_TIMESTAMP IS NULL THEN (MAX(Wcs_dashboard_session.END_TIMESTAMP)  - MIN(Wcs_dashboard_session.START_TIMESTAMP))
 WHEN Wcs_dashboard_interaction.END_TIMESTAMP is NOT null AND Wcs_dashboard_interaction.DISPATCH_TIMESTAMP IS NOT NULL  THEN (MAX(Wcs_dashboard_interaction.DISPATCH_TIMESTAMP)  - MIN(Wcs_dashboard_session.START_TIMESTAMP))
 WHEN Wcs_dashboard_interaction.END_TIMESTAMP is null AND Wcs_dashboard_interaction.DISPATCH_TIMESTAMP IS NOT NULL      THEN (MAX(Wcs_dashboard_interaction.DISPATCH_TIMESTAMP)  - MIN(Wcs_dashboard_session.START_TIMESTAMP))
 WHEN Wcs_dashboard_interaction.END_TIMESTAMP is NOT null AND Wcs_dashboard_interaction.DISPATCH_TIMESTAMP IS NULL      THEN (MAX(Wcs_dashboard_session.END_TIMESTAMP)  - MIN(Wcs_dashboard_session.START_TIMESTAMP))
ELSE '00.00.00't
END) AS TIME_FOR_REPLY
 
FROM UNIBANK_ASYNC.Wcs_dashboard_session Wcs_dashboard_session 
LEFT JOIN UNIBANK_ASYNC.Wcs_dashboard_interaction  Wcs_dashboard_interaction ON Wcs_dashboard_session.WCS_SESSION_ID=Wcs_dashboard_interaction.WCS_SESSION_ID 
LEFT JOIN UNIBANK_ASYNC.Wcs_dashboard_escalation Wcs_dashboard_escalation ON Wcs_dashboard_session.WCS_SESSION_ID=Wcs_dashboard_interaction.WCS_SESSION_ID 
LEFT JOIN UNIBANK_ASYNC.Wcs_dashboard_hold_resume Wcs_dashboard_hold_resume ON Wcs_dashboard_session.WCS_SESSION_ID=Wcs_dashboard_hold_resume.WCS_SESSION_ID 
LEFT JOIN UNIBANK_ASYNC.Wcs_dashboard_share Wcs_dashboard_share ON Wcs_dashboard_session.WCS_SESSION_ID=Wcs_dashboard_share.WCS_SESSION_ID   
LEFT JOIN UNIBANK_ASYNC.Wcs_survey Wcs_survey ON Wcs_survey.EXTERNAL_ID=Wcs_dashboard_session.EXTERNAL_ID 
 
When i creat the query in ireport i don't konw what is the correct syntax:  '00.00.00't    (I would like that as a last case stament were enhanced by the timestamp "00:00:00")
 
Can you give me some help?
 
Thank's so much
 
Marcello.

 

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

  • Solution

Your question is probably more regarding SAS PROC SQL than iReport itself. I'm not to good with SaS SQL but I will give it a try and that may point you in the right direction. The timestamp format is a full date/time so the '00:00:00' format is not a match someting like '01JAN1900:00:00:00' will be more suitable.

On other hand you can keep the last ELSE statement to 'null' and then use in iReport in the field expresion add a condition to print what you need. i.e.
$F{TIME_FOR_REPLY}.equals(null) ? '00:00:00' : $F{TIME_FOR_REPLY}

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...