September 7, 2011 at 2:07 pm
Hi,
This is a query to determine delta changes that a friend of mine wrote in excel. If i link my sql tables to excel it works but i need to run it straight from SQL. Can someone recreate this query in SQL? thanks in advance.
SELECT o.DADMS_ID,
IIf(o.Application=n.Application,o.Application,'Old: ' & o.Application & ' || New: ' & n.Application) AS Application,
IIf(o.Acronym=n.Acronym,o.Acronym,'Old: ' & o.Acronym & ' || New: ' & n.Acronym) AS Acronym,
IIf(o.Version=n.Version,o.Version,'Old: ' & o.Version & ' || New: ' & n.Version) AS Version
FROM Old AS o INNER JOIN New AS n ON o.DADMS_ID=n.DADMS_ID
WHERE o.DADMS_ID In (SELECT Old.DADMS_ID
FROM Old, New
WHERE ( Old.DADMS_ID = New.DADMS_ID
AND ( Old.Version <> New.Version OR
Old.Acronym <> New.Acronym OR
Old.Application <> New.Application )
)
);
September 7, 2011 at 2:24 pm
This should work for you.
SELECT o.DADMS_ID,
CASE WHEN o.Application=n.Application
THEN o.Application
ELSE 'Old: ' + o.Application + ' || New: ' + n.APPLICATION END
AS Application,
CASE WHEN o.Acronym=n.Acronym
THEN o.Acronym
ELSE 'Old: ' + o.Acronym + ' || New: ' + n.Acronym END
AS Acronym,
CASE WHEN o.Version=n.Version
THEN o.Version
ELSE 'Old: ' + o.Version + ' || New: ' + n.Version END
AS Version
FROM Old AS o INNER JOIN New AS n ON o.DADMS_ID=n.DADMS_ID
WHERE o.DADMS_ID In (SELECT Old.DADMS_ID
FROM Old, New
WHERE ( Old.DADMS_ID = New.DADMS_ID
AND ( Old.Version <> New.Version OR
Old.Acronym <> New.Acronym OR
Old.Application <> New.Application )
)
);
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2011 at 2:36 pm
THANKS A MILLION!! works perfect.
another question, what does <> mean?
September 7, 2011 at 2:39 pm
<> means "Not EQUAL"
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply