Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-01 : 07:28:35
|
| anibus writes "SELECT CS.ENC_NO AS ENCNO ,CS.CLAIM_NO AS CLAIMNO ,CS.VOID_STAT_CD AS VOIDSTAT ,CS.FACIL_ID AS FACILID ,FAC.FACIL_NM AS FACILNM ,FAC.TAX_ID_NO AS TAXID ,CS.SERV_PHY_ID AS PHYID ,PHY.PHY_NM AS PHYNM ,CS.PERSON_NO AS PERSONNO ,PERS.EXTERNAL_PERSON_ID AS MEMBERID ,PROD.PURCH_TP_NM AS PRCHTPNM ,PROD.PURCH_SUB_TP_NM AS PRCHSUB ,C.ADMIT_DT AS ADMITDT ,C.DSCHRG_DT AS DISCHDT ,CS.FIRST_SERV_DT AS FIRSTDOS ,CS.LAST_SERV_DT AS LASTDOS ,(CASE WHEN CS.CPT4_CD = 'NA' THEN CS.RVN_CPT4_CD ELSE CS.CPT4_CD END) AS PROCCD ,(CASE WHEN CS.CPT4_CD = 'NA' THEN CS.RVN_CPT4_MOD_CD ELSE CS.PROC_MOD_CD END) AS PROCMOD ,C.PRI_ICD9_PROC_CD AS PRIMPROC ,PR.ICD9_PROC1_CD AS PROC1 ,PR.ICD9_PROC2_CD AS PROC2 ,PR.ICD9_PROC3_CD AS PROC3 ,PR.ICD9_PROC4_CD AS PROC4 ,PR.ICD9_PROC5_CD AS PROC5 ,PR.ICD9_PROC6_CD AS PROC6 ,CS.ICD9_DX_CD AS DIAG1 ,ICD9.ICD9_DX2_CD AS DIAG2 ,ICD9.ICD9_DX3_CD AS DIAG3 ,ICD9.ICD9_DX4_CD AS DIAG4 ,ICD9.ICD9_DX5_CD AS DIAG5 ,ICD9.ICD9_DX6_CD AS DIAG6 ,ICD9.ICD9_DX7_CD AS DIAG7 ,ICD9.ICD9_DX8_CD AS DIAG8 ,ICD9.ICD9_DX9_CD AS DIAG9 ,CS.SERV_CAT_LEVEL1_ID AS SVCATID ,SUM(CS.COB_CALC_AMT) AS COBAMT ,SUM(CS.CHG_AMT) AS BILLAMT ,SUM(CS.COIN_AMT + CS.COPAY_AMT + CS.DEDUCT_AMT + CS.OTH_MEM_LIAB_AMT) AS MEMSUM ,SUM(CS.TO_BE_PAID_AMT + CS.ADVANC_AMT) AS CHECKPD ,SUM(CS.TO_BE_PAID_AMT + CS.ADVANC_AMT + CS.PRE_PAID_AMT) AS PLANPD ,SUM(CS.TO_BE_PAID_AMT + CS.ADVANC_AMT + CS.COIN_AMT + CS.COPAY_AMT + CS.DEDUCT_AMT + CS.PRE_PAID_AMT + CS.REDUCED_BY_OTH_INS_AMT + CS.OTH_MEM_LIAB_AMT) AS TOTALPD FROM (SELECT DISTINCT RATE.FACILID FROM LBAKER.KNEE_GROUPS RATE) LBA ,CLAIM_DS C ,CLAIM_SER_DS CS ,CVRGE_DS COV ,FACIL_DIM FAC ,PERSON_DIM PERS ,PROD_DIM PROD ,PHY_DIM PHY ,CLAIM_ICD9_DX_DIM ICD9 ,CLAIM_ICD9_PROC_DIM PR WHERE C.CLAIM_NO = CS.CLAIM_NO AND C.REC_TP_CD = CS.REC_TP_CD AND CS.CLAIM_NO = ICD9.CLAIM_NO (+) AND CS.REC_TP_CD = ICD9.REC_TP_CD (+) AND CS.CVRGE_ID = COV.CVRGE_ID AND CS.FACIL_ID = LBA.FACILID AND LBA.FACILID = FAC.FACIL_ID AND CS.PERSON_NO = PERS.PERSON_NO AND |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-01 : 07:31:02
|
Best way to optimize it is stop using Oracle and convert it to SQL Server, so you can use ANSI-style JOINs.  SQL Team is a SQL Server site, you'll get a better response here:http://dbforums.com/It might also help to describe what the query does, instead of just asking a blind "optimize this please" (yes, PLEASE is a good word to include). |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-01 : 07:32:56
|
| Posting "table structure, sample data and the result you want" would be helpful to give solutionMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Sql Optimization : i need the following SQL to be optimized