|
Hi Guys, I'm trying to reduce runtime(optimize)of the stored procedure which is mentioned below. I found the problem is with cursor using in stored procedure. Some one suggest me to use function or while loop instead of cursor. Can any one try to replace cursor with something to reduce run time. Please help me guys. Thank you. CREATE PROCEDURE "kvarma"."FDSSP_UMC023_PHARMACY_SELECT_ORIGINAL"
(
@p_RUN_FROM_DATE CHAR(10),
@p_RUN_TO_DATE CHAR(10),
@p_GRGR_ID VARCHAR(100) --Version 1.11 change
)
AS
BEGIN
/**********************************************************************
** Declare local variables **
**********************************************************************/
DECLARE
@l_UMUM_REF_ID CHAR(9) ,
@l_LOC_UMUM_REF_ID CHAR(9) ,
@l_RunDate DATETIME ,
@l_RetCode INT ,
@l_RowCount INT ,
@d_Firstday DATE ,
@d_Lastday DATE ,
@d_RUN_FROM_DATE DATE ,
@d_RUN_TO_DATE DATE ,
@l_NTNB_ID DATETIME ,
@l_COMMENTS CHAR(141) ,
@l_DRUG_REQ CHAR(141) ,
@l_LOC_NTNB_ID DATETIME ,
@l_CON_COMMENTS VARCHAR(500) ,
@l_CON_DRG_REQ VARCHAR(500) ,
@l_NTTX_SEQ_NO int ,
@msg VARCHAR(100) ,
@l_GRGR_ID VARCHAR(100) --Version 1.11 change
/**********************************************************************
** Getting the First & Last day of the month. **
**********************************************************************/
if @p_RUN_FROM_DATE <>'00/00/0000'
BEGIN
SELECT @p_RUN_FROM_DATE = SUBSTRING(@p_RUN_FROM_DATE,7,4)+'-'+SUBSTRING(@p_RUN_FROM_DATE,1,2)+'-'+SUBSTRING(@p_RUN_FROM_DATE,4,2)
SELECT @d_RUN_FROM_DATE=CONVERT(DATE,@p_RUN_FROM_DATE)
SELECT @d_Firstday = @d_RUN_FROM_DATE
END
if @p_RUN_TO_DATE <>'00/00/0000'
BEGIN
SELECT @p_RUN_TO_DATE = SUBSTRING(@p_RUN_TO_DATE,7,4)+'-'+SUBSTRING(@p_RUN_TO_DATE,1,2)+'-'+SUBSTRING(@p_RUN_TO_DATE,4,2)
SELECT @d_RUN_TO_DATE=CONVERT(DATE,@p_RUN_TO_DATE)
SELECT @d_Lastday = @d_RUN_TO_DATE
END
IF ISNULL(LTRIM(@p_RUN_FROM_DATE),'00/00/0000' ) = '00/00/0000'
AND ISNULL(LTRIM(@p_RUN_TO_DATE),'00/00/0000' ) = '00/00/0000'
BEGIN -- If none of the dates are given
-- period is the month of the cofg table date
EXEC @l_RetCode = kvarma.FDSSP_GET_CURRENT_DATE @l_RunDate OUTPUT
IF (@l_RetCode != 0)
BEGIN
SELECT @msg = 'exec FDSSP_GET_CURRENT_DATE failed!'
PRINT @msg
RETURN
END
SELECT @d_Firstday = @l_RunDate
SELECT @d_Lastday = @l_RunDate
END
/**********************************************************************
** Create temporary tables **
**********************************************************************/
CREATE TABLE #FTM_UMC023_REC
(
GROUP_NAME VARCHAR(39) NULL , --Version 1.11 change
UMSV_RECD_DT DATETIME NULL ,
INI_AUTH_NURSE CHAR(82) NULL ,
REVIEW_REQUEST CHAR(82) NULL ,
UMUM_REF_ID CHAR(9) NULL ,
MCTR_DESC CHAR(30) NULL ,
UMVT_SEQ_NO SMALLINT NULL ,
UMSV_SEQ_NO SMALLINT NULL ,
MD_NAME CHAR(55) NULL ,
PRCF_MCTR_SPEC CHAR(4) NULL ,
PRAC_SPECIALITY CHAR(30) NULL ,
AREA_CODE CHAR(3) NULL ,
COUNTY CHAR(20) NULL ,
INDICATION CHAR(127) NULL ,
UM_COST_STS CHAR(35) DEFAULT SPACE(35) ,
UM_COST_TYPE CHAR(35) DEFAULT SPACE(35) ,
NTNB_ID DATETIME NULL ,
DRUG_REQ VARCHAR(500) NULL ,
COMMENTS VARCHAR(500) NULL ,
SUBJECT_TYPE VARCHAR(500) NULL ,
NOTE_TYPE VARCHAR(500) NULL ,
PROJECTED_COST MONEY DEFAULT 0.0 ,
ACTUAL_COST MONEY DEFAULT 0.0 ,
MEME_LAST_NAME CHAR(35) NULL ,
MEME_FIRST_NAME CHAR(15) NULL ,
MEMBER_NAME VARCHAR(52) NULL ,
MEME_SFX CHAR(3) NULL ,
MEME_SEX CHAR(1) NULL ,
MEMBER_ID VARCHAR(12) NULL ,
MEME_BIRTH_DT DATETIME NULL ,
PATIENT_AGE INT NULL ,
UMSV_PRPR_ID_REQ CHAR(12) NULL ,
MEME_CK INT NULL ,
GRGR_CK INT NULL ,
SBSB_CK INT NULL ,
UMSV_FROM_DT DATETIME NULL ,
UMSV_TO_DT DATETIME NULL ,
UMVT_STS CHAR(30) NULL ,
UMVT_STS_REAS CHAR(30) NULL ,
UMSV_MCTR_LDNY CHAR(4) NULL ,
FLAG INT DEFAULT 0 ,
UMCL_SEQ_NO INT DEFAULT 0 ,
UMUM_PDPD_ID CHAR(12) NULL ,
UMUM_PDPD_ID1 CHAR(12) NULL ,
EXPD_STND VARCHAR(500) NULL ,
CSPI_ID CHAR(8) NULL , -- Version 2.1
UMSV_MCTR_CALL CHAR(4) NULL ,
NTNB_UPD_DTM DATETIME NULL ,
UMSV_TYPE CHAR(1) NULL ,
TIMELY_MCTR_DESC CHAR(255) NULL ,
UMSV_MICRO_ID CHAR(18) NULL ,
UMSV_MCTR_PVIO CHAR(4) NULL,
ATXR_SOURCE_ID DATETIME NULL ,
IDCD_TYPE VARCHAR(4) NULL,
IDCD_DESC CHAR(228) NULL
)
CREATE TABLE #FTM_UMC023_NOTES
(
UMUM_REF_ID CHAR(9) NULL ,
NTNB_ID DATETIME NULL ,
NTNB_SUMMARY CHAR(70) NULL ,
NTTX_TEXT CHAR(70) NULL ,
NTNB_MCTR_SUBJ CHAR(4) NULL ,
DRUG_REQ CHAR(500) NULL ,
COMMENTS CHAR(500) NULL ,
NTTX_SEQ_NO INT NULL
)
CREATE TABLE #FTM_UMC023_CONT
(
UMUM_REF_ID CHAR(9) NULL ,
NTNB_ID DATETIME NULL ,
DRUG_REQ VARCHAR(500) NULL ,
COMMENTS VARCHAR(500) NULL ,
NTTX_SEQ_NO INT NULL
)
CREATE TABLE #FTM_UMC023_UMCL_SEQNO
(
UMUM_REF_ID CHAR(9) NULL ,
UMCL_SEQ_NO INT DEFAULT 0
)
--Version 1.11 change begins
/*********************************************************************
** Create temporary tables **
*********************************************************************/
CREATE TABLE
#FTM_RPT_PARAM
(
PARAM_NAME VARCHAR(7) ,
PARAM_VALUE VARCHAR(8)
)
IF ISNULL(LTRIM(RTRIM(@p_GRGR_ID)),'') <> ''
BEGIN
SELECT
@l_GRGR_ID = LTRIM(RTRIM(@p_GRGR_ID))
END
EXEC @l_RetCode = kvarma.FDSSP_TEMP_TABLE_INSERT 'GRGR_ID',@l_GRGR_ID
IF (@l_RetCode != 0)
BEGIN
SELECT @msg = 'EXEC FDSSP_TEMP_TABLE_INSERT FAILED!'
PRINT @msg
RETURN
END
--Version 1.11 change ends
/**********************************************************************
** Populating the temporary table for **
** #FTM_UMC023_REC **
**********************************************************************/
INSERT INTO #FTM_UMC023_REC
(
GROUP_NAME , --Version 1.11 change
UMUM_REF_ID ,
UMSV_RECD_DT ,
INDICATION ,
UMSV_PRPR_ID_REQ ,
UMVT_SEQ_NO ,
UMSV_SEQ_NO ,
NTNB_ID ,
GRGR_CK ,
SBSB_CK ,
MEME_CK ,
UMSV_FROM_DT ,
UMSV_TO_DT ,
INI_AUTH_NURSE ,
UMVT_STS ,
UMSV_MCTR_LDNY ,
UMUM_PDPD_ID ,
UMUM_PDPD_ID1 ,
EXPD_STND ,
CSPI_ID ,-- Version 2.1
UMSV_MCTR_CALL ,
UMSV_TYPE ,
UMSV_MICRO_ID ,
UMSV_MCTR_PVIO,
ATXR_SOURCE_ID
)
SELECT
GRGR.GRGR_ID + '-' + --Version 1.11 change
MCTR.MCTR_DESC ,--Version 1.11 change
UMUM.UMUM_REF_ID ,
UMSV.UMSV_RECD_DT ,
UMSV.UMSV_IDCD_ID_PRI ,
UMSV.UMSV_PRPR_ID_REQ ,
UMSV.UMVT_SEQ_NO ,
UMSV.UMSV_SEQ_NO ,
UMUM.NTNB_ID ,
UMUM.GRGR_CK ,
UMUM.SBSB_CK ,
UMUM.MEME_CK ,
UMSV.UMSV_FROM_DT ,
UMSV.UMSV_TO_DT ,
UMUM.UMUM_CREATE_USID ,
CASE UMSV.UMVT_STS
WHEN 'CL' THEN 'Void'
WHEN 'CO' THEN 'Approved'
WHEN 'DS' THEN 'Disallowed'
WHEN 'IN' THEN 'Pended'
WHEN 'LG' THEN 'Pended'
WHEN 'PD' THEN 'Pended'
WHEN 'UP' THEN 'Pended'
ELSE ''
END ,
UMSV.UMSV_MCTR_LDNY,
SUBSTRING(UMUM.CSPI_ID, 1, 3) as LOB ,
CASE
WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='P' and
(SUBSTRING(UMUM.CSPI_ID, 1, 1)='M'
or SUBSTRING(UMUM.CSPI_ID, 1, 3)='D')THEN 'MA-PD'
WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='N' and
(SUBSTRING(UMUM.PDPD_ID, 1, 1)='D'
or SUBSTRING(UMUM.CSPI_ID, 1, 1)='M') THEN 'MA'
WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='A' and
(SUBSTRING(UMUM.CSPI_ID, 1, 1)='M'
or SUBSTRING(UMUM.PDPD_ID, 1, 1)='D')THEN 'MA-PD Dual'
ELSE ' '
END AS MEDICARE,
Space(500) AS EXPD ,
UMUM.CSPI_ID , -- Version 2.1
UMSV.UMSV_MCTR_CALL ,
UMSV.UMSV_TYPE ,
UMSV.UMSV_MICRO_ID ,
UMSV.UMSV_MCTR_PVIO,
UMSV.ATXR_SOURCE_ID
FROM
ODS_FACETS.CMC_UMUM_UTIL_MGT UMUM
INNER JOIN
ODS_FACETS.CMC_UMSV_SERVICES UMSV
ON
UMUM.MEME_CK = UMSV.MEME_CK
AND
UMUM.UMUM_REF_ID = UMSV.UMUM_REF_ID
--Version 1.11 change begins
INNER JOIN
ODS_FACETS.CMC_GRGR_GROUP GRGR
ON
UMUM.GRGR_CK = GRGR.GRGR_CK
AND
UPPER(LTRIM(RTRIM(GRGR.GRGR_ID))) IN (
SELECT
PARAM_VALUE
FROM
#FTM_RPT_PARAM
WHERE
PARAM_NAME = 'GRGR_ID'
)
INNER JOIN ODS_FACETS.CMC_MCTR_CD_TRANS MCTR
ON MCTR.MCTR_ENTITY = 'GRGR'
AND MCTR.MCTR_TYPE = 'TYPE'
AND MCTR.MCTR_VALUE = GRGR.GRGR_MCTR_TYPE
--Version 1.11 change ends
WHERE
UMSV.SESE_ID in (SELECT TEXT_VAL
FROM ODS_FACETS.fid_FDS_CNFG_CONFIG
WHERE ENTITY = 'UMC023_PHARM_OVR') --Pharmacy Override cases
AND
UMSV.UMSV_RECD_DT BETWEEN @d_Firstday AND @d_Lastday
/**********************************************************************
** Updating the temporary table **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
MD_NAME = PRPR.PRPR_NAME ,
AREA_CODE = SUBSTRING(PRAD.PRAD_PHONE,1,3) ,
COUNTY = PRAD.PRAD_COUNTY ,
PRCF_MCTR_SPEC = PRPR.PRCF_MCTR_SPEC ,
FLAG = 1
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_PRPR_PROV PRPR
ON
FTMP.UMSV_PRPR_ID_REQ = PRPR.PRPR_ID
INNER JOIN
ODS_FACETS.CMC_PRAD_ADDRESS PRAD
ON
PRPR.PRAD_ID = PRAD.PRAD_ID
AND
PRAD.PRAD_TYPE = 'PRI'
AND
FTMP.UMSV_RECD_DT BETWEEN PRAD_EFF_DT AND PRAD_TERM_DT
/**********************************************************************
** Deletion from #FTM_UMC023_REC **
**********************************************************************/
DELETE FROM #FTM_UMC023_REC
WHERE FLAG = 0
/**********************************************************************
** Updating the temporary table **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
MEME_LAST_NAME = ISNULL(RTRIM(LTRIM(MEME.MEME_LAST_NAME)),'' ) ,
MEME_FIRST_NAME = ISNULL(RTRIM(LTRIM(MEME.MEME_FIRST_NAME)),'' ) ,
MEME_SFX = CASE
WHEN MEME.MEME_SFX IS NULL THEN ''
ELSE '-'+ RIGHT('0'+LTRIM(RTRIM(CONVERT(CHAR(2),
MEME.MEME_SFX))),2)
END ,
MEME_SEX = MEME.MEME_SEX ,
MEME_BIRTH_DT = MEME.MEME_BIRTH_DT
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_MEME_MEMBER MEME
ON
FTMP.GRGR_CK = MEME.GRGR_CK
AND
FTMP.MEME_CK = MEME.MEME_CK
UPDATE #FTM_UMC023_REC
SET
MEME_LAST_NAME = RTRIM(LTRIM(MEME_LAST_NAME)) ,
MEME_FIRST_NAME = RTRIM(LTRIM(MEME_FIRST_NAME))
FROM
#FTM_UMC023_REC
/**********************************************************************
** Member Name and Patient Age **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
MEMBER_NAME = CASE
WHEN MEME_LAST_NAME <>'' AND MEME_FIRST_NAME <> ''
THEN RTRIM(MEME_LAST_NAME) + ', ' + RTRIM(MEME_FIRST_NAME)
WHEN MEME_LAST_NAME ='' AND MEME_FIRST_NAME <> ''
THEN MEME_FIRST_NAME
WHEN MEME_LAST_NAME <>'' AND MEME_FIRST_NAME = ''
THEN MEME_LAST_NAME
ELSE ''
END ,
PATIENT_AGE = CASE
WHEN DATEPART(MM,@d_Lastday) <
DATEPART(MM,MEME_BIRTH_DT )
THEN (DATEDIFF (YY,
MEME_BIRTH_DT,@d_Lastday ) -1 )
WHEN DATEPART(MM,@d_Lastday)=
DATEPART(MM,MEME_BIRTH_DT)
AND DATEPART(dd,@d_Lastday) <
DATEPART(dd,MEME_BIRTH_DT)
THEN (DATEDIFF (YY,
MEME_BIRTH_DT,@d_Lastday) -1 )
ELSE DATEDIFF (YY,
MEME_BIRTH_DT,@d_Lastday)
END
UPDATE #FTM_UMC023_REC -- Set the Age = 0 when the Age is < 0
SET
PATIENT_AGE = 0
WHERE
PATIENT_AGE < 0
/**********************************************************************
** Member ID **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
MEMBER_ID = LTRIM(RTRIM(SBSB.SBSB_ID)) + FTMP.MEME_SFX
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_SBSB_SUBSC SBSB
ON
FTMP.GRGR_CK = SBSB.GRGR_CK
AND
FTMP.SBSB_CK = SBSB.SBSB_CK
/**********************************************************************
** note and subject types **
**********************************************************************/
-----------------------------------------------------------------------
UPDATE #FTM_UMC023_REC
SET
NOTE_TYPE = 'Medication',
SUBJECT_TYPE = MCTR_SPEC.MCTR_DESC
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_NTNB_NOTE_BASE NTNB
ON
FTMP.NTNB_ID = NTNB.NTNB_ID
AND NTNB.NTNB_CAT = 'RX'
INNER JOIN
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR_SPEC
ON
MCTR_SPEC.MCTR_VALUE = NTNB.NTNB_MCTR_SUBJ
AND
MCTR_SPEC.MCTR_ENTITY = 'NTNB'
AND
MCTR_SPEC.MCTR_TYPE = 'SUBJ'
-----------------------------------------------------------------------
/**********************************************************************
** Getting the Provider's Speciality **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
PRAC_SPECIALITY = MCTR_SPEC.MCTR_DESC
FROM
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR_SPEC
INNER JOIN
#FTM_UMC023_REC FTMP
ON
MCTR_SPEC.MCTR_VALUE = FTMP.PRCF_MCTR_SPEC
WHERE
MCTR_SPEC.MCTR_ENTITY = 'PRAC'
AND
MCTR_SPEC.MCTR_TYPE = 'SPEC'
/**********************************************************************
** Getting the Primary Diagnosis Code **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
IDCD_TYPE = IDCD.IDCD_TYPE,
IDCD_DESC = IDCD.IDCD_DESC,
INDICATION = CASE
WHEN ISNULL(LTRIM(RTRIM(IDCD.IDCD_DESC)),'') = ''
THEN FTMP.INDICATION
ELSE FTMP.INDICATION + '-'
+ LTRIM(RTRIM(IDCD.IDCD_DESC))
END
FROM
ODS_FACETS.CMC_IDCD_DIAG_CD IDCD
INNER JOIN
#FTM_UMC023_REC FTMP
ON
IDCD.IDCD_ID = FTMP.INDICATION
AND
IDCD.IDCD_TYPE IN ('I', 'T')
/**********************************************************************
** Insert max UMCL seq # corresponding to the auth in the temp table **
**********************************************************************/
INSERT INTO #FTM_UMC023_UMCL_SEQNO
(
UMUM_REF_ID ,
UMCL_SEQ_NO
)
SELECT
FTMP.UMUM_REF_ID ,
MAX(UMCL.UMCL_SEQ_NO)
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_UMCL_COST_LOG UMCL
ON
FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID
GROUP BY
FTMP.UMUM_REF_ID
/**********************************************************************
** Update the max UMCL seq no in the main temp table **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
UMCL_SEQ_NO = UMCL.UMCL_SEQ_NO
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
#FTM_UMC023_UMCL_SEQNO UMCL
ON
FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID
/**********************************************************************
** Drop the temporary table which stored sequence # **
**********************************************************************/
DROP TABLE #FTM_UMC023_UMCL_SEQNO
/**********************************************************************
** Getting the UM Cost Status **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
UM_COST_STS = ISNULL(LTRIM(RTRIM(MCTR_UMCL.MCTR_DESC)),''),
UM_COST_TYPE = UMCL.UMCL_MCTR_COST ,
PROJECTED_COST = UMCL.UMCL_PROJ_COST ,
ACTUAL_COST = UMCL.UMCL_ACT_COST
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_UMCL_COST_LOG UMCL
ON
FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID
AND
FTMP.UMCL_SEQ_NO = UMCL.UMCL_SEQ_NO
LEFT OUTER JOIN
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR_UMCL
ON
UMCL.UMCL_MCTR_CSTS = MCTR_UMCL.MCTR_VALUE
AND
MCTR_UMCL.MCTR_ENTITY = 'UMCL'
AND
MCTR_UMCL.MCTR_TYPE = 'CSTS'
/**********************************************************************
** Getting the UM Cost Type **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
UM_COST_TYPE = ISNULL(LTRIM(RTRIM(UMCL_COST.MCTR_DESC)),'')
FROM
ODS_FACETS.CMC_MCTR_CD_TRANS UMCL_COST
INNER JOIN
#FTM_UMC023_REC FTMP
ON
UMCL_COST.MCTR_VALUE = FTMP.UM_COST_TYPE
AND
UMCL_COST.MCTR_ENTITY = 'UMCL'
AND
UMCL_COST.MCTR_TYPE = 'COST'
/**********************************************************************
Getting the UMVT STATUS CODE REASON DESCRIPTION **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
UMVT_STS_REAS = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_UMVT_STATUS UMVT
ON
FTMP.UMUM_REF_ID = UMVT.UMUM_REF_ID
AND
FTMP.UMSV_SEQ_NO = UMVT.UMSV_SEQ_NO
AND
FTMP.UMVT_SEQ_NO = UMVT.UMVT_SEQ_NO
INNER JOIN
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR
ON
UMVT.UMVT_MCTR_REAS = MCTR.MCTR_VALUE
AND
MCTR_ENTITY = 'UMVT'
AND
MCTR_TYPE = 'REAS'
/**********************************************************************
Getting the REASON DESCRIPTION FOR DISALLOWED CASES **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
UMVT_STS_REAS = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR
ON
FTMP.UMSV_MCTR_LDNY = MCTR.MCTR_VALUE
AND
MCTR.MCTR_ENTITY = 'UMSV'
AND
MCTR.MCTR_TYPE = 'LDNY'
AND
FTMP.UMVT_STS = 'Disallowed'
/**********************************************************************
** Getting the Reviewer User ID and Description **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
REVIEW_REQUEST = UMVT.USUS_ID
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_UMVT_STATUS UMVT
ON
FTMP.UMUM_REF_ID = UMVT.UMUM_REF_ID
AND
FTMP.UMSV_SEQ_NO = UMVT.UMSV_SEQ_NO
AND
FTMP.UMVT_SEQ_NO = UMVT.UMVT_SEQ_NO
/**********************************************************************
** Populating the temporary table for **
** #FTM_UMC023_NOTES **
**********************************************************************/
INSERT INTO #FTM_UMC023_NOTES
(
UMUM_REF_ID ,
NTNB_ID ,
NTNB_SUMMARY ,
NTTX_TEXT ,
NTNB_MCTR_SUBJ ,
COMMENTS ,
NTTX_SEQ_NO
)
SELECT
FTMP.UMUM_REF_ID ,
NTNB.NTNB_ID ,
NTNB.NTNB_SUMMARY ,
NTTX.NTTX_TEXT ,
NTNB.NTNB_MCTR_SUBJ ,
CASE
WHEN ISNULL(LTRIM(RTRIM(NTTX.NTTX_TEXT)),'') = ''
THEN LTRIM(RTRIM(NTNB.NTNB_SUMMARY))
ELSE LTRIM(RTRIM(NTNB.NTNB_SUMMARY)) + '-'
+ LTRIM(RTRIM(NTTX.NTTX_TEXT))
END ,
NTTX.NTTX_SEQ_NO
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_NTNB_NOTE_BASE NTNB
ON
FTMP.NTNB_ID = NTNB.NTNB_ID
INNER JOIN
ODS_FACETS.CMC_NTTX_NOTE_TEXT NTTX
ON
NTNB.NTNB_ID = NTTX.NTNB_ID
AND
NTNB.NTNB_INPUT_DTM = NTTX.NTNB_INPUT_DTM
WHERE
NTNB.NTNB_CAT = 'RX'
AND
NTNB.NTNB_MCTR_SUBJ IN ('NS44','NS96','NS97','NU78','NU79','NU80','NU81','NU82','NU83','NU84','NU85','NU86','NU87','NU88','NU89','NU90','NU91','NU92','NU93')
/**********************************************************************
** Getting the Comments and Drug Requested **
**********************************************************************/
DECLARE COMMENTS_CURSOR CURSOR
FOR
SELECT
FTMP.UMUM_REF_ID ,
FTMP.NTNB_ID ,
FTMP.NTTX_TEXT ,
FTMP.DRUG_REQ ,
FTMP.NTTX_SEQ_NO
FROM
#FTM_UMC023_NOTES FTMP
ORDER BY
FTMP.UMUM_REF_ID ,
FTMP.NTTX_SEQ_NO DESC
OPEN COMMENTS_CURSOR
FETCH COMMENTS_CURSOR INTO
@l_UMUM_REF_ID ,
@l_NTNB_ID ,
@l_COMMENTS ,
@l_DRUG_REQ ,
@l_NTTX_SEQ_NO
SELECT
@l_LOC_NTNB_ID = @l_NTNB_ID ,
@l_CON_COMMENTS= '' ,
@l_CON_DRG_REQ = '' ,
@l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID
WHILE (@@sqlstatus <> 2)
BEGIN
IF ( @l_UMUM_REF_ID != @l_LOC_UMUM_REF_ID)
BEGIN
INSERT INTO #FTM_UMC023_CONT
(
UMUM_REF_ID ,
NTNB_ID ,
COMMENTS ,
DRUG_REQ
)
SELECT
@l_LOC_UMUM_REF_ID ,
@l_LOC_NTNB_ID ,
@l_CON_COMMENTS ,
@l_CON_DRG_REQ
SELECT
@l_LOC_NTNB_ID = @l_NTNB_ID ,
@l_CON_COMMENTS='' ,
@l_CON_DRG_REQ = '' ,
@l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID
SELECT
@l_CON_COMMENTS =
ISNULL(LTRIM(RTRIM( @l_COMMENTS)),'')
SELECT
@l_CON_DRG_REQ =
CASE
WHEN @l_DRUG_REQ IS NULL
THEN @l_CON_DRG_REQ
ELSE ISNULL(LTRIM(RTRIM(@l_CON_DRG_REQ )),'')
+ ISNULL(LTRIM(RTRIM( @l_DRUG_REQ)),'') + ';'
END
END
ELSE
BEGIN
IF (@l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID)
BEGIN
SELECT @l_CON_COMMENTS =
CASE
WHEN @l_COMMENTS IS NULL
THEN @l_CON_COMMENTS
ELSE ISNULL(LTRIM(RTRIM( @l_COMMENTS)),'')
+ ' '
+ ISNULL(LTRIM(RTRIM(@l_CON_COMMENTS)),'')
END
SELECT @l_CON_DRG_REQ =
CASE
WHEN @l_DRUG_REQ IS NULL
THEN @l_CON_DRG_REQ
ELSE ISNULL(LTRIM(RTRIM(@l_CON_DRG_REQ )),'')
+ ISNULL(LTRIM(RTRIM( @l_DRUG_REQ)),'') + ';'
END
END
END
FETCH COMMENTS_CURSOR INTO
@l_UMUM_REF_ID ,
@l_NTNB_ID ,
@l_COMMENTS ,
@l_DRUG_REQ ,
@l_NTTX_SEQ_NO
END
INSERT INTO #FTM_UMC023_CONT
(
UMUM_REF_ID ,
NTNB_ID ,
COMMENTS ,
DRUG_REQ
)
SELECT
@l_UMUM_REF_ID ,
@l_LOC_NTNB_ID ,
@l_CON_COMMENTS ,
@l_CON_DRG_REQ
CLOSE COMMENTS_CURSOR
DEALLOCATE CURSOR COMMENTS_CURSOR
/**********************************************************************
** Getting the Comments and Drug Requested **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
COMMENTS = CONT.COMMENTS
FROM
#FTM_UMC023_CONT CONT
INNER JOIN
#FTM_UMC023_REC FTMP
ON
CONT.UMUM_REF_ID = FTMP.UMUM_REF_ID
UPDATE #FTM_UMC023_REC
SET
DRUG_REQ = CONT.NTNB_SUMMARY
FROM
#FTM_UMC023_NOTES CONT
INNER JOIN
#FTM_UMC023_REC FTMP
ON
CONT.UMUM_REF_ID = FTMP.UMUM_REF_ID
/**********************************************************************
** Update Notes Only for the cases where NTNB_CAT = "GN" **
** AND NTNB_MCTR_SUBJ IN ("NT78","NT79","NT82", "NT83") **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET EXPD_STND = NTNB_MCTR_SUBJ
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_NTNB_NOTE_BASE NTNB
ON
FTMP.NTNB_ID = NTNB.NTNB_ID
INNER JOIN
ODS_FACETS.CMC_NTTX_NOTE_TEXT NTTX
ON
NTNB.NTNB_ID = NTTX.NTNB_ID
AND
NTNB.NTNB_INPUT_DTM = NTTX.NTNB_INPUT_DTM
WHERE
NTNB.NTNB_CAT = 'GN'
AND
NTNB.NTNB_MCTR_SUBJ IN ('NT78','NT79', 'NT82', 'NT83')
/*********************************************************************
UPDATE NTNB_UPD_DTM
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET NTNB_UPD_DTM = NTNB.NTNB_UPD_DTM
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_NTNB_NOTE_BASE NTNB
ON
FTMP.NTNB_ID = NTNB.NTNB_ID
AND NTNB.NTNB_CAT = 'PA'
AND NTNB.NTNB_MCTR_SUBJ = 'NS89'
/********************************************************************
UPDATE Timely/Untimely
*********************************************************************/
UPDATE #FTM_UMC023_REC
SET
TIMELY_MCTR_DESC = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
#FTM_UMC023_REC FTMP
INNER JOIN
ODS_FACETS.CMC_MCTR_CD_TRANS MCTR
ON FTMP.UMSV_MCTR_PVIO = MCTR.MCTR_VALUE
AND MCTR.MCTR_ENTITY = 'UMSV'
AND MCTR.MCTR_TYPE = 'PVIO'
/**********************************************************************
** Final Result Set **
**********************************************************************/
SELECT
GROUP_NAME , --Version 1.11 change
CSPI_ID , -- Version 2.1
UMSV_RECD_DT ,
INI_AUTH_NURSE ,
REVIEW_REQUEST ,
UMUM_REF_ID ,
MEMBER_NAME ,
MEMBER_ID ,
PATIENT_AGE ,
MD_NAME ,
PRAC_SPECIALITY ,
MEME_SEX ,
AREA_CODE ,
COUNTY ,
INDICATION ,
UM_COST_STS ,
UM_COST_TYPE ,
PROJECTED_COST ,
ACTUAL_COST ,
DRUG_REQ ,
COMMENTS ,
SUBJECT_TYPE ,
NOTE_TYPE ,
UMSV_FROM_DT ,
UMSV_TO_DT ,
UMVT_STS ,
UMVT_STS_REAS ,
UMUM_PDPD_ID ,
UMUM_PDPD_ID1 ,
EXPD_STND ,
CASE WHEN ISNULL(LTRIM(RTRIM(UMSV_MCTR_CALL)),'') = ''
THEN 'BLANK'
ELSE LTRIM(RTRIM(UMSV_MCTR_CALL))
END 'Category of Med' ,
NTNB_UPD_DTM ,
UMSV_TYPE ,
TIMELY_MCTR_DESC ,
UMSV_MICRO_ID ,
'Style_Id' = ATXR.ATSY_ID ,
'Document_ID' =ATLD_ID ,
IDCD_TYPE ,
IDCD_DESC
FROM
#FTM_UMC023_REC FTM
LEFT OUTER JOIN
ODS_FACETS.CER_ATXR_ATTACH_U ATXR
ON ATXR.ATXR_SOURCE_ID = FTM.ATXR_SOURCE_ID
AND ATTB_ID = 'UMSV'
LEFT OUTER JOIN
ODS_FACETS.CER_ATLT_LETTER_D ATLT
ON ATXR.ATSY_ID = ATLT.ATSY_ID
AND ATXR.ATXR_DEST_ID = ATLT.ATXR_DEST_ID
-----------
ORDER BY
GROUP_NAME , --Version 1.11 change
UMSV_RECD_DT DESC ,
UMUM_REF_ID
RETURN 0
END
|
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 24 Mar '14, 14:40
question was seen: 2,419 times
last updated: 25 Mar '14, 04:26
How do you know it is the cursor that is taking the time?
Have you run the stored procedure profiler to see where the time is going?
Have you looked at the graphical plan with statistics for any of the queries that you know are taking up a lot of time?
What version of SQL Anywhere are you using? Please tell me it is SQL Anywhere, not Sybase ASE or Microsoft SQL Server... which is what it looks like because the code's written in Transact SQL rather than Watcom SQL.
Thanks you for the reply
1) Yes I know it is the cursor which is taking time because I have run the SP to see where it is taking time and for cursor it is taking 3 hrs to run the store procedure.
2) no I didnt look at the grapical plan, this is the first time I am using rapid SQL(sybase).
3) I am using sybase IQ rapid sql
Please let me know if you have any question that I have to answer. Once again thanks for your reply.
If "sybase IQ rapid sql" means you are using Embarcadero's Rapid SQL product on a Sybase IQ database, then this is the wrong forum. This forum is dedicated to Sybase SQL Anywhere which is a different product from Sybase IQ when it comes to performance (the SQL language is very close but the underlying query engines are different).
"I have run the SP to see where it is taking time" - exactly HOW did you do that? Does Rapid SQL have a profiler for IQ?