Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Correct way of finding what data is different


I have a stored proc that runs every night. My manager says data is sometimes different even tho for some rows it has to be the same.
So i put a debug step and been logging the data every day. I want to see what rows are different from today to next day (i have the dates)


I've logged data for 17 days. From Sept 30 to Oct 17th. This is what I do to see what's different but I get no rows back. I keep plugging in
da X and day X+1 is this a correct SQL?
select * from dbo.OrderAnalysisDebug 
where updatedate = '2011-10-06 06:00:20.010'
       and not exists (select * from dbo.OrderAnalysisDebug where  updatedate = '2011-10-07 06:00:10.020')

Open in new window

Avatar of twol
twol
Flag of United States of America image

What key are you match ing on?

For instance, let's say you have a key ItemNbr.
You could then say
select b.itemNbr from dbo.OrderAnalysisDebug New
left join dbo.OrderAnalysisDebug Old on new.ItemNbr = Old.ItemNbr and a.updatedate < then b.updatedate
where a.updatedate = '2011-10-07 06:00:10.020'
and b.itemNbr is null

to show new b.itemnbrs. an inner join fill find matching keys that have new update dates in the log.
Sorry but I couldn't get your query. Can you explain it more?
Here is a great pictorial about joins:

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

In the above code, I am using the same table twice and joining them together.
Avatar of Camillia

ASKER

ok, i tried that but this is what i want...

the table has 10 columns. I log data for 17 days. I want to see if any of those 10 columns are different. For example:

orderNumber   col2   col3   col4  updatedate
123                  ab      11     wxy    2011-10-01
123                 xx       11     wxy    2011-10-02
123                  ab      11     bbb  2011-10-03

That's why I used "not exists". But not sure if that's correct. So, it could be any column that could be different from today to tomorrow.

ASKER CERTIFIED SOLUTION
Avatar of twol
twol
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks, let me try
You have "or and" here...is it or OR and? :)

(
(case when new.col2 = old.col2 then 'N' else 'Y' end) = 'Y'
or and (case when new.col3 = old.col3 then 'N' else 'Y' end) = 'Y'
)
sorry...it is or

What the where clause is saying is that if either of the columns changes, you want to see the record.