• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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

0
Camillia
Asked:
Camillia
  • 4
  • 3
1 Solution
 
twolCommented:
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.
0
 
TempDBACommented:
Sorry but I couldn't get your query. Can you explain it more?
0
 
twolCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CamilliaAuthor Commented:
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.

0
 
twolCommented:
I see.
ok, so between any two days, you can do this:


select New.itemNbr
,case when new.col2 = old.col2 then 'N' else 'Y' end)  as Col2Changed
,case when new.col2 = old.col2 then 'N' else 'Y' end)  as Col3Changed
 from dbo.OrderAnalysisDebug New
inner join dbo.OrderAnalysisDebug Old on new.ItemNbr = Old.ItemNbr
where New.updatedate = '2011-10-07 06:00:10.020'
and Old.updatedate = '2011-10-07 06:00:10.020'
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'
)
0
 
CamilliaAuthor Commented:
thanks, let me try
0
 
CamilliaAuthor Commented:
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'
)
0
 
twolCommented:
sorry...it is or

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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