SQL 2008 Computed Column - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL 2008 Computed Column

Author

Topic

marsanjf
Starting Member

1 Post

Posted - 2013-09-19 : 14:49:03

Hi I need help to calculate column with statement
like...

CostEventPrice
TicketSalesPrice (computed column)

If the CostEventPrice < 10,00$ then TicketSalesPrice = CostEventPrice + 2,00$
If the CostEventPrice between 10,00$ and 20,00$ then TicketSalesPrice = CostEventPrice + 3,00$
If the CostEventPrice > 20,00$ then TicketSalesPrice = CostEventPrice + 4,00$

Can someone help me!!

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 14:57:38

alter table table_name
add TicketSalesPrice as case when CostEventPrice < 10.00$ then CostEventPrice + 2.00$
when CostEventPrice between 10.00$ and 20.00$ then CostEventPrice + 3.00$
when CostEventPrice > 20.00$ then CostEventPrice + 4.00$
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:53:22

whats the datatype of TicketSalesPrice and CostEventPrice? assuming money it should be


alter table table_name
add TicketSalesPrice as case when CostEventPrice < $10.00 then CostEventPrice + $2.00
when CostEventPrice between $10.00 and 20.00$ then CostEventPrice + $3.00
when CostEventPrice > $20.00 then CostEventPrice + $4.00
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-22 : 06:12:53

Right, the dollar sign must be at the first of value.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-22 : 07:42:35

Alternative:


update yourtable
   set ticketsalesprice=costeventprice
                       +case
                           when costeventprice<10 then 2
                           when costeventprice<20 then 3
                           else 4
                        end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 08:25:35

quote:

Originally posted by bitsmed

Alternative:


update yourtable
   set ticketsalesprice=costeventprice
                       +case
                           when costeventprice<10 then 2
                           when costeventprice<20 then 3
                           else 4
                        end

this have to be done for each insertion/modification of value in ticketsalesprice field
the better approach is computed column which does this automatically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-22 : 09:35:56

quote:
Originally posted by visakh16

quote:
Originally posted by bitsmed

Alternative:

update yourtable
set ticketsalesprice=costeventprice
+case
when costeventprice<10 then 2
when costeventprice<20 then 3
else 4
end

this have to be done for each insertion/modification of value in ticketsalesprice field
the better approach is computed column which does this automatically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I'm sure OP is able to select from the above mentioned suggestions, which ever solution fits best for him/her. Maybe even a mixup of the above suggestions. Or maybe he/she doesn't want additional column in the table, and will do it on the fly in a select. For all I know, this also could be done in a trigger.
All i'm trying to say is, all suggestions offers a way of dealing with OP's question - now it's up to OP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 11:01:03

quote:
Originally posted by bitsmed

quote:
Originally posted by visakh16

quote:
Originally posted by bitsmed

Alternative:

update yourtable
set ticketsalesprice=costeventprice
+case
when costeventprice<10 then 2
when costeventprice<20 then 3
else 4
end

this have to be done for each insertion/modification of value in ticketsalesprice field
the better approach is computed column which does this automatically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I'm sure OP is able to select from the above mentioned suggestions, which ever solution fits best for him/her. Maybe even a mixup of the above suggestions. Or maybe he/she doesn't want additional column in the table, and will do it on the fly in a select. For all I know, this also could be done in a trigger.
All i'm trying to say is, all suggestions offers a way of dealing with OP's question - now it's up to OP.

OP clearly suggested use of computed column in the initial post.
I was just telling caveat of using your suggested approach... thats all
Computed column would still be better that trigger approach as in latter case it would still have overhead to preape internal temp table, then apply required logic copy back etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources