PL-SQL链接Excel文件的时间求和问题

Question

我将尽我最大的努力彻底解释这个问题，因为我已经花了很多天试图找出解决这个问题的方法，但都无济于事。

我有一些引用Five9调用数据导出的excel报告模板。我必须定期从Five9应用程序中手动导出数据以刷新这些报表，因此我决定对导出进行编码，并将其直接链接到excel工作簿报表以便于刷新，从而提高流程的效率和自动化程度。

我有基本的T-SQL经验，但意识到我需要PL-SQL经验，因为这是一个基于Oracle的数据库。几天后，我想出了如何在PL-SQL中编写所需的导出代码，并将它们设置为链接的excel工作簿。但后来我遇到了一个问题...

我的报表依赖于能够对导出的时间数据求和，但是我的SUM公式现在不应该得到Zero，问题不是格式化列到时间的问题(h:mm:ss...yes也是我的第一个想法)。问题是，数据根本不被识别为时间，而是被识别为文本。这篇文章解释了我遇到的确切问题：http://theexceltrainer.co.uk/adding-up-times-in-excel-results-in-zero/

问题是它没有给我一个合理的变通方法。如果我使用文本到列的技巧，当我下次需要刷新报告时，当我刷新数据时，他们建议我不会坚持(时间将恢复为文本)。我编写导出代码的整个目的是使这个过程完全自动化，所以每次都必须进入并重新格式化(做文本到列的事情)，这完全违背了我的目的。

有没有其他的解决方法？有没有可能在代码中添加一些东西，让excel将时间识别为实际时间而不是文本？当我手动将数据导出为CSV file...but时，这个问题就不存在了。我不能(或者至少我不知道如何)设置也是CSV的PL-SQL链接excel文件。我不知所措...

我已经包含了我的代码，如果这有帮助的话：

SELECT 
CALL_DATE as "Date"
, CALL_TIME as "Time"
, CALL_TIMESTAMP as "Timestamp"
, RING_TIME as "Ring Time"
, DIAL_TIME as "Dial Time"
, CALL_TIME_2 as "Call Time"
, AFTER_CALL_WORK_TIME as "After Call Work Time"
, CAMPAIGN as "Campaign"
, CAMPAIGN_TYPE as "Campaign Type"
, CALL_TYPE as "Call Type"
, DISPOSITION as "Disposition"
, AGENT_GROUP as "Agent Group"
, AGENT_NAME as "Agent Name"
, SALESFORCE_ID as "Salesforce ID"

FROM Comp_DB.FIVE9_CALLS

WHERE 
AGENT_GROUP = 'Collections'
and TRUNC(CALL_DATE) BETWEEN '01-June-2017' and TRUNC(SYSDATE)

ORDER BY
CALL_DATE asc

RING_TIME、DIAL_TIME、CALL_TIME_2和AFTER_CALL_WORK_TIME是我需要能够求和的字段。它们已经输入了正确的“hh:mm:ss”格式，...but Excel仍然不能将它们识别为时间...

Answer 1

我可以建议将WHERE子句中的字符串替换为这样的内容吗？：

TRUNC(CALL_DATE) BETWEEN to_date('01-06-2017','dd-mm-yyyy') and TRUNC(SYSDATE)

这样就不会让Oracle引擎(希望)解析日期值。

现在，对于您的问题，也许您可以在查询中进行“数学运算”，而不是在Excel中进行，如下所示：

SELECT 
CALL_DATE as "Date"
, CALL_TIME as "Time"
, CALL_TIMESTAMP as "Timestamp"
, RING_TIME as "Ring Time"
, DIAL_TIME as "Dial Time"
, CALL_TIME_2 as "Call Time"
, AFTER_CALL_WORK_TIME as "After Call Work Time"
, CAMPAIGN as "Campaign"
, CAMPAIGN_TYPE as "Campaign Type"
, CALL_TYPE as "Call Type"
, DISPOSITION as "Disposition"
, AGENT_GROUP as "Agent Group"
, AGENT_NAME as "Agent Name"
, SALESFORCE_ID as "Salesforce ID"
, numtodsinterval(
         (SUBSTR(RING_TIME, 1, 2)*3600 + SUBSTR(RING_TIME, 4, 2)*60 + SUBSTR(RING_TIME, 7, 2))+
         (SUBSTR(DIAL_TIME, 1, 2)*3600 + SUBSTR(DIAL_TIME, 4, 2)*60 + SUBSTR(DIAL_TIME, 7, 2))+
         (SUBSTR(CALL_TIME_2, 1, 2)*3600 + SUBSTR(CALL_TIME_2, 4, 2)*60 + SUBSTR(CALL_TIME_2, 7, 2))+
         (SUBSTR(AFTER_CALL_WORK_TIME, 1, 2)*3600 + SUBSTR(AFTER_CALL_WORK_TIME, 4, 2)*60 + SUBSTR(AFTER_CALL_WORK_TIME, 7, 2))
         , 'SECOND') as "Total duration"
FROM Comp_DB.FIVE9_CALLS

WHERE 
AGENT_GROUP = 'Collections'
and TRUNC(CALL_DATE) BETWEEN to_date('01-06-2017','dd-mm-yyyy') and TRUNC(SYSDATE)

ORDER BY
CALL_DATE asc

它基本上从每个字段中细分时间元素(小时、分钟)，以秒为单位获取它们的表示形式，对所有所需的列进行相加，最后使用numtodsinterval将得到的秒数转换为“日期到秒”的间隔。

请注意，我从您的操作中假设字段

已拉入正确的"hh:mm:ss“格式

因此，substr函数按照预期的位置顺序提取出小时、分钟、秒的元素。

PS:我刚才描述的方法可以帮助你进行行级求和，而不是列级求和，请指定你真正需要的是哪一个，这样我们就可以提供更好的答案。