Camillia
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')
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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, let me try
ASKER
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'
)
(
(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.
What the where clause is saying is that if either of the columns changes, you want to see the record.
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.