CODEWITHSUNDEEP
sqlsql-servert-sql
Stephen Lloyd
Stephen Lloyd

Reputation: 805

Query age of a current value in table with historical records

Assume a history table which contains a Status, RecordFromDate and RecordThruDate fields as well as other fields..

Given that a new record could be created for any change on any field, not just the Status field, how can one find the length of time the Status field has held it's current value?

RecordThruDate of the current record is represented by '9999-12-31'.

Note that the last two records have the same status and the dates of each need to be included in the calculation

somefield, status, recfromdate, recthrudate
'abc', 1, '2016-04-01', '2016-04-10'
'abc', 2, '2016-04-11', '2016-04-16'
'def', 2, '2016-04-17', '2016-04-19'
'def', 3, '2016-04-20', '2016-04-25'
'ghi', 3, '2016-04-26', '9999-12-31'

Upvotes: 1

Views: 48

Answers (2)

Dave C
Dave C

Reputation: 7392

Given the wording of your question, would you not just select the age based on the RecordFromDate of the current record? If I'm misunderstanding, please clarify your question with specifics/sample data & sample output.

DECLARE @HistoryTable TABLE (RecordStatus VARCHAR(100), RecordFromDate DATETIME, RecordThruDate DATETIME)

INSERT INTO @HistoryTable (RecordStatus, RecordFromDate, RecordThruDate)
VALUES ('Pending','2016-01-01 09:34:05','2016-04-01 19:34:43'),
       ('Approved','2016-04-01 19:34:43','2016-05-09 11:45:43'),
       ('Shipped','2016-05-09 11:45:32','9999-12-31 00:00:00')

SELECT *, DATEDIFF(DD,RecordFromDate,GETDATE()) AS AgeInDays
FROM @HistoryTable
WHERE RecordThruDate='9999-12-31'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The following gets the maximum date of the previous status:

select max(h.RecordThruDate)
from history h
where h.status <> (select h2.status
                   from history h2
                   where h2.recordthrudate = '9999-12-31'
                  );

You might want to add "1" to it to get the date. To get the duration, subtract the result from getdate().

Upvotes: 1

Related Questions