Creating new column in sql derived from other columns

Question

Creating new column in sql derived from other columns

pwalter83

SSChampion

Points: 14575
More actions
January 31, 2011 at 3:15 am

#142280

Hello,
Good Day !!!
Could someone please help me ? I am trying to create a new column(TEU) from the existing columns in a table. The requirement is :
Calculate TEU based on EQ_CNT column from MG_BOOKING_EQUIPMENT_REQ table, i.e. if KL_EQUIPMENT_TYPE_CD starts with '20' then multiply x 1, if starts with '40' or 45 then multiply x2.
TEU will then be sum of all these calculations.
The DDL for the table is:
CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_TEST](
[BOOKING_ID] [numeric](10, 0) NULL,
[EQ_CNT] [numeric](3, 0) NULL,
[KL_EQUIPMENT_TYPE_CD] [varchar](5) NULL
)
-----------------------------------------------------------------
The data for the above table is:
Insert into MG_BOOKING_EQUIPMENT_REQ
values('1','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('2','1','40R96')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('3','2','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('4','3','20O86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('5','2','45D96')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('6','1','40O86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('7','2','20F86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('8','2','40F86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('9','1','20R86')
------------------------------------------------------
Thanks in advance for your help.
Kind Regards,
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can create a computed column that does it. When you create a computed column you don't have to specify its data type and you use the key word as to specify the way to compute the value. Here is a script that does it:

CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_REQ](

[BOOKING_ID] [numeric](10, 0) NULL,

[EQ_CNT] [numeric](3, 0) NULL,

[KL_EQUIPMENT_TYPE_CD] [varchar](5) NULL,

TEU as (CASE WHEN KL_EQUIPMENT_TYPE_CD like '20%'THEN 1 WHEN KL_EQUIPMENT_TYPE_CD like '40%' THEN 2 WHEN KL_EQUIPMENT_TYPE_CD like '45%' THEN 2 END * EQ_CNT))

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Hi Adi,

Thanks a lot for your reply. I had just one more query for you. How would you then sum the TEU which would be based on the rows per Booking_ID ?

Kind Regards,

Paul

pietlinden SSC Guru Points: 63509 More actions · Answer 3

just create a summary query

SELECT Booking_ID, SUM(TEU) As BookingTotal

FROM [MG_BOOKING_EQUIPMENT_REQ]

GROUP BY Booking_ID

ORDER BY Booking_ID;