SQL- Trying to subtract date values from the same column

1

0down votefavorite

I need a subtraction of a max date to the last previous status date and cannot figure it out. I will be using FindingID and UpdatedEstimatedRemediationDate.

For example:

FindingID 'FND-5645' has been updated 3 times:
UpdatedEstimatedRemediationDate

NULL
2015-06-15
2015-12-30
2016-06-30
I need to get the days difference from June 30, 2016 from December 12,2015. I am using SQL Server 2008 R2. Thanks in advance.

select
findingid,
datediff(day, min(UpdatedEstimatedRemediationDate),
max(UpdatedEstimatedRemediationDate))
from flod..FirstDef_ArcherRemPlansHistoric
where FindingID= 'FND-5645'
group by findingid;

but it only gives me the MIN and MAX, not the two most recent days.

2

This works:

with cte(dt) as (
select cast('2015-06-15' as DATE) union all
select cast('2015-12-30' as DATE) union all
select cast('2016-06-30' as DATE)
)

select DATEDIFF(day, min(dt), max(dt))
from cte

So maybe there is only one date per finding id (your group by column)

3

Here is the beginning of what I am thinking of. I hope it gives you an idea WITH cte AS ( SELECT FindingID, UpdatedEstimatedRemediationDate, ROW_NUMBER() OVER(PARTITION BY FindingID ORDER BY UpdatedEstimatedRemediationDate DESC) AS RN FROM flod..FirstDef_ArcherRemPlansHistoric ) , cte2 AS ( SELECT FindingID, UpdatedEstimatedRemediationDate AS UpdatedEstimatedRemediationDate2 FROM cte WHERE RN = 2 ) SELECT H.FindingID, DATEDIFF(DAY, cte2.UpdatedEstimatedRemediationDate2, MAX(UpdatedEstimatedRemediationDate)) FROM flod..FirstDef_ArcherRemPlansHistoric H INNER JOIN cte2 ON H.FindingID = cte2.FindingID GROUP BY H.FindingID

4 
declare @FindingID varchar(30)
set @FindingID = 'FND-5645'

select FindingID, 
    MAX(UpdatedEstimatedRemediationDate) AS LastEstimatedRemediationDate,
    MIN(UpdatedEstimatedRemediationDate) AS NextToLastEstimatedRemediationDate,
    DATEDIFF(DAY, MIN(UpdatedEstimatedRemediationDate), MAX(UpdatedEstimatedRemediationDate)) AS DaysDiff
from (
    select 
        FindingID, UpdatedEstimatedRemediationDate,
        ROW_NUMBER() OVER(PARTITION BY FindingID 
            ORDER BY UpdatedEstimatedRemediationDate DESC) AS row_num
    from flod..FirstDef_ArcherRemPlansHistoric
    where FindingID = @FindingID
) as derived
where row_num between 1 and 2
group by FindingID
--uncomment if you want only FindingIDs that have at least two Estimated dates
--having MAX(UpdatedEstimatedRemediationDate) <> MIN(UpdatedEstimatedRemediationDate)