August 20, 2008 at 12:17 pm
Ok. So using the NORTHWIND database id like to be able to calculate a person age based on their date of birth. For that portion I have this simple code.
select year(getdate()) - year(birthdate) - case when month(getdate()) > month(birthdate)
then 0
else
case when month(getdate()) < month(birthdate)
then 1
else
case when day(getdate())< day(birthdate)
then 1
else 0
end
end
end as age
from employees
Which works fine and gives me a persons age. Now what Im having trouble with is I need to somehow update the "title" column in that same table, (in my actual enviroment I need to update a DIFFERENT table, but to get the concept im just trying to use a single table) based on their age. So lets says anyone over 50 needs the title "Sr". How can I run a script to
A. automaticall caluclate the age based on dob (which I believe my above code accomplishes)
B. Update a column in the table (or a different table) with a particular value based on a person age.
What is the best way of approaching this? I was also thinking I could use a select into to populate a person age into a temp table, then update a value in a different table using that temp table, but im open to all ideas. Thanks!
August 20, 2008 at 12:37 pm
You could do this:
[font="Courier New"]UPDATE dbo.Persons
SET title = CASE
WHEN DATEDIFF(YEAR, GETDATE(), birth_date) >= 50 THEN 'Sr'
ELSE 'Jr'
END
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 12:42 pm
Assuming of course that you cannot just use the UPDATE's WHERE clause (presumably because there are other columns being updated is this same UPDATE statement, according to different criteria)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 12:43 pm
Jack Corbett (8/20/2008)
You could do this:
[font="Courier New"]UPDATE dbo.Persons
SET title = CASE
WHEN DATEDIFF(YEAR, GETDATE(), birth_date) >= 50 THEN 'Sr'
ELSE 'Jr'
END
[/font]
Thanks, but your case statement doesnt take into account for leap years, or peoples birthdays who have yet to occur yet in the current year. But the idea looks ok and my case statement should work, BUT, assuming your updating the persons table (which is fine in this case) what if the "birthdate" column was in lets says the Employee table, how then would that be done?
August 20, 2008 at 12:45 pm
rbarryyoung (8/20/2008)
Assuming of course that you cannot just use the UPDATE's WHERE clause (presumably because there are other columns being updated is this same UPDATE statement, according to different criteria)
Can you expand on that? Essentially I have one table which stores the dob, which I would like to use to calculate age, then based on that age, update a DIFFERENT table with a particular value.
August 20, 2008 at 12:57 pm
craig (8/20/2008)
rbarryyoung (8/20/2008)
Assuming of course that you cannot just use the UPDATE's WHERE clause (presumably because there are other columns being updated is this same UPDATE statement, according to different criteria)Can you expand on that? Essentially I have one table which stores the dob, which I would like to use to calculate age, then based on that age, update a DIFFERENT table with a particular value.
Not worrying about the AGE function correctness thing, I think that this is a fair example:
UPDATE E
Set Title = LTrim(Title + ' Dr.')
From Employees E
Where NOT Title Like '%Dr.%'
And Exists( Select *
From Persons P
Where P.PersID = E.PersID
And (P.Degrees Like '%PHD%'
Or P.Degrees Like '%MD%'))
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 1:20 pm
craig (8/20/2008)
Thanks, but your case statement doesnt take into account for leap years, or peoples birthdays who have yet to occur yet in the current year. But the idea looks ok and my case statement should work, BUT, assuming your updating the persons table (which is fine in this case) what if the "birthdate" column was in lets says the Employee table, how then would that be done?
I'm not sure what you mean by doesn't take into account leap years or people whose birthdays have yet to occur in the current year (someone under 1 year old?)?
My wife's birthday takes place in September and until that day comes I wouldn't refer to her as that age yet. If you want to get the "age" of someone under 1 year old it only takes a simple Case When DateDiff(Year, birth_date, getdate()) = 0 then 1 Else DateDiff(Year, birth_date, getdate()) End.
This statement:
Select
DateDiff(year, '2/29/1980', getdate()) as leap_day_birthday
Returns 28 which is how old someone born on that date would be.
Now to do it from another table you could do:
[font="Courier New"]-- Generate test data
DECLARE @persons TABLE (person_id INT IDENTITY(1,1), dob smalldatetime)
DECLARE @employees TABLE (person_id INT, title VARCHAR(5), age smallint)
DECLARE @m INT, @y INT
SET @y = 76
WHILE @y > 0
BEGIN
SET @m = 12
WHILE @m > 4
BEGIN
INSERT INTO @persons (dob)
SELECT
DATEADD(DAY, (@y - @m)/3, DATEADD(MONTH, -@m, DATEADD(YEAR, -@y, GETDATE())))
SET @m = @m - 4 * RAND(4)
END
SET @y = @y - 3 * RAND(7)
IF @y = 0
BEGIN
SET @y = 1
END
END
-- insert baby
INSERT INTO @persons (dob)
SELECT
'5/7/08'
INSERT INTO @employees(Person_id)
SELECT
person_id
FROM @persons
-- Do the update
UPDATE @employees
SET title = CASE
WHEN DATEDIFF(YEAR, P.dob, GETDATE()) >= 50 THEN 'Sr'
ELSE 'Jr'
END,
age = CASE
WHEN DATEDIFF(YEAR, P.dob, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END
FROM
@employees E JOIN
@persons P ON
E.person_Id = p.person_id
SELECT
*
FROM
@employees E JOIN
@persons P ON
E.person_id = P.person_id
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 1:28 pm
Jack Corbett (8/20/2008)
craig (8/20/2008)
Thanks, but your case statement doesnt take into account for leap years, or peoples birthdays who have yet to occur yet in the current year. But the idea looks ok and my case statement should work, BUT, assuming your updating the persons table (which is fine in this case) what if the "birthdate" column was in lets says the Employee table, how then would that be done?
I'm not sure what you mean by doesn't take into account leap years or people whose birthdays have yet to occur in the current year (someone under 1 year old?)?
My wife's birthday takes place in September and until that day comes I wouldn't refer to her as that age yet. If you want to get the "age" of someone under 1 year old it only takes a simple
Case When DateDiff(Year, birth_date, getdate()) = 0 then 1 Else DateDiff(Year, birth_date, getdate()) End.This statement:
Select
DateDiff(year, '2/29/1980', getdate()) as leap_day_birthday
Returns 28 which is how old someone born on that date would be.
Now to do it from another table you could do:
[font="Courier New"]-- Generate test dataDECLARE @persons TABLE (person_id INT IDENTITY(1,1), dob smalldatetime)
DECLARE @employees TABLE (person_id INT, title VARCHAR(5), age smallint)
DECLARE @m INT, @y INT
SET @y = 76
WHILE @y > 0
BEGIN
SET @m = 12
WHILE @m > 4
BEGIN
INSERT INTO @persons (dob)
SELECT
DATEADD(DAY, (@y - @m)/3, DATEADD(MONTH, -@m, DATEADD(YEAR, -@y, GETDATE())))
SET @m = @m - 4 * RAND(4)
END
SET @y = @y - 3 * RAND(7)
IF @y = 0
BEGIN
SET @y = 1
END
END
-- insert baby
INSERT INTO @persons (dob)
SELECT
'5/7/08'
INSERT INTO @employees(Person_id)
SELECT
person_id
FROM @persons
-- Do the update
UPDATE @employees
SET title = CASE
WHEN DATEDIFF(YEAR, P.dob, GETDATE()) >= 50 THEN 'Sr'
ELSE 'Jr'
END,
age = CASE
WHEN DATEDIFF(YEAR, P.dob, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END
FROM
@employees E JOIN
@persons P ON
E.person_Id = p.person_id
SELECT
*
FROM
@employees E JOIN
@persons P ON
E.person_id = P.person_id
[/font]
My statement was worded wrong. What I meant was if for example your birthday is Sep 10 1980 and today is Sep 1 2000 your age would incorrectly show as 20 even though your not actually 20 until Sep 10. So thats why I needed to compare the month/day to ensure I am getting the most accurate representation of age. Unfortunetly looking at your code is a bit beyond my comprehension, so I will continue to review it. It has/had nothing to do with a child under 1. Appreciate the time.
August 20, 2008 at 1:49 pm
Okay, I understand what you mean now. As far as my code, you can ignore all but the update as the rest is just generating some data to test against so you and I can both see that the results are what you want. Here is my amended code:
[font="Courier New"]-- Generate test data
DECLARE @persons TABLE (person_id INT IDENTITY(1,1), dob smalldatetime)
DECLARE @employees TABLE (person_id INT, title VARCHAR(5), age smallint)
DECLARE @m INT, @y INT
SET @y = 76
WHILE @y > 0
BEGIN
SET @m = 12
WHILE @m > 4
BEGIN
INSERT INTO @persons (dob)
SELECT
DATEADD(DAY, (@y - @m)/3, DATEADD(MONTH, -@m, DATEADD(YEAR, -@y, GETDATE())))
SET @m = @m - 4 * RAND(4)
END
SET @y = @y - 3 * RAND(7)
IF @y = 0
BEGIN
SET @y = 1
END
END
-- insert birthday after todays date
INSERT INTO @persons (dob)
SELECT
'10/7/1980'
UNION ALL
SELECT
'8/21/1980'
INSERT INTO @employees(Person_id)
SELECT
person_id
FROM @persons
-- Do the update
UPDATE @employees
SET title = CASE
WHEN CASE
WHEN MONTH(P.dob)>MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END) >= 50 THEN 'Sr'
ELSE 'Jr'
END,
age = CASE
WHEN DATEDIFF(YEAR, P.dob, GETDATE()) = 0 THEN 1
WHEN MONTH(P.dob)>MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END
FROM
@employees E JOIN
@persons P ON
E.person_Id = p.person_id
SELECT
*
FROM
@employees E JOIN
@persons P ON
E.person_id = P.person_id
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 2:28 pm
^^ Thank you! I will begin testing this with my actual tables. I wasnt aware you would do an update directly from a case statement.
*edit*
I dont need to directly store the age, so I think I can get rid of that portion. Im assuming I wont have a problem with setting a numeric value, which is what I need to do? So what im actually do is finding someones age (based on dob) and then adjusting their max contribution in a different table. Basically if your age greater than or = to 50 then max amount=50,000 otherwise that column is not updated.
August 20, 2008 at 2:53 pm
Glad I could help.
Just a little tip, you should share what you are really trying to do, unless doing that would violate some policy at your office. Then just build a little set of test data, and give your desired results. That will lead to getting the right answer faster. There are some folks who won't even attmpt to answer a question like yours if there isn't some test data to work with.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 2:56 pm
Jack Corbett (8/20/2008)
Glad I could help.Just a little tip, you should share what you are really trying to do, unless doing that would violate some policy at your office. Then just build a little set of test data, and give your desired results. That will lead to getting the right answer faster. There are some folks who won't even attmpt to answer a question like yours if there isn't some test data to work with.
I know and I appreciate you taking the time to do so. I actually dont know how to format like you did in your posts, but think I saw a how-to at somepoint with these rules? Thats why I was hoping to use the test database because I am more worried about concept as opposed to exact design, in this case.
August 21, 2008 at 9:40 am
Jack Corbett (8/20/2008)
Glad I could help.Just a little tip, you should share what you are really trying to do, unless doing that would violate some policy at your office. Then just build a little set of test data, and give your desired results. That will lead to getting the right answer faster. There are some folks who won't even attmpt to answer a question like yours if there isn't some test data to work with.
Out of curiosity was there a problem with my original case statement? To me it seems much simpler and less coding is always a plus. But I could be missing something?
August 21, 2008 at 10:24 am
Not sure where im going wrong here, but I adjusted your code slightly to fit my needs. But when I try to run the update statement I recieve the error "Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near ')'. I also recieved this error with your orginal code, any ideas?
-- Generate test data
Create TABLE People(person_id INT IDENTITY(1,1), dob smalldatetime)
Create TABLE Benefits(person_id INT, dedyrmax VARCHAR(5), age smallint)
DECLARE @m INT, @y INT
SET @y = 76
WHILE @y > 0
BEGIN
SET @m = 12
WHILE @m > 4
BEGIN
INSERT INTO People (dob)
SELECT
DATEADD(DAY, (@y - @m)/3, DATEADD(MONTH, -@m, DATEADD(YEAR, -@y, GETDATE())))
END
SET @y = @y - 3 * RAND(7)
IF @y = 0
BEGIN
SET @y = 1
END
END
-- insert birthday after todays date
INSERT INTO people (dob)
SELECT
'10/7/1980'
UNION ALL
SELECT
'8/21/1980'
INSERT INTO benefits(Person_id)
SELECT
person_id
FROM People
-- Do the update
UPDATE Benefits
SET dedyrmax = CASE
WHEN CASE
WHEN MONTH(P.dob)>MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END) >= 50 THEN '50000'
ELSE dedyrmax
END,
August 21, 2008 at 10:30 am
I wouldn't say that there is anything wrong with it. I'd do it something like this:
[font="Courier New"]YEAR(GETDATE()) - YEAR(birthdate) - CASE
WHEN MONTH(GETDATE()) < MONTH(birthdate) THEN 1
WHEN DAY(GETDATE())< DAY(birthdate) THEN 1
ELSE 0
END AS age
[/font]
Versus mine:
[font="Courier New"]CASE
WHEN CASE
WHEN MONTH(dob) >MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1
ELSE DATEDIFF(YEAR, P.dob, GETDATE())
END >= 50 THEN 'Sr'
ELSE 'Jr'
END[/font]
versus original:
[font="Courier New"]YEAR(GETDATE()) - YEAR(birthdate) - CASE
WHEN MONTH(GETDATE()) > MONTH(birthdate) THEN 0
ELSE CASE
WHEN MONTH(GETDATE()) < MONTH(birthdate) THEN 1
ELSE CASE
WHEN DAY(GETDATE())< DAY(birthdate) THEN 1
ELSE 0
END
END
END
[/font]
It's really preference. I find yours a bit harder to read and with all the nested CASE's a little confusing. If you haven't noticed, I'm a bit fussy about formatting and I like having the CASE at the beginning if at all possible. Also you can see I reduce in both the change in yours and in mine the number of CASE WHEN's needed. In mine I put all the logic in 1 nested CASE WHEN and in the top one I removed the first CASE WHEN because anything that doesn't meet the conditions would be 0 so let it fall through to that. I would actually combine the 2 CASE WHEN's onto 1 line (OR'ing) in your original as well since the THEN is the same.
Also it's kind of comparing apple's and orange's as the outcome of mine is the final comparison giving the text you said you were after while yours is just getting the age, which would eliminate the outer CASE in my code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply