工作一年半—半年报表交接（二）

现在的位置: 首页 > 综合 > 正文

工作一年半—半年报表交接（二）

2012年11月06日 ⁄ 综合 ⁄ 共 5398字 ⁄ 字号小中大 ⁄ 评论关闭

一：交叉表格子内是平均值，格子的下方是计算和

二：sql语句：

SELECT 'Requesting clinical services' AS TYPE,
    F.FRM_NAME,
    F.FRM_NUM,
    F.FRM_NUM AS FORNUM,
    NULL SITE_COD,
    AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) IRDAY,
    C.SVC_NAME
FROM IVG_RQST            R,
    IVG_FRM             F,
    CLC_ENCNTR E,
    COD_SVC    C,
    IVG_RPT             IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.CLC_ENCNTR_ID = E.CLC_ENCNTR_ID
AND E.COD_SVC_ID = C.COD_SVC_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID
   and C.IS_ACTIVE=1
--   AND ((SELECT CASE
--                  WHEN '{?Frequency}' = 'Yearly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?Year}')
--         WHEN '{?Frequency}' = 'Monthly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?Month}',1,4)
--                           AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?Month}',6,2)
--)
--
--WHEN '{?Frequency}' = 'Daily' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?Date}, 'yyyy-mm-dd'))
--
--
--
--WHEN '{?Frequency}' = 'User Specified' THEN
--                       (SELECT 1
--                          FROM DUAL
--                         WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
--                            {?Start date}    AND {?End date})
--
--
--
--                END CASE
--      FROM DUAL) = 1)

GROUP BY F.FRM_NAME, F.FRM_NUM, C.SVC_NAME

UNION ALL

SELECT 'Requesting clinics/units' AS TYPE,
    F.FRM_NAME,
    F.FRM_NUM,
    F.FRM_NUM AS FORNUM,
    C.SITE_COD,
    AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) AS IRDAY,
    NULL SVC_NAME
FROM IVG_RQST            R,
    IVG_FRM             F,
    CLC_ENCNTR E,
    ORG_SITE   C,
    IVG_RPT             IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.CLC_ENCNTR_ID = E.CLC_ENCNTR_ID
AND E.ORG_SITE_ID = C.ORG_SITE_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID
-- AND ((SELECT CASE
--                  WHEN '{?Frequency}' = 'Yearly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?Year}')
--         WHEN '{?Frequency}' = 'Monthly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?Month}',1,4)
--                           AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?Month}',6,2)
--)
--
--WHEN '{?Frequency}' = 'Daily' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?Date}, 'yyyy-mm-dd'))
--
--
--
--WHEN '{?Frequency}' = 'User Specified' THEN
--                       (SELECT 1
--                          FROM DUAL
--                         WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
--                            {?Start date}    AND {?End date})
--
--
--                END CASE
--      FROM DUAL) = 1)

GROUP BY F.FRM_NAME, F.FRM_NUM, C.SITE_COD

UNION ALL

SELECT 'investigation request forms/disciplines' AS TYPE,
    F.FRM_NAME,
    F.FRM_NUM,
    F.FRM_NUM as FORNUM,
    NULL SITE_COD,
    AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) AS IRDAY,
    NULL SVC_NAME
FROM IVG_RQST R, IVG_FRM F, IVG_RPT IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID

--    AND ((SELECT CASE
--                  WHEN '{?Frequency}' = 'Yearly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?Year}')
--         WHEN '{?Frequency}' = 'Monthly' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?Month}',1,4)
--                           AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?Month}',6,2)
--)
--
--WHEN '{?Frequency}' = 'Daily' THEN
--                         (SELECT 1
--                            FROM DUAL
--                           WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?Date}, 'yyyy-mm-dd'))
--
--
--
--WHEN '{?Frequency}' = 'User Specified' THEN
--                       (SELECT 1
--                          FROM DUAL
--                         WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
--                            {?Start date}    AND {?End date})
--                END CASE
--      FROM DUAL) = 1)

GROUP BY F.FRM_NAME, F.FRM_NUM