Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Optimize PL/SQL Procedure

2649469Apr 7 2014 — edited Apr 12 2024

Hello,

I created this procedure that runs a query for each day within a for loop, then inserts the results into a table, and it takes for ever to run, any advice?

CREATE OR REPLACE PROCEDURE ZDSP_STATS_ADMITS IS

-- Purpose: Used to update the ZDS_STATS_ADMITS_DETAIL, ZDS_STATS_ADMITS_BY_DAY, ZDS_STATS_ADMITS_BY_MTH tables

-- DBMS_Job: 781, executed the 8th of every month at 2:00 AM (CST)

-- Revision: 3.9.14

-- Declare and initialize variables

-- Run report starting 7/1 of prior fiscal year

startdate date := TO_DATE(('1-JUL-' || ((CASE WHEN EXTRACT(MONTH FROM TRUNC(SYSDATE, 'MM') - 1) < 7

              THEN EXTRACT(YEAR FROM SYSDATE - 1) - 1

              ELSE EXTRACT(YEAR FROM SYSDATE - 1) END) - 1)));

enddate date := TRUNC(SYSDATE, 'MM') - 1; -- last day of prior month

idate number;

BEGIN

-- Delete all records from existing table

EXECUTE IMMEDIATE 'TRUNCATE TABLE ADS.ZDS_STATS_ADMITS_DETAIL';

EXECUTE IMMEDIATE 'TRUNCATE TABLE ADS.ZDS_STATS_ADMITS_BY_DAY';

EXECUTE IMMEDIATE 'TRUNCATE TABLE ADS.ZDS_STATS_ADMITS_BY_MTH';

-- START: Update admits detail

-- Initialize record counter to zero

idate := 0;

FOR idate IN 0 .. (enddate - startdate)

LOOP

INSERT INTO ZDS_STATS_ADMITS_DETAIL (STAT, CAL_YEAR, CAL_QTR, CAL_MTH_NO, FISCAL_YEAR, FISCAL_QTR, FISCAL_MTH_NO, MTH_NAME, ENTITY, POSTINGTIMESTAMP, AREA, DEPT, HAR, QTY)

SELECT

'ADMITS' AS STAT,

EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP) AS CAL_YEAR,

(CASE

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (1, 2, 3)) THEN '1'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (4, 5, 6)) THEN '2'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (7, 8, 9)) THEN '3'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (10, 11, 12)) THEN '4' END) AS CAL\_QTR,

EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) AS CAL_MTH_NO,

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) < 7 THEN

 EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP)

ELSE EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP) + 1 END) AS FISCAL_YEAR,

(CASE

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (7, 8, 9)) THEN '1'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (10, 11, 12)) THEN '2'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (1, 2, 3)) THEN '3'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (4, 5, 6)) THEN '4' END) AS FISCAL\_QTR,

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) >= 7 THEN

 EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) - 6

ELSE EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) + 6 END) AS FISCAL_MTH_NO,

TO_CHAR(TO_DATE(EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP), 'MM'), 'MON') AS MTH_NAME,

Q7.ENTITY,

Q7.POSTINGTIMESTAMP,

(CASE Q7.DEPT

WHEN '10006113' THEN 'MED SURG'

WHEN '10006115' THEN 'MED SURG'

WHEN '10006116' THEN 'MED SURG'

WHEN '10006118' THEN 'MED SURG'

WHEN '10006119' THEN 'MED SURG'

WHEN '10006120' THEN 'MED SURG'

WHEN '10006146' THEN 'ADULT CRITICAL CARE'

WHEN '10006147' THEN 'ADULT CRITICAL CARE'

WHEN '10006250' THEN 'ADULT CRITICAL CARE'

WHEN '10006130' THEN 'PEDIATRICS PICU'

WHEN '10006148' THEN 'PEDIATRICS PICU'

WHEN '10006132' THEN 'ED'

WHEN '10006144' THEN 'LABOR AND DELIVERY/OB'

WHEN '10006145' THEN 'LABOR AND DELIVERY/OB'

WHEN '10006142' THEN 'NICU'

WHEN '10006194' THEN 'BEHAVIORAL HEALTH'

WHEN '10006195' THEN 'BEHAVIORAL HEALTH'

WHEN '41006100' THEN 'MED SURG'

WHEN '41006101' THEN 'MED SURG'

WHEN '41006105' THEN 'MED SURG'

WHEN '41006110' THEN 'MED SURG'

WHEN '41006130' THEN 'ADULT CRITICAL CARE'

WHEN '41006115' THEN 'ADULT INTERMEDIATE CARE'

WHEN '41006120' THEN 'PEDIATRICS'

WHEN '41006160' THEN 'LABOR AND DELIVERY/OB' END) AS AREA,

