CODEWITHSUNDEEP
sql-server
lolz
lolz

Reputation: 79

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

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';

enter image description here

Upvotes: 0

Views: 52

Answers (1)

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

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

Upvotes: 1

Related Questions