This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,960 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Categories

Optimize PL/SQL Procedure

2649469
2649469 Member Posts: 1
edited Dec 17, 2016 3:53PM in SQL & PL/SQL

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)

-- Author: Clinton Garafolo

-- 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;

Tagged:
· Share on TwitterShare on Facebook

Answers

  • L-MachineGun
    L-MachineGun Member Posts: 926 Silver Badge

    Trace the session to get the execution plans of each sql query.

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown 
    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?

    There are more questions in your post than answers.

    Why are you processing each day separately in a loop? Why not just process all data at the same time?

    Why do you have SEVEN layes of nested selects?

    -- 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)));

    That produces a value of 7/1/2012. Is that what you mean by 'prior fiscal year'?

    Why are you hard coding that computation two more times in two of the queries rather than just use 'startdate' and 'enddate' in the queries?

    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)

    Why do you have an ORDER BY on an INSERT statement for '-- START: Update admits by mth'?

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi,

    As mentioned already, it will be much faster if you do one big INSERT statement instead of doing lots of little INSERT statements in a loop.

    It may be simpler and more efficient to use MERGE instead of INSERT.

    If you'd like help, post CREATE TABLE and INSERT statements for the relevant tables as they exist before the INSERT, and the results you want from that data (that is, the contents of the changed table(s) after the INSERT is finished).

    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

    Is there any reason to have ORDER BY clauses?  A lot of folks don't care if one row was entered a second or two before another row, rather than a  second or two after it.

    · Share on TwitterShare on Facebook
  • Etbin
    Etbin SloveniaMember Posts: 8,968 Gold Crown
    edited Apr 9, 2014 5:45AM

    Taking baby steps:

    seems that using

    select case when postingtimestamp = startdate + idate

                then 'current'

                else 'prior'

           end period,

           uentity01 as entity,

           servicetimestamp,

           postingtimestamp,

           actdeptcode as dept,

           financialrecnum as har,

           sum(quantity) as qty

      from ads.chargeitem

    where uentity01 in ('1000','4100')

       and actdscrcode not in ('1700001','1710001','1720001','1730001','1740001')

       and (revenuecodecode between '100' and '219'

        or  revenuecodecode between '1000' and '1005'

           )

    -- AND FINANCIALRECNUM IN ('22150281')

           -- Current time period

       and postingtimestamp between startdate + idate - 548 and startdate + idate

    group by case when postingtimestamp = startdate + idate

                   then 'current'

                   else 'prior'

              end,

              uentity01,

              servicetimestamp,

              postingtimestamp,

              actdeptcode,

              financialrecnum

    having sum(quantity) <> 0

    there's no need to evaluate the following two subqueries (four times each) any more

    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

    and

    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 startdate + idate - 548 and startdate + idate - 1

    group by c.uentity01,

              c.servicetimestamp,

              c.postingtimestamp,

              c.actdeptcode,

              c.financialrecnum

    having sum(c.quantity) <> 0

    so the query might look something like

    with

    q1 as

    (select case when postingtimestamp = startdate + idate

                 then 'current'

                 else 'prior'

            end period,

            uentity01 as entity,

            servicetimestamp,

            postingtimestamp,

            actdeptcode as dept,

            financialrecnum as har,

            sum(quantity) as qty

       from ads.chargeitem

      where uentity01 in ('1000','4100')

        and actdscrcode not in ('1700001','1710001','1720001','1730001','1740001')

        and (revenuecodecode between '100' and '219'

         or  revenuecodecode between '1000' and '1005'

            )

    --  AND FINANCIALRECNUM IN ('22150281')

            -- Current time period

        and postingtimestamp between startdate + idate - 548 and startdate + idate

      group by case when postingtimestamp = startdate + idate

                    then 'current'

                    else 'prior'

               end,

               uentity01,

               servicetimestamp,

               postingtimestamp,

               actdeptcode,

               financialrecnum

      having sum(quantity) <> 0

    )

    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 when q7.dept in ('10006113','10006115','10006116','10006118','10006119','10006120','41006100','41006101','41006105','41006110')

                then 'MED SURG'

                when q7.dept in ('10006146','10006147','10006250','41006130')

                then 'ADULT CRITICAL CARE'

                when q7.dept in ('10006130','10006148')

                then 'PEDIATRICS PICU'

                when q7.dept = '10006132'

                then 'ED'

                when q7.dept in ('10006144','10006145','41006160')

                then 'LABOR AND DELIVERY/OB'

                when q7.dept = '10006142'

                then 'NICU'

                when q7.dept in ('10006194','10006195')

                then 'BEHAVIORAL HEALTH'

                when q7.dept = '41006115'

                then 'ADULT INTERMEDIATE CARE'

                when q7.dept = '41006120'

                then 'PEDIATRICS'

           end as area,

           q7.dept,

           q7.har,

           sum(q7.qty) as qty

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

      from (select q6.entity,q6.postingtimestamp,q6.dept,q6.har,q6.qty

    -- START: DETERMINE CURRENT TIME PERIOD ADMIT QTY

              from (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)

                                  or (q5.prior_qty = 0 and q5.cur_qty > 0)

                                then 1

                                when (q5.prior_qty is null and q5.cur_qty = 0)

                                  or (q5.prior_qty = 0 and q5.cur_qty = 0)

                                  or (q5.prior_qty + q5.cur_qty > 0)

                                then 0

                                when q5.prior_qty + q5.cur_qty = 0

                                then -1

                           end as qty

    -- START: CONSOLIDATE CURRENT AND PRIOR TIME PERIODS

                      from (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

    -- START: GET ADMITS FOR CURRENT TIME PERIOD

                              from (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 entity,servicetimestamp,dept,har,qty

                                                                      from q1

                                                                     where period = 'current'

                                                                   ) q1

                                                             group by q1.entity,q1.har

                                                           ) q2,

                                                           (select entity,servicetimestamp,postingtimestamp,actdeptcodedept,har,qty

                                                              from q1

                                                             where period = 'current'

                                                           ) 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 entity,servicetimestamp,postingtimestamp,dept,har,qty

                                                      from q1

                                                     where period = 'current'

                                                   ) 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 entity,servicetimestamp,postingtimestamp,dept,har,qty

                                              from q1

                                             where period = 'current'

                                           ) 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

    -- START: GET ADMITS < CURRENT TIME PERIOD

                                   left outer join

                                   (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 entity,servicetimestamp,dept,har,qty

                                                                      from q1

                                                                     where period = 'prior'

                                                                   ) q1

                                                             group by q1.entity,q1.har

                                                           ) q2,

                                                           (select entity,servicetimestamp,postingtimestamp,dept,har,qty

                                                              from q1

                                                             where period = 'prior'

                                                           ) 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 entity,servicetimestamp,postingtimestamp,dept,har,qty

                                                      from q1

                                                     where period = 'prior'

                                                   ) 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 entity,servicetimestamp,postingtimestamp,dept,har,qty

                                              from q1

                                             where period = 'prior'

                                           ) 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)

                                                                                       or (Q5.PRIOR_QTY = 0 AND Q5.CUR_QTY > 0)

                                                                                     then 1

                                                                                     when (q5.prior_qty is null and q5.cur_qty = 0)

                                                                                       or (q5.prior_qty = 0 and q5.cur_qty = 0)

                                                                                       or (q5.prior_qty + q5.cur_qty > 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 when q7.dept in ('10006113','10006115','10006116','10006118','10006119','10006120')

                   then '1'

                   when q7.dept in ('10006146','10006147','10006250')

                   then '2'

                   when q7.dept in ('10006130','10006148')

                   then '3'

                   when q7.dept = '10006132'

                   then '4'

                   when q7.dept in ('10006144','10006145')

                   then '5'

                   when q7.dept = '10006142'

                   then '6'

                   when q7.dept in ('10006194','10006195')

                   then '7'

                   when q7.dept in ('41006100','41006101','41006105','41006110')

                   then '10'

                   when q7.dept = '41006130'

                   then '11'

                   when q7.dept = '41006115'

                   then '12'

                   when q7.dept = '41006120'

                   then '13'

                   when q7.dept = '41006160'

                   then '14'

              end,

    -- Area

        case when q7.dept in ('10006113','10006115','10006116','10006118','10006119','10006120','41006100','41006101','41006105','41006110')

             then 'MED SURG'

             when q7.dept in ('10006146','10006147','10006250','41006130')

             then 'ADULT CRITICAL CARE'

             when q7.dept in ('10006130','10006148')

             then 'PEDIATRICS PICU'

             when q7.dept = '10006132'

             then 'ED'

             when q7.dept in ('10006144','10006145','41006160')

             then 'LABOR AND DELIVERY/OB'

             when q7.dept = '10006142'

             then 'NICU'

             when q7.dept in ('10006194','10006195')

             then 'BEHAVIORAL HEALTH'

             when q7.dept = '41006115'

             then 'ADULT INTERMEDIATE CARE'

             when q7.dept = '41006120'

             then 'PEDIATRICS'

        end as area,

        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 when q7.dept in ('10006113','10006115','10006116','10006118','10006119','10006120')

                   then '1'

                   when q7.dept in ('10006146','10006147','10006250')

                   then '2'

                   when q7.dept in ('10006130','10006148')

                   then '3'

                   when q7.dept = '10006132'

                   then '4'

                   when q7.dept in ('10006144','10006145')

                   then '5'

                   when q7.dept = '10006142'

                   then '6'

                   when q7.dept in ('10006194','10006195')

                   then '7'

                   when q7.dept in ('41006100','41006101','41006105','41006110')

                   then '10'

                   when q7.dept = '41006130'

                   then '11'

                   when q7.dept = '41006115'

                   then '12'

                   when q7.dept = '41006120'

                   then '13'

                   when q7.dept = '41006160'

                   then '14'

              end asc,

              q7.dept asc,

              q7.har asc

    by the way:

    try not to commit within the loop

    dont use to_date on dates:

    AND C.POSTINGTIMESTAMP BETWEEN (TO_DATE((startdate + idate)) - 548) AND (TO_DATE((startdate + idate)) - 1)

    seems the left outer join betwwen current and prior period could be replaced by a pivot after evaluating the two periods together (as the new q1 subquery) but you provided no sample data neither your Oracle version as Frank asked for already.

    Regards

    Etbin

    · Share on TwitterShare on Facebook
This discussion has been closed.