Q7.DEPT,

Q7.HAR,

SUM(Q7.QTY) AS QTY

FROM (

-- START: GET CURRENT TIME PERIOD ADMIT QTY EXCLUDING QTY = 0

SELECT

Q6.ENTITY,

Q6.POSTINGTIMESTAMP,

Q6.DEPT,

Q6.HAR,

Q6.QTY

FROM (

-- START: DETERMINE CURRENT TIME PERIOD ADMIT QTY

SELECT

  Q5.ENTITY,

  Q5.CUR\_MIN\_POST AS POSTINGTIMESTAMP,

  Q5.CUR\_MIN\_DEPT AS DEPT,

  Q5.HAR,

  (CASE

     WHEN (Q5.PRIOR\_QTY IS NULL AND Q5.CUR\_QTY > 0) THEN 1

     WHEN (Q5.PRIOR\_QTY IS NULL AND Q5.CUR\_QTY = 0) THEN 0

     WHEN (Q5.PRIOR\_QTY = 0 AND Q5.CUR\_QTY > 0) THEN 1

     WHEN (Q5.PRIOR\_QTY = 0 AND Q5.CUR\_QTY = 0) THEN 0

     WHEN (Q5.PRIOR\_QTY + Q5.CUR\_QTY) > 0 THEN 0

     WHEN (Q5.PRIOR\_QTY + Q5.CUR\_QTY) = 0 THEN -1

   END) AS QTY

FROM (

  -- START: CONSOLIDATE CURRENT AND PRIOR TIME PERIODS

  SELECT

    CUR\_PERIOD.ENTITY,

    CUR\_PERIOD.HAR,

    CUR\_PERIOD.CUR\_MIN\_DEPT,

    CUR\_PERIOD.CUR\_MIN\_POST,

    CUR\_PERIOD.CUR\_QTY,

    PRIOR\_PERIOD.PRIOR\_MIN\_DEPT,

    PRIOR\_PERIOD.PRIOR\_MIN\_POST,

    PRIOR\_PERIOD.PRIOR\_QTY

  FROM (

    -- START: GET ADMITS FOR CURRENT TIME PERIOD

    SELECT

      Q4.ENTITY,

      Q4.CUR\_MIN\_POST,

      Q4.CUR\_MIN\_SERVICE,

      Q4.CUR\_MIN\_DEPT,

      Q4.HAR,

      SUM(D3.QTY) AS CUR\_QTY

    FROM (

      SELECT

        Q3.ENTITY,

        Q3.CUR\_MIN\_POST,

        Q3.CUR\_MIN\_SERVICE,

        D2.DEPT AS CUR\_MIN\_DEPT,

        Q3.HAR,

        SUM(D2.QTY) AS CUR\_QTY

      FROM (

        SELECT

          Q2.ENTITY,

          Q2.CUR\_MIN\_SERVICE,

          MIN(D.POSTINGTIMESTAMP) CUR\_MIN\_POST,

          Q2.HAR

        FROM (

          SELECT

            Q1.ENTITY,

            MIN(Q1.SERVICETIMESTAMP) AS CUR\_MIN\_SERVICE,

            Q1.HAR

          FROM (

            SELECT

              C.UENTITY01 AS ENTITY,

              C.SERVICETIMESTAMP,

              C.ACTDEPTCODE AS DEPT,

              C.FINANCIALRECNUM AS HAR,

              SUM(C.QUANTITY) AS QTY

            FROM

              ADS.CHARGEITEM C

            WHERE

              C.UENTITY01 IN ('1000', '4100')

              AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

              AND (C.REVENUECODECODE BETWEEN '100' AND '219'

              OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

              --AND C.FINANCIALRECNUM IN ('22150281')

              -- Current time period

              AND C.POSTINGTIMESTAMP = (startdate + idate)

            GROUP BY

              C.UENTITY01,

              C.SERVICETIMESTAMP,

              C.ACTDEPTCODE,

              C.FINANCIALRECNUM

            HAVING

              SUM(C.QUANTITY) \<> 0 ) Q1

          GROUP BY

            Q1.ENTITY,

            Q1.HAR ) Q2, (SELECT

                            C.UENTITY01 AS ENTITY,

                            C.SERVICETIMESTAMP,

                            C.POSTINGTIMESTAMP,

                            C.ACTDEPTCODE AS DEPT,

                            C.FINANCIALRECNUM AS HAR,

                            SUM(C.QUANTITY) AS QTY

                          FROM

                            ADS.CHARGEITEM C

                          WHERE

                            C.UENTITY01 IN ('1000', '4100')

                            AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                            AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                            OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                            --AND C.FINANCIALRECNUM IN ('22150281')

                            -- Current time period

                            AND C.POSTINGTIMESTAMP = (startdate + idate)

                          GROUP BY

                            C.UENTITY01,

                            C.SERVICETIMESTAMP,

                            C.POSTINGTIMESTAMP,

                            C.ACTDEPTCODE,

                            C.FINANCIALRECNUM

                          HAVING

                            SUM(C.QUANTITY) \<> 0) D

        WHERE

          Q2.ENTITY = D.ENTITY

          AND Q2.HAR = D.HAR

          AND Q2.CUR\_MIN\_SERVICE = D.SERVICETIMESTAMP

        GROUP BY

          Q2.ENTITY,

          Q2.CUR\_MIN\_SERVICE,

          Q2.HAR ) Q3, (SELECT

                          C.UENTITY01 AS ENTITY,

                          C.SERVICETIMESTAMP,

                          C.POSTINGTIMESTAMP,

                          C.ACTDEPTCODE AS DEPT,

                          C.FINANCIALRECNUM AS HAR,

                          SUM(C.QUANTITY) AS QTY

                        FROM

                          ADS.CHARGEITEM C

                        WHERE

                          C.UENTITY01 IN ('1000', '4100')

                          AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                          AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                          OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                          --AND C.FINANCIALRECNUM IN ('22150281')

                          -- Current time period

                          AND C.POSTINGTIMESTAMP = (startdate + idate)

                        GROUP BY

                          C.UENTITY01,

                          C.SERVICETIMESTAMP,

                          C.POSTINGTIMESTAMP,

                          C.ACTDEPTCODE,

                          C.FINANCIALRECNUM

                        HAVING

                          SUM(C.QUANTITY) \<> 0) D2

      WHERE

        Q3.ENTITY = D2.ENTITY

        AND Q3.HAR = D2.HAR

        AND Q3.CUR\_MIN\_SERVICE = D2.SERVICETIMESTAMP

      GROUP BY

        Q3.ENTITY,

        Q3.CUR\_MIN\_POST,

        Q3.CUR\_MIN\_SERVICE,

        D2.DEPT,

        Q3.HAR ) Q4, (SELECT

                        C.UENTITY01 AS ENTITY,

                        C.SERVICETIMESTAMP,

                        C.POSTINGTIMESTAMP,

                        C.ACTDEPTCODE AS DEPT,

                        C.FINANCIALRECNUM AS HAR,

                        SUM(C.QUANTITY) AS QTY

                      FROM

                        ADS.CHARGEITEM C

                      WHERE

                        C.UENTITY01 IN ('1000', '4100')

                        AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                        AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                        OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                        --AND C.FINANCIALRECNUM IN ('22150281')

                        -- Current time period

                        AND C.POSTINGTIMESTAMP = (startdate + idate)

                      GROUP BY

                        C.UENTITY01,

                        C.SERVICETIMESTAMP,

                        C.POSTINGTIMESTAMP,

                        C.ACTDEPTCODE,

                        C.FINANCIALRECNUM

                      HAVING

                        SUM(C.QUANTITY) \<> 0) D3

    WHERE

      Q4.ENTITY = D3.ENTITY

      AND Q4.HAR = D3.HAR

    GROUP BY

      Q4.ENTITY,

      Q4.CUR\_MIN\_POST,

      Q4.CUR\_MIN\_SERVICE,

      Q4.CUR\_MIN\_DEPT,

      Q4.HAR ) CUR\_PERIOD

    -- END: GET ADMITS FOR CURRENT TIME PERIOD

    LEFT OUTER JOIN (

      -- START: GET ADMITS \< CURRENT TIME PERIOD

      SELECT

        Q4.ENTITY,

        Q4.PRIOR\_MIN\_POST,

        Q4.PRIOR\_MIN\_SERVICE,

        Q4.PRIOR\_MIN\_DEPT,

        Q4.HAR,

        SUM(D3.QTY) AS PRIOR\_QTY

      FROM (

        SELECT

          Q3.ENTITY,

          Q3.PRIOR\_MIN\_POST,

          Q3.PRIOR\_MIN\_SERVICE,

          D2.DEPT AS PRIOR\_MIN\_DEPT,

          Q3.HAR,

          SUM(D2.QTY) AS PRIOR\_QTY

        FROM (

          SELECT

            Q2.ENTITY,

            Q2.PRIOR\_MIN\_SERVICE,

            MIN(D.POSTINGTIMESTAMP) PRIOR\_MIN\_POST,

            Q2.HAR

          FROM (

            SELECT

              Q1.ENTITY,

              MIN(Q1.SERVICETIMESTAMP) AS PRIOR\_MIN\_SERVICE,

              Q1.HAR

            FROM (

              SELECT

                C.UENTITY01 AS ENTITY,

                C.SERVICETIMESTAMP,

                C.ACTDEPTCODE AS DEPT,

                C.FINANCIALRECNUM AS HAR,

                SUM(C.QUANTITY) AS QTY

              FROM

                ADS.CHARGEITEM C

              WHERE

                C.UENTITY01 IN ('1000', '4100')

                AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                --AND C.FINANCIALRECNUM IN ('22150281')

                -- Prior time period (no more than 18 months back)

                AND C.POSTINGTIMESTAMP BETWEEN (TO\_DATE((startdate + idate)) - 548) AND (TO\_DATE((startdate + idate)) - 1)

              GROUP BY

                C.UENTITY01,

                C.SERVICETIMESTAMP,

                C.ACTDEPTCODE,

                C.FINANCIALRECNUM

              HAVING

                SUM(C.QUANTITY) \<> 0 ) Q1

            GROUP BY

              Q1.ENTITY,

              Q1.HAR ) Q2, (SELECT

                              C.UENTITY01 AS ENTITY,

                              C.SERVICETIMESTAMP,

                              C.POSTINGTIMESTAMP,

                              C.ACTDEPTCODE AS DEPT,

                              C.FINANCIALRECNUM AS HAR,

                              SUM(C.QUANTITY) AS QTY

                            FROM

                              ADS.CHARGEITEM C

                            WHERE

                              C.UENTITY01 IN ('1000', '4100')

                              AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                              AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                              OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                              --AND C.FINANCIALRECNUM IN ('22150281')

                              -- Prior time period (no more than 18 months back)

                              AND C.POSTINGTIMESTAMP BETWEEN (TO\_DATE((startdate + idate)) - 548) AND (TO\_DATE((startdate + idate)) - 1)

                            GROUP BY

                              C.UENTITY01,

                              C.SERVICETIMESTAMP,

                              C.POSTINGTIMESTAMP,

                              C.ACTDEPTCODE,

                              C.FINANCIALRECNUM

                            HAVING

                              SUM(C.QUANTITY) \<> 0) D

          WHERE

            Q2.ENTITY = D.ENTITY

            AND Q2.HAR = D.HAR

            AND Q2.PRIOR\_MIN\_SERVICE = D.SERVICETIMESTAMP

          GROUP BY

            Q2.ENTITY,

            Q2.PRIOR\_MIN\_SERVICE,

            Q2.HAR ) Q3, (SELECT

                            C.UENTITY01 AS ENTITY,

                            C.SERVICETIMESTAMP,

                            C.POSTINGTIMESTAMP,

                            C.ACTDEPTCODE AS DEPT,

                            C.FINANCIALRECNUM AS HAR,

                            SUM(C.QUANTITY) AS QTY

                          FROM

                            ADS.CHARGEITEM C

                          WHERE

                            C.UENTITY01 IN ('1000', '4100')

                            AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                            AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                            OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                            --AND C.FINANCIALRECNUM IN ('22150281')

                            -- Prior time period (no more than 18 months back)

                            AND C.POSTINGTIMESTAMP BETWEEN (TO\_DATE((startdate + idate)) - 548) AND (TO\_DATE((startdate + idate)) - 1)

                          GROUP BY

                            C.UENTITY01,

                            C.SERVICETIMESTAMP,

                            C.POSTINGTIMESTAMP,

                            C.ACTDEPTCODE,

                            C.FINANCIALRECNUM

                          HAVING

                            SUM(C.QUANTITY) \<> 0) D2

        WHERE

          Q3.ENTITY = D2.ENTITY

          AND Q3.HAR = D2.HAR

          AND Q3.PRIOR\_MIN\_SERVICE = D2.SERVICETIMESTAMP

        GROUP BY

          Q3.ENTITY,

          Q3.PRIOR\_MIN\_POST,

          Q3.PRIOR\_MIN\_SERVICE,

          D2.DEPT,

          Q3.HAR ) Q4, (SELECT

                          C.UENTITY01 AS ENTITY,

                          C.SERVICETIMESTAMP,

                          C.POSTINGTIMESTAMP,

                          C.ACTDEPTCODE AS DEPT,

                          C.FINANCIALRECNUM AS HAR,

                          SUM(C.QUANTITY) AS QTY

                        FROM

                          ADS.CHARGEITEM C

                        WHERE

                          C.UENTITY01 IN ('1000', '4100')

                          AND C.ACTDSCRCODE NOT IN ('1700001', '1710001', '1720001', '1730001', '1740001')

                          AND (C.REVENUECODECODE BETWEEN '100' AND '219'

                          OR C.REVENUECODECODE BETWEEN '1000' AND '1005')

                          --AND C.FINANCIALRECNUM IN ('22150281')

                          -- Current time period

                          AND C.POSTINGTIMESTAMP BETWEEN (TO\_DATE((startdate + idate)) - 548) AND (TO\_DATE((startdate + idate)) - 1)

                        GROUP BY

                          C.UENTITY01,

                          C.SERVICETIMESTAMP,

                          C.POSTINGTIMESTAMP,

                          C.ACTDEPTCODE,

                          C.FINANCIALRECNUM

                        HAVING

                          SUM(C.QUANTITY) \<> 0) D3

    WHERE

      Q4.ENTITY = D3.ENTITY

      AND Q4.HAR = D3.HAR

    GROUP BY

      Q4.ENTITY,

      Q4.PRIOR\_MIN\_POST,

      Q4.PRIOR\_MIN\_SERVICE,

      Q4.PRIOR\_MIN\_DEPT,

      Q4.HAR ) PRIOR\_PERIOD

        -- END: GET ADMITS \< CURRENT TIME PERIOD

    ON CUR\_PERIOD.ENTITY = PRIOR\_PERIOD.ENTITY

    AND CUR\_PERIOD.HAR = PRIOR\_PERIOD.HAR

  GROUP BY

    CUR\_PERIOD.ENTITY,

    CUR\_PERIOD.HAR,

    CUR\_PERIOD.CUR\_MIN\_DEPT,

    CUR\_PERIOD.CUR\_MIN\_POST,

    CUR\_PERIOD.CUR\_QTY,

    PRIOR\_PERIOD.PRIOR\_MIN\_DEPT,

    PRIOR\_PERIOD.PRIOR\_MIN\_POST,

    PRIOR\_PERIOD.PRIOR\_QTY ) Q5

-- END: CONSOLIDATE CURRENT AND PRIOR TIME PERIODS

GROUP BY

  Q5.ENTITY,

  Q5.CUR\_MIN\_POST,

  Q5.CUR\_MIN\_DEPT,

  Q5.HAR,

  (CASE

     WHEN (Q5.PRIOR\_QTY IS NULL AND Q5.CUR\_QTY > 0) THEN 1

     WHEN (Q5.PRIOR\_QTY IS NULL AND Q5.CUR\_QTY = 0) THEN 0

     WHEN (Q5.PRIOR\_QTY = 0 AND Q5.CUR\_QTY > 0) THEN 1

     WHEN (Q5.PRIOR\_QTY = 0 AND Q5.CUR\_QTY = 0) THEN 0

     WHEN (Q5.PRIOR\_QTY + Q5.CUR\_QTY) > 0 THEN 0

     WHEN (Q5.PRIOR\_QTY + Q5.CUR\_QTY) = 0 THEN -1

   END) ) Q6

-- END: DETERMINE CURRENT TIME PERIOD ADMIT QTY

GROUP BY

Q6.ENTITY,

Q6.POSTINGTIMESTAMP,

Q6.DEPT,

Q6.HAR,

Q6.QTY

HAVING

Q6.QTY \<> 0 ) Q7

-- END: GET CURRENT TIME PERIOD ADMIT QTY EXCLUDING QTY = 0

GROUP BY

'ADMITS',

-- Calendar year

EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP),

