Adding in a Column with a Calculated Date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Adding in a Column with a Calculated Date

Author

Topic

a massive zebra
Starting Member

5 Posts

Posted - 2014-08-26 : 15:17:03

Hi,

I have a table with columns showing names, birth dates and death dates. I want to add another column showing the date at which each person became the oldest living person in the list. I somehow need to compare their birth date with the birth and death dates of the other people in the table and take the death date of the oldest living person at the time they were second oldest.

Is this possible and how would I do it?

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-26 : 17:58:43

[code]DECLARE @Sample TABLE
(
Person VARCHAR(10) NOT NULL,
Birth DATE NOT NULL,
Death DATE NULL
);

INSERT @Sample
(
Person,
Birth,
Death
)
VALUES ('A', '19120423', '19961230'),
('B', '19951002', NULL),
('C', '19290423', '20140825'),
('D', '19700101', NULL);

-- SwePeso
SELECT Person,
Birth,
Death,
CASE
WHEN DATEDIFF(DAY, Birth, ISNULL(Death, GETDATE())) = MAX(DATEDIFF(DAY, Birth, ISNULL(Death, GETDATE()))) OVER () THEN 1
ELSE 0
END AS OldestEver,
CASE
WHEN Death IS NULL AND DATEDIFF(DAY, Birth, ISNULL(Death, GETDATE())) = MAX(CASE WHEN Death IS NULL THEN DATEDIFF(DAY, Birth, ISNULL(Death, GETDATE())) ELSE 0 END) OVER () THEN 1
ELSE 0
END AS OldestAlive,
CASE
WHEN Death IS NULL THEN DATEADD(DAY, 1 + MAX(DATEDIFF(DAY, Birth, Death)) OVER (), Birth)
ELSE NULL
END AS CalculatedOldestEver
FROM @Sample;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

a massive zebra
Starting Member

5 Posts

Posted - 2014-08-26 : 18:18:09

Thanks very much for your help.

But wouldn't this create a list of the oldest ever? I want to determine the date at which everyone became the oldest living person even if there had been older deceased people in the past.

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-27 : 00:37:10

Run the code please.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

a massive zebra
Starting Member

5 Posts

Posted - 2014-08-27 : 04:35:17

Thanks.

Obviously this sample table uses dummy data. I want to source the data from another table named miworkingarea.Dates. I tried to do this by replacing:

VALUES ('A', '19120423', '19961230'),
('B', '19951002', NULL),
('C', '19290423', '20140825'),
('D', '19700101', NULL);

with

VALUES (SELECT * FROM miworkingarea.Dates)

But this gives me the following error:

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.

How do I replace the dummy data with data from the Dates table? Possibly INSERT INTO?

a massive zebra
Starting Member

5 Posts

Posted - 2014-08-27 : 04:45:37

Also, the CalculatedOldestEver column appears to calculate the date at which all living people would become the oldest ever. I actually want to calculate the date at which all people (living or deceased) became the oldest living person. So for instance, in your dummy data, person C became the oldest living person on 12th December 1996 when person A died. And person D became the oldest living person on 25th August 2014 when person C died. Person B has never been the oldest living person so this should show as NULL.

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-27 : 05:01:27

The dates will be refreshed whenever a change is made; new row, deleted row or updated row.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

a massive zebra
Starting Member

5 Posts

Posted - 2014-08-27 : 10:40:15

This gives me what I want for the first entry but I somehow need to loop round and apply it to all subsequent entries:

Create table #Temp (Code Varchar(50),Birth Date,Death date, OldDate Date)

Truncate Table #temp

Insert into #Temp
Select A.*,b.minSt from
Dates A
Inner Join
(Select
MIN(Birth) minSt
from
Dates
) B
on a.Birth = b.minSt

Insert into #Temp
Select *,Null from
Dates
Where Death <= (Select MAX(Death) from #temp)
and Birth > (Select MAX(Birth) from #temp)

Select * from #temp

Drop Table #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources