January 6, 2016 at 12:52 pm
I have two tables Coverage and Rate as given below. There is some problem with the code that is populating the data incorrectly. I have to prepare a query that pulls the invalid records to fix the data. Please assist.
Correct Scenario 1 : A1 exists in both tables and data is same.
Correct Scenario 2 : D4 exists in coverage table but need not be in rate table
Incorrect Scenario 1 : C3 having mismatch in rate id for its second row. The mismatch can be vice versa as well.
Incorrect Scenario 2 : E5 having mismatch in rate id for its second row. The mismatch can be vice versa as well.
Coverage Table
CoverageID RateId
**************************
A1 1
A1 2
-----------------------------------
B2 1
-----------------------------------
C3 1
C3 2
-----------------------------------
D4 1
-----------------------------------
E5 1
E5 NULL
-----------------------------------
Rate Table
CoverageID RateId
**************************
A1 1
A1 2
-----------------------------------
B2 1
-----------------------------------
C3 1
C3 3
-----------------------------------
E5 1
E5 2
-----------------------------------
January 6, 2016 at 1:05 pm
welcome aboardSSC!
i converted your post into an easily consumable format...now anyone can copy/paste/test.
the intersect and except operators are an easy way to compar esomething like this, if you are expecting exact matches
CREATE TABLE #CoverageTable( CoverageID varchar(30),RateId int)
INSERT INTO #CoverageTable
SELECT 'A1','1' UNION ALL
SELECT 'A1','2' UNION ALL
SELECT 'B2','1' UNION ALL
SELECT 'C3','1' UNION ALL
SELECT 'C3','2' UNION ALL
SELECT 'D4','1' UNION ALL
SELECT 'E5','1' UNION ALL
SELECT 'E5',NULL
CREATE TABLE #RateTable( CoverageID varchar(30),RateId int)
INSERT INTO #RateTable
SELECT 'A1','1' UNION ALL
SELECT 'A1','2' UNION ALL
SELECT 'B2','1' UNION ALL
SELECT 'C3','1' UNION ALL
SELECT 'C3','3' UNION ALL
SELECT 'E5','1' UNION ALL
SELECT 'E5','2'
SELECT * FROM #CoverageTable
EXCEPT
SELECT * FROM #RateTable
SELECT * FROM #RateTable
EXCEPT
SELECT * FROM #CoverageTable
Lowell
January 6, 2016 at 1:37 pm
Thank you Lowell. Your suggestion works but it isn't covering one case which I didn't explain earlier in my original post. I have written the complete create/insert for you to use.
The EXCEPT SQL statment pulls all mismatch records but I do not want "D4" because it doesnt exists in #Rate table. I want to pull mismatch records only when a record exists in the #rate table.
Please assist.
/*
Drop Table #Coverage;
Drop Table #rate;
*/
Create Table #Coverage(coverageid varchar(2),rateid int);
create table #rate(coverageid varchar(2),rateid int);
Insert into #Coverage Values('A1',1);
Insert into #Coverage Values('A1',2);
Insert into #Coverage Values('B2',1);
Insert into #Coverage Values('C3',1);
Insert into #Coverage Values('C3',2); /*My query SHOULD pull this as it has a mismatch in the rate table*/
Insert into #Coverage Values('D4',1); /*My query SHOULD NOT pull this as it doesn't exist rate table*/
Insert into #Coverage Values('E5',1);
Insert into #Coverage Values('E5',Null); /*My query SHOULD pull this as it has a mismatch in the rate table*/
Insert into #Coverage Values('F6',1);
Insert into #Coverage Values('F6',2); /*My query SHOULD pull this as it has a mismatch in the rate table*/
Insert into #rate Values ('A1',1);
Insert into #rate Values ('A1',2);
Insert into #rate Values ('B2',1);
Insert into #rate Values ('C3',1);
Insert into #rate Values ('C3',3);
Insert into #rate Values ('E5',1);
Insert into #rate Values ('E5',2);
Insert into #rate Values ('F6',1);
Insert into #rate Values ('F6',Null);
select * from #coverage
EXCEPT
select * from #rate;
January 6, 2016 at 1:45 pm
You just need to slightly modify the query Lowell provided.
SELECT * FROM #CoverageTable
WHERE CoverageID IN ( SELECT CoverageID FROM #RateTable )
EXCEPT
SELECT * FROM #RateTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 6, 2016 at 1:50 pm
Great thank you.:-)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply