CODEWITHSUNDEEP
sqldatetimeselectsql-server-2012
Steve Waters
Steve Waters

Reputation: 3548

SQL: Select the latest datetime value when duplicates found on another column

I'm using SQL Server 2012.

I have a table called service_reports. The table has four columns:

  1. id column
  2. Foreign key column fk_service referring to a service that the report is about
  3. a datetime column done_date which represents the point in time when the report was finalized.
  4. Lastly, it has a boolean (BIT) column 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

Answers (2)

Sheldon Neilson
Sheldon Neilson

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

JBrooks
JBrooks

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

Related Questions