-- Calendar quarter

(CASE

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (1, 2, 3)) THEN '1'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (4, 5, 6)) THEN '2'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (7, 8, 9)) THEN '3'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (10, 11, 12)) THEN '4' END),

-- Calendar month no

EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP),

-- Fiscal year

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) < 7 THEN

 EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP)

ELSE EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP) + 1 END),

-- Fiscal quarter

(CASE

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (7, 8, 9)) THEN '1'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (10, 11, 12)) THEN '2'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (1, 2, 3)) THEN '3'

 WHEN (EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) IN (4, 5, 6)) THEN '4' END),

-- Fiscal month no

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) >= 7 THEN

 EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) - 6

ELSE EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) + 6 END),

-- Month name

TO_CHAR(TO_DATE(EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP), 'MM'), 'MON'),

Q7.ENTITY,

Q7.POSTINGTIMESTAMP,

-- Area no

(CASE Q7.DEPT

WHEN '10006113' THEN '1'

WHEN '10006115' THEN '1'

WHEN '10006116' THEN '1'

WHEN '10006118' THEN '1'

WHEN '10006119' THEN '1'

WHEN '10006120' THEN '1'

WHEN '10006146' THEN '2'

WHEN '10006147' THEN '2'

WHEN '10006250' THEN '2'

WHEN '10006130' THEN '3'

WHEN '10006148' THEN '3'

WHEN '10006132' THEN '4'

WHEN '10006144' THEN '5'

WHEN '10006145' THEN '5'

WHEN '10006142' THEN '6'

WHEN '10006194' THEN '7'

WHEN '10006195' THEN '7'

WHEN '41006100' THEN '10'

WHEN '41006101' THEN '10'

WHEN '41006105' THEN '10'

WHEN '41006110' THEN '10'

WHEN '41006130' THEN '11'

WHEN '41006115' THEN '12'

WHEN '41006120' THEN '13'

WHEN '41006160' THEN '14' END),

