Reputation: 6321
We have an issue with two tables, let's call them Item and ItemStatuses. We track each change to the status, so there is a start date and end date in the ItemStatuses table. We track the current status by looking for the one with an end date that is null.
Through an error in the system the newest status was added multiple times to a number of items. I need to select all but the first status for each item. I have the following query which gives me all the open statuses. I was trying this route because I figured I could use the row number to skip the first one, but there are multiple Items in these sets, so I need to skip the first status for each item. I think I'm pretty close with my query, but I'm not sure what I need to do.
SELECT ID, rn = ROW_NUMBER() OVER (ORDER BY ItemID)
FROM ItemStatuses WHERE ID IN
(
SELECT
s.ID
FROM Items as i
INNER JOIN ItemStatuses AS s ON
i.ID = s.ItemID AND
s.EndDate IS NULL
GROUP BY i.ID
HAVING COUNT(i.ID) > 1
)
Upvotes: 2
Views: 243
Reputation: 9302
To illustrate how to update all but the first status of your table:
declare @itemstatuses table (id int, Enddate datetime, theStatus int)
insert into @itemstatuses
values (1,getdate()-3,1),(1,getdate()-2,2),(1,getdate()-1,3),
(2,getdate()-3,2),(2,getdate()-2,2),(2,getdate()-1,99),
(3,getdate(),1)
select 'before',* from @itemStatuses
;with sorted
as (
select [r] = row_number()over(partition by id order by Enddate), *
from @ItemStatuses
)
update sorted
set theStatus = 100
where r>1
select 'after',* from @itemStatuses
Upvotes: 2
Reputation: 4693
I would simplify your SQL
query since this can become overly complicated and expensive.
Then use your server sided language to perform any filtering or condition.
Upvotes: 0