Reputation: 3548
I'm using SQL Server 2012.
I have a table called service_reports
. The table has four columns:
id
columnfk_service
referring to a service that the
report is aboutdone_date
which represents the point in time
when the report was finalized.isFinalReport
.Now, some services have multiple service reports about them and I want to select all but the newest report of each group that refers to a certain service. I want to set the isFinalReport
boolean value in all but the latest report referring to the same service, as false. Thereby leaving the latest service_report
as the final report.
Example: service_reports
rows(id
) 10 11 and 12 all refer to fk_service
25, so there are three rows in the service_reports
table that refer to that same fk_service
.
Now, let's say row 12 is the latest of these three reports. I want to set the isFinalReport
as false in service_reports
10 and 11.
I need to do this to all entries in the table, where more than one service_report
refers to same fk_service
Any help would be appreciated! Thank you.
Upvotes: 0
Views: 541
Reputation: 803
DECLARE @service_reports TABLE
(
id INT PRIMARY KEY,
fk_service INT,
done_date DATETIME,
isFinalReport BIT
)
INSERT INTO @service_reports
(
id
,fk_service
,done_date
,isFinalReport
)
VALUES
(
10
,25
,'2015-04-14'
,1
)
INSERT INTO @service_reports
(
id
,fk_service
,done_date
,isFinalReport
)
VALUES
(
11
,25
,'2015-04-15'
,1
)
INSERT INTO @service_reports
(
id
,fk_service
,done_date
,isFinalReport
)
VALUES
(
12
,25
,'2015-04-16'
,1
)
UPDATE sr
SET sr.isFinalReport = 0
FROM @service_reports sr
JOIN
(
SELECT id,
ROW_NUMBER() OVER (PARTITION BY fk_service ORDER BY done_date DESC) AS rowNumber
FROM @service_reports
) s
ON s.id = sr.id
WHERE s.rowNumber > 1
SELECT *
FROM @service_reports
Upvotes: 0
Reputation: 10013
I think I would actually do this in a trigger where when a new row is added it sets the isFinalReport to false for its older siblings.
But this is how I would answer your question:
update a
set a.isFinalReport = false
from service_reports a
where isnull(a.isFinalReport, 1) = 1
and exists
(select 1
from service_reports b
where b.fk_service = a.fk_service
and b.done_date > a.done_date)
Upvotes: 1