asked on

SQL Query to verify changed records

I have this query that searches for all records that have a change in column 3

SELECT * FROM [Country_audit] where
iid IN
(
SELECT iid FROM [Country_audit] WHERE YEAR(change_dte)=2011 AND (change_cols & 4) =4 AND DATEPART(quarter, change_dte) =2
)

I have noticed that the audit trigger is inserting rows even when there is no actual change

so the result from this is

1 Honduras 3 1 1 U 0x1E 2011-05-18 10:45:43.533
1 Honduras 3 1 1 U 0x1E 2011-11-23 12:07:08.610

so what I need is a query that will return a country only when column 3 actually has a change in the period specified.

fhlio_admin

Can you please put the field names on your data? I am unclear as to what fields we are dealing with. You

Are you wanting only the last change being a country change?

E.g.
If a record changed from Honduras to US in a quarter and then back to Honduras all prior to the second quarter of 2011, but in second quarter of 2011, there is no change to the country, should it return?

Also, the records that you present, the second record is after the requested time. Does that matter?

JonMny

ASKER

the first three are (that's all I need)

iid,country,rating

yes that does matter...need to add the where.

I only care if the rating column changed , if it went from 1 to 2 then back to one I guess it would be ok to return that or not.

fhlio_admin

let's see if this works for you...

WITH 
  CHANGED_RECORDS (
  SELECT iid, rating, change_dte 
  FROM [Country_audit] 
  WHERE 
    YEAR(change_dte)=2011
    AND (change_cols & 4) = 4
    AND DATEPART(quarter, change_dte) = 2  
 ) , CHANGE_VAL (
  SELECT CR.iid
  FROM 
    CHANGED_RECORDS CR
    INNER JOIN 
      (
      SELECT TOP 1 SCA.iid, SCA.rating
      FROM [Country_audit] SCA 
      WHERE CR.iid = SCA.iid AND CR.change_dte > SCA.change_dte
      ORDER BY SCA.change_dte DESC
      ) CA ON CR.iid = CA.iid
  WHERE CR.rating <> CA.rating
 )

SELECT CA.*
FROM 
  [Country_audit] CA 
  INNER JOIN CHANGE_VAL CV ON CA.iid = CV.iid

Open in new window

Bhavesh Shah

hi,

check out this link.

It will help you to find duplicate....

http://msdn.microsoft.com/en-us/library/ms188055(v=sql.105).aspx

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial