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 2008 Forums
 Transact-SQL (2008)
 period alive synchronously

Author  Topic 

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-03-01 : 10:19:47
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 12:21:07
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

529 Posts

Posted - 2013-03-01 : 13:56:37
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

529 Posts

Posted - 2013-03-01 : 14:11:47
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
   

- Advertisement -