October 13, 2011 at 10:31 am
First I would like you for taking the time to views this post.
I have been tasked to find changes made to a DB over time. I pull bi-weekly reports and I need to compare the two and report any changes.
For time sake I came up with a little example.
Say table old is a dumb of the DB in September and table New is a dumb of October.
Is there a way to compare both tables and get the “desired output” below? THANKS FOR YOU HELP!
Table A
IdNameversion disposition
1MS office2009approved
2 MS office 2011approved
3MS SQL2005approved
Table B
IdNameversion disposition
1MS office2009disapproved
4Adobe CS5CS5approved
3MS SQL2008approved
Desired output
Status IdName version disposition
Change 1 MS office 2009 Old: approved || new: disapproved
Delete 2 MS office 2011approved
Change 3 MS SQL Old: 2005 || New: 2008 approved
Add 4 Adobe CS5 CS5approved
October 13, 2011 at 10:43 am
you need to simply join the two tables on the id , i think;
CREATE TABLE #TableA (
id int,
name varchar(30),
version varchar(30),
disposition varchar(30) )
CREATE TABLE #TableB (
id int,
name varchar(30),
version varchar(30),
disposition varchar(30) )
INSERT INTO #TableA
SELECT '1','MS office','2009','approved' UNION ALL
SELECT '2','MS office','2011','approved' UNION ALL
SELECT '3','MS SQL','2005','approved'
INSERT INTO #TableB
SELECT '1','MS office','2009','disapproved' UNION ALL
SELECT '4','Adobe CS5','CS5','approved ' UNION ALL
SELECT '3','MS SQL','2008','approved '
SELECT * FROM #TableA
FULL OUTER JOIN #TableB
ON #TableA.id = #TableB.id
then the join:
SELECT * FROM #TableA
FULL OUTER JOIN #TableB
ON #TableA.id = #TableB.id
Lowell
October 13, 2011 at 11:13 am
I tried a few different queries.
This one has the format I need but does not address the Adds and deletes.
SELECT o. Id,
CASE WHEN o.Application=n.Application
THEN o.Application
ELSE 'Old: ' + o.Application + ' || New: ' + n.APPLICATION END
AS '*Application',
CASE WHEN o.Version=n.Version
THEN o.Version
ELSE 'Old: ' + o.Version + ' || New: ' + n.Version END
AS '*Version',
CASE WHEN o.[Disposition]=n.[Disposition]
THEN o.[Disposition]
ELSE 'Old: ' + o.[Disposition] + ' || New: ' + n.[Disposition] END
AS '*Disposition' ,
FROM apps_Old AS o INNER JOIN apps_New AS n ON o.DADMS_ID=n.DADMS_ID
WHERE o.DADMS_ID In (SELECT Old. ID FROM apps_old as Old, apps_new as New
WHERE (Old. ID = New. ID
AND ( Old.Version != New.Version OR
Old.Application <> New.Application OR
Old.[Disposition] <> New.[Disposition]))
order by [/code]
This one handles Adds, Deletes and changes but the output is horrible It has two rows for changes
table old11896AXUM
Tablenew11896AXUM
table old12080MATHSOFT MATHCAD
tablenew12345test
select Min(apps_new) as [Status],[ ID],[Application],[Version],[Disposition]
from
( select 'table new' as apps_new, a.[ ID], a.[Application],a.[Version], a.[Disposition]
from apps_new a
union all
select 'table old' as apps_old, b.[ ID], b.[Application]
, b.[Version], b.[Disposition]
from apps_old b)temp
group by [ID], [Application]
,[ Version],[Disposition]
having count (*) = 1
order by [ID]
October 13, 2011 at 11:13 am
I tried a few different queries.
This one has the format I need but does not address the Adds and deletes.
SELECT o. Id,
CASE WHEN o.Application=n.Application
THEN o.Application
ELSE 'Old: ' + o.Application + ' || New: ' + n.APPLICATION END
AS '*Application',
CASE WHEN o.Version=n.Version
THEN o.Version
ELSE 'Old: ' + o.Version + ' || New: ' + n.Version END
AS '*Version',
CASE WHEN o.[Disposition]=n.[Disposition]
THEN o.[Disposition]
ELSE 'Old: ' + o.[Disposition] + ' || New: ' + n.[Disposition] END
AS '*Disposition' ,
FROM apps_Old AS o INNER JOIN apps_New AS n ON o.DADMS_ID=n.DADMS_ID
WHERE o.DADMS_ID In (SELECT Old. ID FROM apps_old as Old, apps_new as New
WHERE (Old. ID = New. ID
AND ( Old.Version != New.Version OR
Old.Application <> New.Application OR
Old.[Disposition] <> New.[Disposition]))
order by [/code]
This one handles Adds, Deletes and changes but the output is horrible It has two rows for changes
table old11896AXUM
Tablenew11896AXUM
table old12080MATHSOFT MATHCAD
tablenew12345test
select Min(apps_new) as [Status],[ ID],[Application],[Version],[Disposition]
from
( select 'table new' as apps_new, a.[ ID], a.[Application],a.[Version], a.[Disposition]
from apps_new a
union all
select 'table old' as apps_old, b.[ ID], b.[Application]
, b.[Version], b.[Disposition]
from apps_old b)temp
group by [ID], [Application]
,[ Version],[Disposition]
having count (*) = 1
order by [ID]
October 13, 2011 at 11:19 am
the key is to full outer join the tables,a dn to then handle the nulls.
how does this work for you?
SELECT o. Id,
CASE
WHEN ISNULL(o.Application,'No Applcation!') = ISNULL(n.APPLICATION,'No Applcation!')
THEN o.Application
ELSE 'Old: ' + ISNULL(o.Application,'No Applcation!') + ' || New: ' + ISNULL(n.APPLICATION,'No Applcation!')
END AS '*Application',
CASE
WHEN ISNULL(o.Version,'No Version!') = ISNULL(n.Version,'No Version!')
THEN o.Version
ELSE 'Old: ' + ISNULL(o.Version,'No Version!') + ' || New: ' + ISNULL(n.Version,'No Version!')
END AS '*Version',
CASE
WHEN ISNULL(o.[Disposition],'No Disposition!') = ISNULL(n.[Disposition],'No Disposition!')
THEN o.[Disposition]
ELSE 'Old: ' + ISNULL(o.[Disposition],'No Disposition!') + ' || New: ' + ISNULL(n.[Disposition],'No Disposition!')
END AS '*Disposition'
FROM apps_Old AS o
FULL OUTER JOIN apps_New AS n
ON o.DADMS_ID = n.DADMS_ID
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply