Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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.
0
JonMny
Asked:
JonMny
1 Solution
 
fhlio_adminCommented:
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?
0
 
JonMnyAuthor Commented:
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.
0
 
fhlio_adminCommented:
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

0
 
Bhavesh ShahLead AnalysistCommented:
hi,

check out this link.

It will help you to find duplicate....

http://msdn.microsoft.com/en-us/library/ms188055(v=sql.105).aspx
0
 
Scott PletcherSenior DBACommented:
SELECT *
FROM [Country_audit]
WHERE iid IN (
    SELECT
        iid
    FROM [Country_audit]
    WHERE
        change_dte >= '20110401' AND
        change_dte < '20110701'
    GROUP BY
        iid
    HAVING
        MIN(rating) <> MAX(rating)
) AS derived
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now