清盘时效统计
<pre><code>
--- 九坤 托管与外包 确认清盘报告时长 27.22小时
select AVG(hour_diff) hour_diff_avg from (
select t.*, (bgqrsj - upload_time) * 24 as hour_diff from (
select t.ywpc, t.cpdm, max(t.upload_time) upload_time, bgqrsj from (
select t.ywpc, t.cpdm, t4.upload_time, to_date(t.bgqrrq || ' ' || t.bgqrsj, 'yyyymmdd hh24:mi:ss') as bgqrsj from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.glrmc like '%九坤%' and t3.fwms = '托管与外包'
join ZCTG.GTJA_ATTACHMENT t4 on t.seqid = t4.seqid and t4.remark = 'XLS'
where T.CPQSR between '20221231' and '20240101'
) t
group by t.ywpc, t.cpdm, t.bgqrsj
) t)
--- 九坤 单外包 确认清盘报告时长 14.38小时
L20230725152252814 SQA769 24.58
L20230612200818122 SQA769 4.19
select AVG(hour_diff) hour_diff_avg from (
select t.*, (bgqrsj - upload_time) * 24 as hour_diff from (
select t.ywpc, t.cpdm, max(t.upload_time) upload_time, bgqrsj from (
select t.ywpc, t.cpdm, t4.upload_time, to_date(t5.DDRQ || ' ' || t5.DDSJ, 'yyyymmdd hh24:mi:ss') as bgqrsj from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_ywclb t5 on t.ywpc = t5.ywpc and t5.jsmc = '542' and t5.CLRQ is not null
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.glrmc like '%九坤%' and t3.fwms = '单外包'
join ZCTG.GTJA_ATTACHMENT t4 on t.seqid = t4.seqid and t4.remark = 'XLS'
where T.CPQSR between '20221231' and '20240101'
) t
group by t.ywpc, t.cpdm, t.bgqrsj
) t)
--- 所有产品 托管与外包 确认清盘报告时长 16.95小时
select AVG(hour_diff) hour_diff_avg from (
select t.*, (bgqrsj - upload_time) * 24 as hour_diff from (
select t.ywpc, t.cpdm, max(t.upload_time) upload_time, bgqrsj from (
select t.ywpc, t.cpdm, t4.upload_time, to_date(t.bgqrrq || ' ' || t.bgqrsj, 'yyyymmdd hh24:mi:ss') as bgqrsj from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.fwms = '托管与外包'
join ZCTG.GTJA_ATTACHMENT t4 on t.seqid = t4.seqid and t4.remark = 'XLS'
where (T.CPQSR between '20221231' and '20240101') and (t.bgqrrq || ' ' || t.bgqrsj) &gt; to_char(t4.upload_time, 'yyyyMMdd hh24:mi:ss')
) t
group by t.ywpc, t.cpdm, t.bgqrsj
) t )
--- 所有产品 单外包 确认清盘报告时长 35.36小时
select AVG(hour_diff) hour_diff_avg from (
select t.*, (bgqrsj - upload_time) * 24 as hour_diff from (
select t.ywpc, t.cpdm, max(t.upload_time) upload_time, bgqrsj from (
select t.ywpc, t.cpdm, t4.upload_time, to_date(t5.DDRQ || ' ' || t5.DDSJ, 'yyyymmdd hh24:mi:ss') as bgqrsj from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_ywclb t5 on t.ywpc = t5.ywpc and t5.jsmc = '542' and t5.CLRQ is not null
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.fwms = '单外包'
join ZCTG.GTJA_ATTACHMENT t4 on t.seqid = t4.seqid and t4.remark = 'XLS'
where T.CPQSR between '20221231' and '20240101' and (t5.DDRQ || ' ' || t5.DDSJ) &gt; to_char(t4.upload_time, 'yyyyMMdd hh24:mi:ss')
) t
group by t.ywpc, t.cpdm, t.bgqrsj
) t)
------------------------------------------
--- 九坤 单外包 流程时长 4.97天
select AVG(day_diff) day_diff_avg from (
select t.*, (GXRQ - CJRQ) as day_diff from (
select t.ywpc, to_date(t5.CLRQ || ' ' || t5.CLSJ, 'yyyymmdd hh24:mi:ss') GXRQ, to_date(t2.CJRQ || ' ' || t2.CJSJ, 'yyyymmdd hh24:mi:ss') CJRQ from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_ywclb t5 on t.ywpc = t5.ywpc and t5.jsmc = '543' and t5.CLRQ is not null
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.glrmc like '%九坤%' and t3.fwms = '单外包'
where t.QSMS = '1' and T.CPQSR between '20221231' and '20240101'
) T )
--- 所有产品 单外包 流程时长 8.17天
select AVG(day_diff) day_diff_avg from (
select t.*, (GXRQ - CJRQ) as day_diff from (
select t.ywpc, to_date(t5.CLRQ || ' ' || t5.CLSJ, 'yyyymmdd hh24:mi:ss') GXRQ, to_date(t2.CJRQ || ' ' || t2.CJSJ, 'yyyymmdd hh24:mi:ss') CJRQ from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_ywclb t5 on t.ywpc = t5.ywpc and t5.jsmc = '543' and t5.CLRQ is not null
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.fwms = '单外包'
where t.QSMS = '1' and T.CPQSR between '20221231' and '20240101'
) T )
--- 九坤 托管与外包 流程时长 6.23天
select AVG(day_diff) day_diff_avg from (
select t.*, (GXRQ - CJRQ) as day_diff from (
select t.ywpc, to_date(t2.GXRQ || ' ' || t2.GXSJ, 'yyyymmdd hh24:mi:ss') GXRQ, to_date(t2.CJRQ || ' ' || t2.CJSJ, 'yyyymmdd hh24:mi:ss') CJRQ from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.glrmc like '%九坤%' and t3.fwms = '托管与外包'
where T.CPQSR between '20221231' and '20240101'
) T )
--- 所有产品 托管与外包 流程时长 5.36天
select AVG(day_diff) day_diff_avg from (
select t.*, (GXRQ - CJRQ) as day_diff from (
select t.ywpc, to_date(t2.GXRQ || ' ' || t2.GXSJ, 'yyyymmdd hh24:mi:ss') GXRQ, to_date(t2.CJRQ || ' ' || t2.CJSJ, 'yyyymmdd hh24:mi:ss') CJRQ from ZCTG.GTJA_CPQPLC_DT T
join zctg.xzpt_ywclzb t2 on t.ywpc = t2.ywpc and t2.dqjd = '流程已结束'
join zctg.xzpt_cpxx_jcxx t3 on t.cpdm = t3.cpdm and t3.fwms = '托管与外包'
where T.CPQSR between '20221231' and '20240101'
) T )</code></pre>