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.
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 olderselect name, datediff(yyyy,dead,DOB) alive, datepart(yyyy,DOB)-1976 [date difference]from [age today]order by 3 descselect name, DOB, datediff(yyyy,dead,DOB) alive_for, datepart(yyyy,DOB )- 1976 mytimewithfrom [age today]looking for the best way to use coalesce or (case ifnull then) to clear the nullsor redesign to not allow nulls, and use a flag binary for dead, although DATE is neededand I'll add parameters for thison a grand scalethis will be for a large HR table to report on deceased peopleThis 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). |
|
|
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 lattername DOB alive_for mytimewithMike Bird 1976-06-13 NULL 0Ken Bird 1948-05-31 NULL -28Colin Bird 1949-01-10 -48 -27Mum 1927-12-25 -79 -49Vicky Bird 1974-08-14 NULL -2Sam Bird 1985-05-10 NULL 9Georgie Bird 1989-03-05 NULL 13Hayley Bird 1982-08-12 NULL 6Louise Bird 1984-07-13 NULL 8Annie Bird 1999-02-06 NULL 23Patrick Moore 1923-03-04 -89 -53I'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
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 nullname DOB deadColin Bird 1949-01-10 1997-01-10Mum 1927-12-25 2006-11-11Patrick Moore 1923-03-04 2012-10-09 |
|
|
|
|
|
|
|