-- Area

(CASE Q7.DEPT

WHEN '10006113' THEN 'MED SURG'

WHEN '10006115' THEN 'MED SURG'

WHEN '10006116' THEN 'MED SURG'

WHEN '10006118' THEN 'MED SURG'

WHEN '10006119' THEN 'MED SURG'

WHEN '10006120' THEN 'MED SURG'

WHEN '10006146' THEN 'ADULT CRITICAL CARE'

WHEN '10006147' THEN 'ADULT CRITICAL CARE'

WHEN '10006250' THEN 'ADULT CRITICAL CARE'

WHEN '10006130' THEN 'PEDIATRICS PICU'

WHEN '10006148' THEN 'PEDIATRICS PICU'

WHEN '10006132' THEN 'ED'

WHEN '10006144' THEN 'LABOR AND DELIVERY/OB'

WHEN '10006145' THEN 'LABOR AND DELIVERY/OB'

WHEN '10006142' THEN 'NICU'

WHEN '10006194' THEN 'BEHAVIORAL HEALTH'

WHEN '10006195' THEN 'BEHAVIORAL HEALTH'

WHEN '41006100' THEN 'MED SURG'

WHEN '41006101' THEN 'MED SURG'

WHEN '41006105' THEN 'MED SURG'

WHEN '41006110' THEN 'MED SURG'

WHEN '41006130' THEN 'ADULT CRITICAL CARE'

WHEN '41006115' THEN 'ADULT INTERMEDIATE CARE'

WHEN '41006120' THEN 'PEDIATRICS'

WHEN '41006160' THEN 'LABOR AND DELIVERY/OB' END),

