Date Calculation and Querying multiple columns
-
I'm a bit of a SQL novice, so my apologies if these questions are very easy. Thanks in advance for any insight.
Question 1) My database has "date of birth" parsed into 3 columns: dobmonth, dobday and dobyear. To form a complete DOB, I need to put them together. I'm looking to calculate a person's exact age based on this information. The furthest I've been able to get is the following:
select (GETDATE()-(dobmonth+'/'+dobday+'/'+dobyear))/365
This kinda/sorta works. Except the dividing by 365 yields the follow error: Invalid operator for data type. Operator equals divide, type equals datetime. Is there an alternative code that would work in giving me their age?
Question 2) Is there a way to simplify the following WHERE clause?:
where A = '100' or B = '100' or c= '100' ... or Z = '100'
(Note: A through Z are all column names. Is there an easy way to query across all 26 columns in 1 simple step? i.e., "WHERE 100 is found at least once in columns A through Z'). If this is easy, can I take it a step futher and say "WHERE 100 is found at least N times in columns A through Z"?)
Thanks again! I really appreciate any help to these questions!
Andrew
-
Firstly what are the datatypes of your three columns and why store a date as three columns when a SMALLDATETIME will do?
My approach would be to use the CONVERT statement as follows
SELECT DATEDIFF(Day,GETDATE(),CONVERT(SMALLDATETIME,dobmonth+'/'+dobday+'/'+dobyear,101))/365
-
Q1) :
Why are you storing them separately rather than a single column ?
Looks like you have those stored in string data-types...there are couple of ways of doing this:
DECLARE @DOB TABLE (DOB_MONTH CHAR(2), DOB_DAY CHAR(2), DOB_YEAR CHAR(4))
INSERT INTO @DOB VALUES ('11', '29', '1971')
SELECT DATEDIFF(yy, cast((DOB_MONTH + '/' + DOB_DAY + '/' + DOB_YEAR) as datetime),getdate())
FROM @DOB
---
35
Q2) :
Why is there such a requirement ? i.e. why are we looking for the same data element in 26 columns ?
You can do the OR clause like you did or do a conditional case statement in which caseit will short ckt
and will exit on the first occurence of the record...something like:
where (case
when A = '100' then 1
when B = '100' then 1
.
.
else 0
end) = 1
though this is pretty much like an OR clause.
For the N times question, are looking for N occurences of 100 across rows for those 26 columns (i.e. 100 appears say 10
times across rows even though it might be in just 1/2 columns - group by and having will do this for you)
or are you looking for N occurences of 100 across A-Z ?
-
I see age calculations using a simple datediff, however how many of you have tested it. For those people whose DOB is between today and 31th Dec the age will be out by one.
here is an SQL function that Correctly calculates a persons age.
CREATE FUNCTION [Age] (@Date1 datetime,@Date2 datetime)
RETURNS Integer
AS
BEGIN
DECLARE @Age Integer
IF (dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2)
set @Age= datediff (year, @Date1, @Date2) - 1
ELSE
set @Age = datediff (year, @Date1, @Date2)
RETURN @Age
END
-
For question 2, instead of having a table with 26 columns each representing a value, it may be easier to store the data in tables like these:
create table PersonAttribute
(
PersonID integer,
AtttributeTypeID integer,
AttributeValue varchar(20)
)
and
create Table Attribute
(
AttributeTypeID integer,
AttributeDescription varchar(255)
)
By storing the data this way, it is easy to add additional attributes. It also minimizes storage where the data is sparsely populated, i.e. where not all people have values for all the attributes.
It would be easy to do your query:
select distinct PersonID from PersonAttribute where AttributeValue = '100'
or
select PersonID from PersonAttribute where AttributeValue = '100' group by PersonID having count(*) > 10
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply