Select query results compare

Other SQL Server Topics
1

Hi,

I have a query as part of a stored procedure and the results are correct. I do however need to add code to compare the results to the results of the same query when it last ran e.g. 2 days ago. I then need any new records that was not on the results from 2 days ago to appear on top (first) in the results.
Any help will be appreciated how to do this. Thank you

Query:

SELECT * 

FROM

(

SELECT rt.sPortfolio, CAST(rt.iRuleId AS VARCHAR(15)) AS iRuleId, sDescription, sComment, rt.sPortfolio AS BlankRow
FROM thinktank..ruletests AS rt
	INNER JOIN thinktank..rules AS r
	ON r.id = rt.iruleid
		AND dttest > getdate()-1
		AND iresult <> 4
		AND ichecktype = 3
		AND r.iCategory = 0
		AND sRuleSet <> 'NOTIFY'
	INNER JOIN thinktank..GroupPortfolios AS gp
	ON rt.sPortfolio = gp.sPortfolioId
		AND sGroupID = 'EC'
	LEFT OUTER JOIN thinktank..RuleUCs AS ruc
	ON r.id = ruc.iruleid 
		AND ruc.icategory = '636'
	WHERE ISNULL(ruc.sValue,'N') <> 'Y'

UNION

SELECT '', '','','', rt.sPortfolio AS BlankRow
FROM thinktank..ruletests AS rt
	INNER JOIN thinktank..rules AS r
	ON r.id = rt.iruleid
		AND dttest > getdate()-1
		AND iresult <> 4
		AND ichecktype = 3
		AND r.iCategory = 0
		AND sRuleSet <> 'NOTIFY'
	INNER JOIN thinktank..GroupPortfolios AS gp
	ON rt.sPortfolio = gp.sPortfolioId
		AND sGroupID = 'EC'
	LEFT OUTER JOIN thinktank..RuleUCs AS ruc
	ON r.id = ruc.iruleid 
		AND ruc.icategory = '636'
	WHERE ISNULL(ruc.sValue,'N') <> 'Y'
GROUP BY rt.sPortfolio

) AS tbl

ORDER BY BlankRow ASC, sPortfolio DESC, iRuleId ASC
2

this gives the list of new records ... ONLY

you can mark them and do an ORDER BY

image

image
image728×110 4.12 KB

3

Like @harishgg1 said, except works really well. The only probably is figuring out WHAT is different is a pain. If you have a key value use a full outer join:

select coalesce(tableA.key, tableB.key) as Key, tableA.col1 as col1A, tableA.col2
from tableA
full outer join tableb
on tableA.key = tableB.key

now you can compare the data. In the where clause. So if you want rows where col1 is the same:
WHERE tableA.col1 <> tableA.col2 (In your case, each of your queries become a CTE/derived table)