[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query possible Problems

Posted on 2005-05-02
5
Medium Priority
?
264 Views
Last Modified: 2010-03-19
Hi guys,

I  have a potential problem with the following query, and I am not sure whether I am going to get correct results.  I would deeply appreciate it if one of you SQL gurus could take a quick look at the code and see if there are any major errors in the logic:

These are  a bunch of temp tables that I bring together at the end.... My major concern is that the left outer joins I am conducting in #FIN_TOTAL are incorrect (I did them to ensure that I didnt lose any rows).  The table SAF02_D is currently unindexed and contains about 27,000,000 rows.  The last query section takes a reeeeaaaallly long time, and I am afraid that the its not going to give me accurate results.................

DROP TABLE #SAF_VOL
SELECT REV_CNTR_HCPCS_CD AS CPT, COUNT(*) AS VOL
INTO #SAF_VOL
FROM SAF02_D A
GROUP BY REV_CNTR_HCPCS_CD

DROP TABLE #SAF_REV
SELECT REV_CNTR_HCPCS_CD AS CPT, AVG(CAST(REV_CNTR_TOT_CHRG_AMT AS FLOAT)) AS REV_TOT_CHG,
       AVG(CAST(REV_CNTR_PMT_AMT AS FLOAT)) AS AVG_REV_PMT,
       AVG(CAST(CLM_PMT_AMT AS FLOAT)) AS AVG_CLM_PMT,
       AVG(CAST(CLM_TOT_CHRG_AMT AS FLOAT)) AS CLM_TOT_CHG,
       AVG(CAST(NCH_BENE_PTB_COINSRNC_AMT AS FLOAT)) AS PTB_COIN_AMT
INTO #SAF_REV
FROM SAF02_D A, SAF02_B B
WHERE A.RECID = B.RECID
AND CAST(REV_CNTR_PMT_AMT AS FLOAT) > 0
GROUP BY REV_CNTR_HCPCS_CD      

DROP TABLE #RESULTS
SELECT A.CPT, B.SHORT_DESC, B.MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT, AVG_CLM_PMT,
       CLM_TOT_CHG, PTB_COIN_AMT
INTO #RESULTS
FROM #SAF_VOL A, YAEL_TEMP..CPT B, #SAF_REV C
WHERE A.CPT *= B.CPT
AND A.CPT *= C.CPT
ORDER BY A.CPT

UPDATE #RESULTS
SET SHORT_DESC = (SELECT DESCRIPTION FROM YAEL_TEMP..HCPCS_02_NEW A WHERE #RESULTS.CPT = A.CPT)
WHERE SHORT_DESC IS NULL  

DROP TABLE #T1
SELECT RECID, SUM(CAST(REV_CNTR_PMT_AMT AS FLOAT)) TOT_PMT
INTO #T1
FROM SAF02_D
GROUP BY RECID
ORDER BY RECID

DROP TABLE #FIN_TOTAL
SELECT CPT, SHORT_DESC, MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT, AVG(TOT_PMT) AVG_TOT_PMT,
       AVG_CLM_PMT, CLM_TOT_CHG, PTB_COIN_AMT
FROM #RESULTS A, #T1 B, SAF02_D C
WHERE A.CPT *= C.REV_CNTR_HCPCS_CD
AND B.RECID *= C.RECID
AND TOT_PMT > 0
GROUP BY CPT, SHORT_DESC, MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT,
         AVG_CLM_PMT, CLM_TOT_CHG, PTB_COIN_AMT
ORDER BY CPT
0
Comment
Question by:sviriyal
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Julianva
ID: 13915694
I dont see any left outer join in your query
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 13916863
can you check your logic in the 3rd query. It doens't look right, but I can't say for sure, since I don't know what you want to achieve!.
you have 2 outer joins FROM 2 different tables, to the one table on the right.
The net effect here is that you will get a resultset which has a caretesian product between A & B. If there are 1000 records in A and 1000 records in B you will get 1,000,000 records in your result set. Then the aggregation (AVG) will reduct this again. But this is probably not what you want. Most likely you either need to specify an exctra link between A & B, or else you may want to switch your outer join around, so that it is C left outer join A, B not A left outer join C & B left outer join C.

Without knowing what you want to achieve, and what your tables look like etc, it is hard to say what needs to change, but almost suredly this is not what you do want.
Also, it is recommended that you don't use the *= syntax, but rather the SQL-92 standard  method of doing outer joins, e.g.

SELECT A.CPT, B.SHORT_DESC, B.MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT, AVG_CLM_PMT,
       CLM_TOT_CHG, PTB_COIN_AMT
INTO #RESULTS
FROM #SAF_VOL A
LEFT OUTER JOIN YAEL_TEMP..CPT B ON A.CPT = B.CPT
LEFT OUTER JOIN #SAF_REV C ON  A.CPT = C.CPT
ORDER BY A.CPT
0
 

Author Comment

by:sviriyal
ID: 13917879
Thanks for the input BillAn1!  The only thing is, in the 3rd query, I am not seeing 2 outer joins.  Isnt the *= syntax with Table A on the left showing that the query is doing 2 left outer joins with Table A at the left?  The reason I have to do this is because not all CPT codes in Table A exist in Table B or Table C.  I want all CPT codes from Table A to show up with NULL values where this CPT code wasnt found either in B or C.  And this seems to work too.  The table #RESULTS has exactly the same number of rows as #SAF_VOL.

 I will definitely keep in mind to use SQL-92 syntax for outer joins from now on.  And I think the answer you just gave me actually applies to the last query, because Table C, in this case, has left outer joins from 2 different tables with tbl C on the right.  My problem again though, is that I am losing CPT codes in the last query.  As you can see, I am joining 3 tables in this case.  What I am essentially trying to do is get the average of the summed up values from Table B for those RECIDs that occur in Table C under the given CPT code that exists in Table A.  When I do a simple join (a = c and b = c) in the last query, I lose CPT codes.  In Table A, each row is a unique CPT code.  

How do I go about keeping these CPT codes from Table A without creating cartesian joins?  Thanks so much for your help BillAn1!
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 2000 total points
ID: 13919588
sviriyal, as ytou say in your 2nd paragraph, the problem is I think in your last query,
 your first join is
A.CPT *= C.REV_CNTR_HCPCS_CD
AND B.RECID *= C.RECID which has 2 different tables on the left.

So, what  I think you want is to join B with C, and then do an outer join from A to this result set?
THe extra complication is that you only want to include rows from B where TOT_PMT > 0. If you put that clause in the WHERE clause, then you will 'miss' rows, since you will not get rows where A does not join to B. Instead, either you chenge the WHERE clause to be WHERE (TOT_PMT > 0 OR C.REV_CNTR_HCPCS_CD IS NULL) or else, you the condition in the join clause instead.

So, try this :  
SELECT CPT, SHORT_DESC, MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT, AVG(TOT_PMT) AVG_TOT_PMT,
       AVG_CLM_PMT, CLM_TOT_CHG, PTB_COIN_AMT
FROM #RESULTS A
LEFT OUTER JOIN  (#T1 B JOIN  SAF02_D C ON B.RECID = C.RECID AND TOT_PMT > 0)
ON A.CPT = C.REV_CNTR_HCPCS_CD
GROUP BY CPT, SHORT_DESC, MED_DESC, VOL, REV_TOT_CHG, AVG_REV_PMT,
         AVG_CLM_PMT, CLM_TOT_CHG, PTB_COIN_AMT
ORDER BY CPT
0
 

Author Comment

by:sviriyal
ID: 13963338
Sorry for the long delay in my reply.  Thank you very much for the help BillAn1.  I actually ended up ditching the idea of the outer join in this case because it didnt make much sense to throw out rows with the clause TOT_PMT > 0 and then bring them back in with an outer join.  

Thanks again for your help.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question