Q7.DEPT,

Q7.HAR

ORDER BY

-- Fiscal year

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) < 7 THEN

 EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP)

ELSE EXTRACT(YEAR FROM Q7.POSTINGTIMESTAMP) + 1 END) ASC,

Q7.ENTITY ASC,

-- Fiscal month no

(CASE WHEN EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) >= 7 THEN

 EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) - 6

ELSE EXTRACT(MONTH FROM Q7.POSTINGTIMESTAMP) + 6 END) ASC,

Q7.POSTINGTIMESTAMP ASC,

-- Area no

(CASE Q7.DEPT

WHEN '10006113' THEN '1'

WHEN '10006115' THEN '1'

WHEN '10006116' THEN '1'

WHEN '10006118' THEN '1'

WHEN '10006119' THEN '1'

WHEN '10006120' THEN '1'

WHEN '10006146' THEN '2'

WHEN '10006147' THEN '2'

WHEN '10006250' THEN '2'

WHEN '10006130' THEN '3'

WHEN '10006148' THEN '3'

WHEN '10006132' THEN '4'

WHEN '10006144' THEN '5'

WHEN '10006145' THEN '5'

WHEN '10006142' THEN '6'

WHEN '10006194' THEN '7'

WHEN '10006195' THEN '7'

WHEN '41006100' THEN '10'

WHEN '41006101' THEN '10'

WHEN '41006105' THEN '10'

WHEN '41006110' THEN '10'

WHEN '41006130' THEN '11'

WHEN '41006115' THEN '12'

WHEN '41006120' THEN '13'

WHEN '41006160' THEN '14' END) ASC,

