Help(query) need in this Logic.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help(query) need in this Logic.

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-03-20 : 10:24:37

I have two tables X,Y
X
empno....Sal.....Tax.....Returns...name
1.....4500....1050.... 750.......robert
2.....5750.....1560....900.......john
3.....4000.....900.....600.......keen
4.....6100....1200.....1000......stauton

Y
empno....Sal.....Tax.....Returns...name
1.....4500....1000.... 000.......robert
2.....5750.....1200....900.......john
3.....4000.....900.....600.......keen
4.....6100....1000.....1000.......stauton

If you see the above tables I have data mismatch in X and Y tables for the same empno.
I need to write a query which shows emp no and columns(name of col) where the data mismatch has occured.
I came up with a query which I have to write for every individual column to get the mismatch.
Since there 120 columns it is pretty hard task..i m looking for a logic where I can write a query which shows mismatched data in columns.

Expected Output
table z
col1..col2
1......tax
3......tax
Appreciate your help.

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-03-20 : 11:28:25

I came up with some sort of solution. I need it to be verified.

drop table #tmp1
declare @col varchar(50)
Declare @sql char(8000)
create table #tmp1 (col_name varchar(50))
insert into #tmp1
select column_name from information_schema.columns
where table_name = 'X'

DECLARE Bank CURSOR FOR
SELECT col_name
FROM #tmp1

OPEN Bank
FETCH Bank INTO @col

-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN

FETCH bank INTO @col
END

Select
@SQL = 'if exists(Select @col from X, Y
where x.@col != y.@col)
Select @col from X, Y
where x.@col != y.@col

else
Select @col into #tmp2'
Exec (@SQL)
CLOSE bank
DEALLOCATE bank
RETURN

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-20 : 15:39:19

declare @X table (empno int, Sal int, Tax int, Returns int, name varchar(100) ) --NOTE Bad idea to use reserved words as column names
declare @Y table (empno int, Sal int, Tax int, Returns int, name varchar(100) ) --NOTE Bad idea to use reserved words as column names

insert into @X
select 1, 4500, 1050, 750, 'robert' union all
select 2, 5750, 1560, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1200, 1000, 'stauton'

insert into @Y
select 1, 4500, 1000, 000, 'robert' union all
select 2, 5750, 1200, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1000, 1000, 'stautons'

select * from @X
select * from @Y

select
x.empno,
replace(
(case when x.sal<>y.sal then 'SAL' else '-' end + ' ' +
case when x.tax<>y.tax then 'SAL' else '-' end + ' ' +
case when x.[returns]<>y.[returns] then 'returns' else '-' end + ' ' +
case when x.[name]<>y.[name] then 'name' else '-' end) , '-', '')
from @x x join @y y on x.empno = y.empno

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-20 : 15:41:06

And try this to only return rows that are changed
declare @X table (empno int, Sal int, Tax int, Returns int, name varchar(100) ) --NOTE Bad idea to use reserved words as column names
declare @Y table (empno int, Sal int, Tax int, Returns int, name varchar(100) ) --NOTE Bad idea to use reserved words as column names

insert into @X
select 1, 4500, 1050, 750, 'robert' union all
select 2, 5750, 1560, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1200, 1000, 'stauton'

insert into @Y
select 1, 4500, 1000, 000, 'robert' union all
select 2, 5750, 1200, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1000, 1000, 'stautons'

-- select * from @X
-- select * from @Y
select
x.empno,
replace(
(case when x.sal<>y.sal then 'SAL' else '-' end + ' ' +
case when x.tax<>y.tax then 'SAL' else '-' end + ' ' +
case when x.[returns]<>y.[returns] then 'returns' else '-' end + ' ' +
case when x.[name]<>y.[name] then 'name' else '-' end) , '-', '')
from @x x join @y y on x.empno = y.empno
where replace(
(case when x.sal<>y.sal then 'SAL' else '-' end + ' ' +
case when x.tax<>y.tax then 'SAL' else '-' end + ' ' +
case when x.[returns]<>y.[returns] then 'returns' else '-' end + ' ' +
case when x.[name]<>y.[name] then 'name' else '-' end) , '-', '')
<> ''

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-21 : 03:31:58

If you just want to compare where the data is not mtaching for a particular employee you can try this

declare @X table (empno int, Sal int, Tax int, Returns int, name varchar(100) )
declare @Y table (empno int, Sal int, Tax int, Returns int, name varchar(100) )

insert into @X
select 1, 4500, 1050, 750, 'robert' union all
select 2, 5750, 1560, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1200, 1000, 'stauton'

insert into @Y
select 1, 4500, 1000, 000, 'robert' union all
select 2, 5750, 1200, 900, 'john' union all
select 3, 4000, 900, 600, 'keen' union all
select 4, 6100, 1000, 1000, 'stautons'

select empno,sal,tax,returns from
(select * from @X
union all
select * from @Y)
t group by empno,sal,tax,returns having count(sal)=1 or count(tax)=1 or count(returns)=1

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-03-21 : 09:43:41

Thanks, it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources