cello23 Posted August 8, 2013 Share Posted August 8, 2013 Hi,in proc sql for sas i have this query to calculate TIME_FOR_REPLY (operations with timestamp).SELECT (CASEWHEN 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'tEND) 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 More sharing options...
Solution marianol Posted August 8, 2013 Solution Share Posted August 8, 2013 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 More sharing options...
cello23 Posted August 12, 2013 Author Share Posted August 12, 2013 sorry, the question was not clear. my bad English. I solved using NULL. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now