Q7.DEPT ASC,

Q7.HAR ASC;

COMMIT;

END LOOP;

COMMIT;

-- END: Update admits detail

-- START: Update admits by day

INSERT INTO ZDS_STATS_ADMITS_BY_DAY (STAT, CAL_YEAR, CAL_QTR, CAL_MTH_NO, FISCAL_YEAR, FISCAL_QTR, FISCAL_MTH_NO, MTH_NAME, ENTITY, POSTINGTIMESTAMP, AREA, DEPT, QTY)

SELECT

D.STAT,

D.CAL_YEAR,

D.CAL_QTR,

D.FISCAL_MTH_NO,

D.FISCAL_YEAR,

D.FISCAL_QTR,

D.FISCAL_MTH_NO,

D.MTH_NAME,

D.ENTITY,

D.POSTINGTIMESTAMP,

D.AREA,

D.DEPT,

SUM(D.QTY) AS QTY

FROM

ZDS_STATS_ADMITS_DETAIL D

WHERE

-- Run report starting 7/1 of prior fiscal year

D.POSTINGTIMESTAMP >= ('1-JUL-' || ((CASE WHEN EXTRACT(MONTH FROM TRUNC(SYSDATE, 'MM') - 1) < 7 THEN

                                             EXTRACT(YEAR FROM SYSDATE - 1) - 1

                                        ELSE EXTRACT(YEAR FROM SYSDATE - 1) END) - 1))

