SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 create table case when condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

415 Posts

Posted - 01/17/2013 :  03:16:54  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/17/2013 :  03:20:33  Show Profile  Reply with Quote
if you mean creating computed columns you can use
CREATE 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

415 Posts

Posted - 01/17/2013 :  03:34:59  Show Profile  Reply with Quote
can you explain how it actually works?

there are no need for select statement right?
Go to Top of Page

peace
Constraint Violating Yak Guru

415 Posts

Posted - 01/17/2013 :  03:44:42  Show Profile  Reply with Quote
I got it..
THANKS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/17/2013 :  03:46:24  Show Profile  Reply with Quote
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 above

you dont have to insert any values in them as values will be automatically placed as per your expression

see this example

CREATE 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 ABC



output
-----------------------------------------
Name	DOB	Sex	age	Gender
-----------------------------------------
John	1987-02-21 00:00:00.000	1	Unknown	M
Mary	1978-05-16 00:00:00.000	0	Unknown	F
Joseph	1922-01-03 00:00:00.000	1	Senior Citizen	M



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 01/17/2013 :  03:51:02  Show Profile  Reply with Quote
Age and Gender will be populated by using DOB and sex columns
See 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 columns
insert into testCase(id, name, DOB, sex) values(1, 'aaa', '1953-10-27', 2)

--Here you can see data for age and Gender columns
SELECT * FROM testCase


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000