Create a column using select and add data to it depending on a condition?

Question

I'm asked to select some columns from a table. One of which does not exist in the table. This column is called PayComment and I need to use a conditional to check if there is data in another separate column inside that table. If there is PayComment will say 'Yes' If there isn't it will say 'no'.

I'm confused on how to do this because I've only ever created a column using an alias([OldColumn] AS [NewColumn]) or declared it as a variable and INSERT it into the table. However I have to create this column/use a conditional to determine what's inside it, while keeping it in the select statement whilst I grab the other existing columns.

I know this is wrong but this is what I'm imagining

CREATE PROC spPayIncreaseListing
AS
    SELECT FirstName, LastName, CONVERT(varchar, HireDate, 1) AS HireDate, PayRate, 
    CONVERT(varchar, StartDate, 1) AS PayRateStartDate, CONVERT(varchar, EndDate, 1) AS PayRateEndDate, 
    PayComment AS IF Work.COLA > 0
                        PayComment = 'Yes';
                  ELSE  
                        PayComment = 'NO';

You want a case expression

Dale K
– Dale K

2020-04-10 01:57:37 +00:00
Commented Apr 10, 2020 at 1:57 — Dale K
– Dale K, Commented Apr 10, 2020 at 1:57
Just edited it now

lolz
– lolz

2020-04-10 02:03:35 +00:00
Commented Apr 10, 2020 at 2:03 — lolz
– lolz, Commented Apr 10, 2020 at 2:03
@DaleK Here's what I'm expecting

lolz
– lolz

2020-04-10 02:07:54 +00:00
Commented Apr 10, 2020 at 2:07 — lolz
– lolz, Commented Apr 10, 2020 at 2:07
The 'PayComment' column needs to be created

lolz
– lolz

2020-04-10 02:08:31 +00:00
Commented Apr 10, 2020 at 2:08 — lolz
– lolz, Commented Apr 10, 2020 at 2:08
@Dale Im going to try the CASE expression

lolz
– lolz

2020-04-10 02:09:09 +00:00
Commented Apr 10, 2020 at 2:09 — lolz
– lolz, Commented Apr 10, 2020 at 2:09

Dale K · Accepted Answer · 2020-04-10 02:44:49Z

1

For what you are asking for this would be the case expression you would need:

CREATE PROC spPayIncreaseListing
AS
    SELECT FirstName, LastName, CONVERT(varchar, HireDate, 1) AS HireDate, PayRate, 
    CONVERT(varchar, StartDate, 1) AS PayRateStartDate, CONVERT(varchar, EndDate, 1) AS PayRateEndDate, 
    CASE WHEN Work.COLA > 0 THEN 'Yes' else 'No' END AS PayComment

edited Apr 10, 2020 at 2:44

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Apr 10, 2020 at 2:12

Ian-Fogelman

1,6151 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dale K Over a year ago

case expression not statement

Collectives™ on Stack Overflow

Create a column using select and add data to it depending on a condition?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related