| Author |
 Topic  |
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
 Posted - 03/01/2013 : 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 |
Edited by - mikebird on 03/01/2013 12:19:45
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 03/01/2013 : 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). |
 |
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
Posted - 03/01/2013 : 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 |
|
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
Posted - 03/01/2013 : 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 |
|
|
| |
Topic |
|
|
|
period alive synchronously
