SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Adding in a Column with a Calculated Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

a massive zebra
Starting Member

United Kingdom
5 Posts

Posted - 08/26/2014 :  15:17:03  Show Profile  Reply with Quote
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

Edited by - a massive zebra on 08/26/2014 15:30:12

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/26/2014 :  17:58:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

a massive zebra
Starting Member

United Kingdom
5 Posts

Posted - 08/26/2014 :  18:18:09  Show Profile  Reply with Quote
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

Edited by - a massive zebra on 08/26/2014 18:19:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/27/2014 :  00:37:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Run the code please.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

a massive zebra
Starting Member

United Kingdom
5 Posts

Posted - 08/27/2014 :  04:35:17  Show Profile  Reply with Quote
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?

Edited by - a massive zebra on 08/27/2014 04:46:19
Go to Top of Page

a massive zebra
Starting Member

United Kingdom
5 Posts

Posted - 08/27/2014 :  04:45:37  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/27/2014 :  05:01:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

a massive zebra
Starting Member

United Kingdom
5 Posts

Posted - 08/27/2014 :  10:40:15  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000