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 |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-01-17 : 03:16:54
|
how can i create table on case when condition? is it possible?age = case when DATEDIFF(YY,DOB, Date) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, Gender = case when Sex = 1 then 'M' Else 'F' End |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 03:20:33
|
if you mean creating computed columns you can useCREATE TABLE(...age as case when DATEDIFF(YY,DOB, Date) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, Gender as case when Sex = 1 then 'M' Else 'F' End)and of course [Date] and Sex columns have to be declared prior to above computed columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-01-17 : 03:34:59
|
can you explain how it actually works?there are no need for select statement right? |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-01-17 : 03:44:42
|
I got it..THANKS |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 03:46:24
|
quote: Originally posted by peace can you explain how it actually works?there are no need for select statement right?
nope..once you include it in create table as aboveyou dont have to insert any values in them as values will be automatically placed as per your expressionsee this exampleCREATE TABLE ABC(Name varchar(10),DOB datetime,Sex bit,age as case when DATEDIFF(YY,DOB, getdate()) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, Gender as case when Sex = 1 then 'M' Else 'F' End)insert ABC (Name,DOb,Sex)values('John','19870221',1),('Mary','19780516',0),('Joseph','19220103',1)SELECT * FROM ABCoutput-----------------------------------------Name DOB Sex age Gender-----------------------------------------John 1987-02-21 00:00:00.000 1 Unknown MMary 1978-05-16 00:00:00.000 0 Unknown FJoseph 1922-01-03 00:00:00.000 1 Senior Citizen M ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-17 : 03:51:02
|
Age and Gender will be populated by using DOB and sex columnsSee the example...CREATE TABLE testCase(id int,name varchar(10),DOB date,sex int,age as case when DATEDIFF(YY,DOB, getDate()) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, Gender as case when Sex = 1 then 'M' Else 'F' End)--Observe this. you are NOT providing data for age and Gender columnsinsert into testCase(id, name, DOB, sex) values(1, 'aaa', '1953-10-27', 2)--Here you can see data for age and Gender columnsSELECT * FROM testCase --Chandu |
|
|
|
|
|
|
|