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;