-- Run report thru last day of prior month of current fiscal year

AND D.POSTINGTIMESTAMP <= (TRUNC(SYSDATE, 'MM') - 1)

GROUP BY

D.STAT,

D.CAL_YEAR,

D.CAL_QTR,

D.FISCAL_MTH_NO,

D.FISCAL_YEAR,

D.FISCAL_QTR,

D.FISCAL_MTH_NO,

D.MTH_NAME,

D.ENTITY,

D.POSTINGTIMESTAMP,

D.AREA,

D.DEPT

ORDER BY

D.FISCAL_YEAR ASC,

D.ENTITY ASC,

D.FISCAL_MTH_NO ASC,

D.POSTINGTIMESTAMP ASC,

-- Area no

(CASE D.DEPT

WHEN '10006113' THEN '1'

WHEN '10006115' THEN '1'

WHEN '10006116' THEN '1'

WHEN '10006118' THEN '1'

WHEN '10006119' THEN '1'

WHEN '10006120' THEN '1'

WHEN '10006146' THEN '2'

WHEN '10006147' THEN '2'

WHEN '10006250' THEN '2'

WHEN '10006130' THEN '3'

WHEN '10006148' THEN '3'

WHEN '10006132' THEN '4'

WHEN '10006144' THEN '5'

WHEN '10006145' THEN '5'

WHEN '10006142' THEN '6'

WHEN '10006194' THEN '7'

WHEN '10006195' THEN '7'

WHEN '41006100' THEN '10'

WHEN '41006101' THEN '10'

WHEN '41006105' THEN '10'

WHEN '41006110' THEN '10'

WHEN '41006130' THEN '11'

WHEN '41006115' THEN '12'

WHEN '41006120' THEN '13'

WHEN '41006160' THEN '14' END) ASC,

