Sybase

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSybase

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old March 24th, 2014, 03:10 PM
pandu pandu is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2014
Posts: 1 pandu User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 53 sec
Reputation Power: 0
Thumbs up Challenge: reduce stored procedure runtime

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

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSybase > Challenge: reduce stored procedure runtime


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap