December 13, 2006 at 2:02 pm
Hi, can somebody help on this
query 1
select count(PolicyNumber)
from GE_ReservesFile
where Recaptured = 'Recaptured'
i get count equal to 123
query 2
select count(PolicyNumber)
from #temp
where Recaptured = 'Recaptured'
i get count equal to 143
Actually the count from both the tables should be the same.
How can i write a query to find which records exists in both tables
and which records exists in one table and does not exists in the other table
December 13, 2006 at 2:10 pm
SELECT PolicyNumber FROM #temp tmp LEFT OUTER JOIN dbo.GE_ReservesFile GE ON tmp.PolicyNumber = GE.PolicyNumber WHERE GE.PolicyNumber IS NULL
December 13, 2006 at 2:15 pm
select case when a.key is not null and b.key is not null
then 'both'
case a.key is not null
then 'ReservesFile'
case b.key is not null
then 'Temp'
else -- not sure how this would happen
'Neither'
end ,
a.*, b.*
from (Select * from GE_ReservesFile
where Recaptured = 'Recaptured') a
full outer join
(select *
from #temp
where Recaptured = 'Recaptured'
) b
on a.key = b.key
Russel Loski, MCSE Business Intelligence, Data Platform
December 13, 2006 at 2:19 pm
Is there a unique (no duplicate) column common to both tables? Is PolicyNumber Unique?
Try this first on both tables..
Select COUNT(distinct PolicyNumber) from ...
Compare the results. If You still get 123 and 143, then use this query to get the rows in #temp which are NOT IN GE_ReservesFile.
Select T.PolicyNumber from GE_ReservesFile G
right join #temp T on G.PolicyNumber = T.PolicyNumber
where G.PolicyNumber IS NULL
--OR This
Select PolicyNumber from #temp where PolicyNumber not IN (Select PolicyNumber from GE_ReservesFile)
December 13, 2006 at 2:25 pm
i get the message 0 rows effected when i run this query
Select T.PolicyNumber from GE_ReservesFile G
right join #temp T on G.PolicyNumber = T.PolicyNumber
where G.PolicyNumber IS NULL
--OR This
Select PolicyNumber from #temp where PolicyNumber not IN (Select PolicyNumber from GE_ReservesFile)
December 13, 2006 at 2:30 pm
Have you tried inversing the tables?
Does this return any rows?
SELECT PolicyNumber, COUNT(*) AS Total FROM #temp GROUP BY PolicyNumber HAVING COUNT(*) > 1
December 13, 2006 at 2:35 pm
yes it does give me counts equal to 141
December 13, 2006 at 2:38 pm
when i run like this it also give me the same records
SELECT PolicyNumber, COUNT(*) AS Total FROM GE_ReservesFile GROUP BY PolicyNumber HAVING COUNT(*) > 1
December 13, 2006 at 3:22 pm
To find out which PolicyNumbers are duplicated,
Select T.PolicyNumber, TempCount, GECount from
(Select PolicyNumber, COUNT(PolicyNumber) as TempCount
from #temp group by PolicyNumber ) T
join
(Select PolicyNumber, COUNT(PolicyNumber) as GECount
from GE_ReservesFile group by PolicyNumber) G
on T.PolicyNumber = G.PolicyNumber
December 14, 2006 at 8:54 am
Are both tables supposed to contain the same data? How do yoo create and populate the temp table? Can you post that code?
Anyway, here's another variation:
select G.PolicyNumber AS PolicyNumInBoth
from GE_ReservesFile G
join #temp T
on G.PolicyNumber = T.PolicyNumber
where G.Recaptured = 'Recaptured'
select G.PolicyNumber AS PolicyNumNotInTemp
from GE_ReservesFile G
left join #temp T
on G.PolicyNumber = T.PolicyNumber
where G.Recaptured = 'Recaptured'
and T.PolicyNumber IS NULL
select T.PolicyNumber AS PolicyNumNotInGE
from #temp T
left join GE_ReservesFile G
on T.PolicyNumber = G.PolicyNumber
where T.Recaptured = 'Recaptured'
and G.PolicyNumber IS NULL
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply