SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question Regarding Temp Tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

werhardt
Constraint Violating Yak Guru

270 Posts
Posted - 05/02/2012 :  08:49:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 05/02/2012 :  09:39:00  Show Profile  Reply with Quote
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
------------------------------------------------------------
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3821 Posts
Posted - 05/02/2012 :  11:23:04  Show Profile  Reply with Quote
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?
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts
Posted - 05/02/2012 :  12:11:12  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000