D.DEPT ASC;

COMMIT;

-- END: Update admits by day

-- START: Update admits by mth

INSERT INTO ZDS_STATS_ADMITS_BY_MTH (STAT, CAL_YEAR, CAL_QTR, CAL_MTH_NO, FISCAL_YEAR, FISCAL_QTR, FISCAL_MTH_NO, MTH_NAME, ENTITY, AREA, DEPT, QTY)

SELECT

D.STAT,

D.CAL_YEAR,

D.CAL_QTR,

D.FISCAL_MTH_NO,

D.FISCAL_YEAR,

D.FISCAL_QTR,

D.FISCAL_MTH_NO,

D.MTH_NAME,

D.ENTITY,

D.AREA,

D.DEPT,

SUM(D.QTY) AS QTY

FROM

ZDS_STATS_ADMITS_DETAIL D

WHERE

-- Run report starting 7/1 of prior fiscal year

D.POSTINGTIMESTAMP >= ('1-JUL-' || ((CASE WHEN EXTRACT(MONTH FROM TRUNC(SYSDATE, 'MM') - 1) < 7 THEN

                                             EXTRACT(YEAR FROM SYSDATE - 1) - 1

                                        ELSE EXTRACT(YEAR FROM SYSDATE - 1) END) - 1))

-- Run report thru last day of prior month of current fiscal year

AND D.POSTINGTIMESTAMP <= (TRUNC(SYSDATE, 'MM') - 1)

GROUP BY

D.STAT,

D.CAL_YEAR,

D.CAL_QTR,

D.FISCAL_MTH_NO,

D.FISCAL_YEAR,

D.FISCAL_QTR,

D.FISCAL_MTH_NO,

D.MTH_NAME,

D.ENTITY,

D.AREA,

D.DEPT

ORDER BY

D.FISCAL_YEAR ASC,

D.ENTITY ASC,

D.FISCAL_MTH_NO ASC,

-- Area no

(CASE D.DEPT

WHEN '10006113' THEN '1'

WHEN '10006115' THEN '1'

WHEN '10006116' THEN '1'

WHEN '10006118' THEN '1'

WHEN '10006119' THEN '1'

WHEN '10006120' THEN '1'

WHEN '10006146' THEN '2'

WHEN '10006147' THEN '2'

WHEN '10006250' THEN '2'

WHEN '10006130' THEN '3'

WHEN '10006148' THEN '3'

WHEN '10006132' THEN '4'

WHEN '10006144' THEN '5'

WHEN '10006145' THEN '5'

WHEN '10006142' THEN '6'

WHEN '10006194' THEN '7'

WHEN '10006195' THEN '7'

WHEN '41006100' THEN '10'

WHEN '41006101' THEN '10'

WHEN '41006105' THEN '10'

WHEN '41006110' THEN '10'

WHEN '41006130' THEN '11'

WHEN '41006115' THEN '12'

WHEN '41006120' THEN '13'

WHEN '41006160' THEN '14' END) ASC,

D.DEPT ASC;

COMMIT;

-- END: Update admits by month

END ZDSP_STATS_ADMITS;

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 7 2014
Added on Apr 7 2014
4 comments
2,182 views