下面的查询会提取数据以形成“报告”,但它基本上只是按数字分组的行项目
企业社会责任代理。它所做的是正确和正确的,在查询后,我将结果另存为CSV。然而,
它只是给我个人记录,所以我必须将其放入excel并使用公式插入
“总计”行基本上总结了我需要总计的列。

此sql查询在php脚本中运行,但我不确定修改其中任何内容的最佳方法,以便
我可以为每个用户总计这些列。它必须以某种方式说“这是用户7200的记录的结尾
因此,请为用户7200插入总计行,然后转到用户7206'或与此相关的记录。

屏幕截图按原样显示报告,使用“ x”和空空格(而不是1或0),因此在我的excel公式中
我通常有一个计数中频“ x”变量。我希望我可以对此脚本执行类似的操作。查询和
屏幕截图如下。

这是小提琴:http://sqlfiddle.com/#!9/b6a568/1

小提琴的输出是精确查询的输出,唯一的区别是脚本将其放入csv。

我只是希望有一种方法可以为每个用户创建一个“总计”行,从而将“ x”的数量加起来
持续时间和保持时间的秒数。

我对如何重构它的任何建议都表示赞赏。

$result = mysqli_query($conn2,
    "SELECT
          FirstN
          , LastN
        , Extension
        , Recieved
        , Recieved_Known
        , Outbound
        , Outbound_Known
        , Missed_No_VM
        , Missed_VM
        , Missed_Known
        , Calling_Number
        , Called_Number
        , Start_Time
        , End_Time
        , Talk_Time_Seconds
        , Hold_Time_Seconds

        FROM (
          SELECT distinct
           firstn
           , lastn
           , c.extension
           , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved
           , case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Recieved_Known
           , CASE WHEN LEGTYPE1 = 1 then 'x' ELSE '' end  AS Outbound
           , case when  FINALLYCALLEDPARTYNO = kn.long_number then 'x' ELSE '' end as Outbound_Known
           , case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 'x' ELSE '' end as Missed_No_VM
           , case when finallycalledpartyno like '%oice%' then 'x' ELSE '' end as Missed_VM
           , case when legtype1 = 2 and ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as
            Missed_Known
           , a.CALLINGPARTYNO AS Calling_Number
           , a.FINALLYCALLEDPARTYNO AS Called_Number
           , b.starttime as Start_Time
           , b.endtime as End_Time
           , b.duration as Talk_Time_Seconds
           , a.holdtimesecs as Hold_Time_Seconds

          FROM ambition.session a
            INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
            right join jackson_id.users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
            LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
        left join ambition.known_numbers kn on a.finallycalledpartyno = kn.long_number
                WHERE a.ts >= '2017-12-07' -- curdate()
                and(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) OR a.finallycalledpartyno IN (select extension from ambition.ambition_users))
          ) x
          order by extension;") or die(mysqli_error( $conn2));


屏幕截图非常有效:

php - 在SQL查询中为CSV/Excel报告的用户创建总和/总行-LMLPHP

最佳答案

我不确定您如何唯一标识用户。我假设扩展名对于每个用户都是唯一的。基于此,我提出了以下查询:

SELECT
          Extension
        , Recieved
        , Recieved_Known
        , Outbound
        , Outbound_Known
        , Missed_No_VM
        , Missed_VM
        , Missed_Known
        , Talk_Time_Seconds
        , Hold_Time_Seconds

      FROM (
          SELECT distinct
           c.extension
           , sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Recieved
           , sum(case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as Recieved_Known
           , sum(CASE WHEN LEGTYPE1 = 1 then 1 ELSE 0 end)  AS Outbound
           , sum(case when  FINALLYCALLEDPARTYNO = kn.long_number then 1 ELSE 0 end) as Outbound_Known
           , sum(case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 1 ELSE 0 end) as Missed_No_VM
           , sum(case when finallycalledpartyno like '%oice%' then 1 ELSE 0 end) as Missed_VM
           , sum(case when legtype1 = 2 and ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as
            Missed_Known
           , sum(b.duration) as Talk_Time_Seconds
           , sum(a.holdtimesecs) as Hold_Time_Seconds

          FROM session a
        INNER JOIN callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
        right join users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
        LEFT JOIN known_numbers k ON a.callingpartyno = k.phone_number
        left join known_numbers kn on a.finallycalledpartyno = kn.long_number
          WHERE a.ts >= '2017-12-07' -- curdate()
          and(a.CALLINGPARTYNO in (select extension from ambition_users) OR a.finallycalledpartyno IN (select extension from ambition_users))
        group by c.extension
      ) x

          order by extension;


至少上述查询将使您对如何解决该问题有所了解。

10-05 21:23
查看更多