SQL Query Design for data validation issue

Question

I have a fact table that contains some finance data. There is a column (VERS_NM) that defines wherther the value is "Actual" or "Current Outlook". The value for these two should always be the same but we noticed in some reports it seems incorrect. So I want to write a query to find where the actual value does not match the current outlook.

I cannot wrap my head around a way to do this.

Here is what the table looks like: table

So to recap there will be an identical row to row 1 except the VERS_NM column will say "Actual". At least it is supposed to be, I want to find any instances where the Actual and Current Outlook don't match. Any help or ideas is much appreciated. Just a push in the right direction or some kind of plan to tackle this would be great.

Thanks!

score 4 · Accepted Answer · answered Aug 02 '12 at 15:01

4

You could just self join the data, replacing the fields a, b, c, d with the fields that indicate that the rows are equivilent.

SELECT
  *
FROM
  yourTable    AS actual
INNER JOIN
  yourTable    AS outlook
    ON  actual.a = outlook.a
    AND actual.b = outlook.b
    AND actual.c = outlook.c
    AND actual.d = outlook.d
WHERE
      actual.VERS_NM  = 'Actual'
  AND outlook.VERS_NM = 'Current Outlook'
  AND actual.FINC_ACCT_METRIC_VAL <> outlook.FINC_ACCT_METRIC_VAL

answered Aug 02 '12 at 15:01

MatBailie

70,516
16
91
126

This looks promising. I'll let you know how it works out. Thanks! – tarheels058 Aug 02 '12 at 15:13
Worked like a charm. Thank you again for your input! – tarheels058 Aug 02 '12 at 16:28

score 1 · Answer 2 · answered Aug 02 '12 at 15:15

An alternative method is to use UNION. It's more of a way to detect differences in many table fields but can work in your situation as well.

The technique is described in the article The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION! to compare two separate tables but you can analyse a single table.

Replace the fields COL1, COL2 etc. with the columns you want to compare. For your comparison I've added a WHERE clause to the inner SELECT to effectively view your data as two separate tables.

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
  SELECT 'Actual' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
  FROM Finance_Data A
  WHERE VERS_NM = 'Actual'
  UNION ALL
  SELECT 'Outlook' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
  FROM Finance_Data B
  WHERE VERS_NM = 'Current Outlook'
) T
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

score 0 · Answer 3 · answered Aug 02 '12 at 14:59

0

You could group by all other columns, and then use a having clause to demand that each group has an "Actual" and a "Current outlook" variant:

select  *
from    YourTable
group by
        col1
,       col2
,       ... all other columns ...
having  sum(case when VERS_NM = 'Actual' then 1 end) <> 1
        or sum(case when VERS_NM = 'Current Outlook' then 1 end) <> 1
        or count(*) <> 2

answered Aug 02 '12 at 14:59

Andomar

216,619
41
352
379

The OP wants results where two matching rows exist (one actual, one outlook), but with different values. – MatBailie Aug 02 '12 at 15:04

SQL Query Design for data validation issue

3 Answers3