PROSAGA码农传奇-Oracle-SQL报告

SQL报告 - 查询多个表

作者: 仙风道骨刘憨憨
发布时间: 2024-12-19 01:21:13 (1月前)
转自：

<div class =“post-text”itemprop =“text”>
  <P>
    像这样的东西可能会这样做，但是对于你的列名等有一些猜测：
  </p>
   <pre>
    <code>
      SELECT * 
FROM
  (
    SELECT
      to_char(DATELOGGED,'MON-YYYY') as Month, 
      COUNT(*) as Total_Calls,
      SUM(case when CLOSED is null then 1 else null end) as case_Open,
      COUNT(case CLOSED when 'Y' then 1 else null end) as case_Closed,
      SUM(case when EXTREF is null then 0 else 1 end) as case_Referred,
      round((COUNT(case SLA_MET when 'Y' then 1 else null end)/COUNT(case CLOSED when 'Y' then 1 else null end)*100),2) as percent_SLA_met
    FROM IT_SUPPORT_CALLS 
    GROUP BY to_char(DATELOGGED,'MON-YYYY')
  ) calls
  LEFT JOIN 
  (
    SELECT  
      SUBSTRING(START_TIME, 4) as down_month,
      SUM(extract(minute from DURATION) + 
        extract(hour from DURATION) * 60 +
        extract(day from DURATION) * 60 * 24
      ) || 'minutes' as total_down_mins 
    FROM IT_DOWNTIME 
    WHERE duration is not null
    GROUP BY SUBSTR(START_TIME, 4)
  ) downs
  ON calls.month = downs.down_month

</code>
  </pre>
  <P>
    将第一个查询的日期格式更改为MON-YYYY，使其与您声称的第二个查询（dd-mon-yyy）的varchar2日期格式保持一致，并将日期包含在当前删除日期，离开就在这个月
  </p>
</DIV>