on 2014 Mar 24 2:40 PM
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
Request clarification before answering.
User | Count |
---|---|
96 | |
11 | |
9 | |
9 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.