Question Regarding Temp Tables

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question Regarding Temp Tables

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-05-02 : 08:49:57

I created a temp table and everything was working perfectly. Then I decided to do a Summary query. My claim count was correct, but my number of total charges was not. So instead I created an actual table and created a query and my numbers worked. Why would my second summary work and not the first one?

#1 Here is my summary I was using for my temp table.

Select Distinct t1.keyID, count(distinct t1.clm_id1)as clmcount,
SUM( (CAST(t1.clm_tchg AS MONEY))) AS TotalBILLEDCharges
from #EOTFTable t1
join #PrimProvSpecOtheTable t2 on t1.keyID = t2.keyID
group by t1.keyID

#2 Then here is my summary from my view

Select Distinct keyID, count(distinct clm_id1)as clmcount,
SUM( (CAST(clm_tchg AS MONEY))) AS TotalBILLEDCharges
from vw_TEST_EOTF
group by keyID

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-02 : 09:39:00

I suspect this is because the data from the view is not the same as the data that you get when you join the tables. You can test it by doing the following.

1. Check if the number of records returned are the same:

SELECT COUNT(*) 
FROM #EOTFTable t1 
	JOIN #PrimProvSpecOtheTable t2 ON  t1.keyID = t2.keyID;
	
SELECT COUNT(*) FROM  vw_TEST_EOTF;

2. If they are (or even if they are not), check the differences:

------------------------------------------------------------
SELECT t1.keyID,t1.clm_id1,t1.clm_tchg
FROM #EOTFTable t1 
	JOIN #PrimProvSpecOtheTable t2 ON  t1.keyID = t2.keyID

EXCEPT

SELECT keyID,clm_id1,clm_tchg FROM vw_TEST_EOTF;
-----------------------------------------------------------
SELECT keyID,clm_id1,clm_tchg FROM vw_TEST_EOTF

EXCEPT

SELECT t1.keyID,t1.clm_id1,t1.clm_tchg
FROM #EOTFTable t1 
	JOIN #PrimProvSpecOtheTable t2 ON  t1.keyID = t2.keyID
------------------------------------------------------------

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-02 : 11:23:04

What is the statement for the View? Is it doing a SELECT DISTINCT before the query that consumes the view also applies the distinct specifier?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-05-02 : 12:11:12

Thanks so much!

quote:
Originally posted by Lamprey

What is the statement for the View? Is it doing a SELECT DISTINCT before the query that consumes the view also applies the distinct specifier?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources