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 2008 Forums
 Transact-SQL (2008)
 period alive synchronously
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 03/01/2013 :  10:19:47  Show Profile  Reply with Quote
CREATE TABLE [dbo].[age today](
[name] [nchar](15) NOT NULL,
[DOB] [date] NOT NULL,
[dead] [date] NULL
)

wanting a tidy way to express your time with people since your own year date (eg. 1976)
for those younger or older, or the same age (0)
the final column gives (-) for those older

select name, datediff(yyyy,dead,DOB) alive,
datepart(yyyy,DOB)-1976 [date difference]
from [age today]
order by 3 desc

select name, DOB, datediff(yyyy,dead,DOB) alive_for,
datepart(yyyy,DOB )- 1976 mytimewith
from [age today]


looking for the best way to use coalesce or (case ifnull then) to clear the nulls
or redesign to not allow nulls, and use a flag binary for dead, although DATE is needed
and I'll add parameters for this


on a grand scale
this will be for a large HR table to report on deceased people

This will go well for tagged farm animals to group by lifecycle per breed

Edited by - mikebird on 03/01/2013 12:19:45

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/01/2013 :  12:21:07  Show Profile  Reply with Quote
Can you post some sample data along with the desired output that you are expecting to see for that sample input? Descriptions are harder to read and understand than sample data for me (and probably for many others on the forum).
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 03/01/2013 :  13:56:37  Show Profile  Reply with Quote
not sure if you want pure table content or the result of the second query. I'll go for the latter

name DOB alive_for mytimewith

Mike Bird 1976-06-13 NULL 0
Ken Bird 1948-05-31 NULL -28
Colin Bird 1949-01-10 -48 -27
Mum 1927-12-25 -79 -49
Vicky Bird 1974-08-14 NULL -2
Sam Bird 1985-05-10 NULL 9
Georgie Bird 1989-03-05 NULL 13
Hayley Bird 1982-08-12 NULL 6
Louise Bird 1984-07-13 NULL 8
Annie Bird 1999-02-06 NULL 23
Patrick Moore 1923-03-04 -89 -53

I'm hoping to get the 3rd and 4th columns into one, eliminating NULL, thinking of getdate() instead of NULL where there is no date of death, showing a date range including month and day differences for the user's DOB input parameter to show since they were born compared to anyone else's death, which is displayed in the 4th column, but a bit clumsily
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 03/01/2013 :  14:11:47  Show Profile  Reply with Quote
should have put result of this:

SELECT [name]
,[DOB]
,[dead]
FROM [hardcore].[dbo].[age today]
where dead is not null

name DOB dead
Colin Bird 1949-01-10 1997-01-10
Mum 1927-12-25 2006-11-11
Patrick Moore 1923-03-04 2012-10-09
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.11 seconds. Powered By: Snitz Forums 2000