Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chris_Kelley
Posting Yak Master
114 Posts |
 Posted - 2014-10-27 : 11:03:48
|
| c |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-27 : 11:13:45
|
| Its a bit confusing to me. Does your changes table track the column name and old and new values for the column being changed? |
 |
|
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-10-27 : 11:17:42
|
| c |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-27 : 12:04:00
|
| OK -- so what's the name of the base table (the one the change table tracks) and how do you join the two tables together? |
|
|
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-10-27 : 12:28:38
|
| c |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-27 : 14:00:48
|
| so select * from changes where change_code = 27 and old_value = 'false' and new_value <> 'true' |
|
|
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-10-27 : 14:37:31
|
| c |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-27 : 16:10:00
|
| that's why I asked how to join on the main table. You want to find rows in the main table that do not have the same status as New_Value in the change table. That means you need to join the tables. |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-10-27 : 17:40:52
|
Not sure I understand the task correct, but try this:with cte as (select * ,row_number() over (partition by debt_key order by change_date) as rn from yourtable where change_code=27 )select * from cte as a inner join cte as b on b.debt_key=a.debt_key and b.rn=a.rn+1 and b.old_value=a.old_value where a.old_value='false' ps.: Please don't use nolock |
|
|
|
|
|
|
CTE help