Avatar of John-S Pretorius
John-S Pretorius
Flag for United States of America asked on

Adding a column in a SQL statement

I'm selecting the below columns from 2 SQL database tables and want to insert an additional column 'RevRate' for each row based on the value in {ParkingTransWithoutTurnover.ParkingDuration} to 30 if value < 30 and 50 if value >=30

----select tables
Select  ParkingTransWithoutTurnover.Time, ParkingTransWithoutTurnover.DeviceDesig, ParkingTransWithoutTurnover.CarparkNo,
        ParkingTransWithoutTurnover.CarparkDesig, ParkingTransWithoutTurnover.TransactionNo,ParkingTransWithoutTurnover.ArticleNo,
            ParkingTransWithoutTurnover.ArticleDesig, RIGHT(ParkingTransWithoutTurnover.CardNo,6) AS SPTCardNo,
            SPTPaymentWithValidationProviders.CardKey AS ValCardNo,
            ParkingTransWithoutTurnover.ParkingDuration, ParkingTransWithoutTurnover.RateNo, ParkingTransWithoutTurnover.RateDesig,
            SPTPaymentWithValidationProviders.ValidationProviderNo, SPTPaymentWithValidationProviders.ValidationProvider,
            SPTPaymentWithValidationProviders.RateReduction
          
--table joining
from dbo.ParkingTransWithoutTurnover full outer join dbo.SPTPaymentWithValidationProviders on
     (ParkingTransWithoutTurnover.Time = SPTPaymentWithValidationProviders.time)
                             
--time selection
where CAST(ParkingTransWithoutTurnover.Time AS DATE) = '2015/09/23' ORDER BY TRANSACTIONNO
Microsoft SQL Server

Avatar of undefined
Last Comment
John-S Pretorius

8/22/2022 - Mon
Missus Miss_Sellaneus

add this column into the select:

IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate
John-S Pretorius

ASKER
Thank you for your feedback, when I ad the line in at the end :- (I use a , to separate from the last input)

, IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate

I'm seeing :- 'the IIF statement requires 3 arguments'

Am I adding this addition correctly ?
Missus Miss_Sellaneus

It should have 3 arguments. Are you sure there isn't a period in place of one of the commas?

arg. 1: ParkingTransWithoutTurnover.ParkingDuration<30
arg. 2: 30
arg. 3: 50

If it's still not right, could you paste the whole statement here?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
John-S Pretorius

ASKER
Please see below:-

----select tables
 Select  IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate, ParkingTransWithoutTurnover.Time, ParkingTransWithoutTurnover.DeviceDesig, ParkingTransWithoutTurnover.CarparkNo,
         ParkingTransWithoutTurnover.CarparkDesig, ParkingTransWithoutTurnover.TransactionNo,ParkingTransWithoutTurnover.ArticleNo,
             ParkingTransWithoutTurnover.ArticleDesig, RIGHT(ParkingTransWithoutTurnover.CardNo,6) AS SPTCardNo,
             SPTPaymentWithValidationProviders.CardKey AS ValCardNo,
             ParkingTransWithoutTurnover.ParkingDuration, ParkingTransWithoutTurnover.RateNo, ParkingTransWithoutTurnover.RateDesig,
             SPTPaymentWithValidationProviders.ValidationProviderNo, SPTPaymentWithValidationProviders.ValidationProvider,
             SPTPaymentWithValidationProviders.RateReduction, IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate
           
 --table joining
 from dbo.ParkingTransWithoutTurnover full outer join dbo.SPTPaymentWithValidationProviders on
      (ParkingTransWithoutTurnover.Time = SPTPaymentWithValidationProviders.time)
                               
 --time selection
 where CAST(ParkingTransWithoutTurnover.Time AS DATE) = '2015/09/23' ORDER BY TRANSACTIONNO
John-S Pretorius

ASKER
Maybe I also add that I am looking at having additional if conditions :-

if duration < 30(mins) then 50 else
if duration >=30(mins) and < 60(mins) then 55 else
if duration >=60(mins) and < 90(mins) then 60 ........

Hopefully I will be able to figure out the rest.
Ryan Chong

you can try:

 Select
 case  ParkingTransWithoutTurnover.ParkingDuration
 when >=90 then <yourvalue>
 when >=60 then 60
 when >=30 then 55
 else 50
 end RevRate ,
 
 ParkingTransWithoutTurnover.Time, ParkingTransWithoutTurnover.DeviceDesig, ParkingTransWithoutTurnover.CarparkNo,
         ParkingTransWithoutTurnover.CarparkDesig, ParkingTransWithoutTurnover.TransactionNo,ParkingTransWithoutTurnover.ArticleNo,
             ParkingTransWithoutTurnover.ArticleDesig, RIGHT(ParkingTransWithoutTurnover.CardNo,6) AS SPTCardNo,
             SPTPaymentWithValidationProviders.CardKey AS ValCardNo,
             ParkingTransWithoutTurnover.ParkingDuration, ParkingTransWithoutTurnover.RateNo, ParkingTransWithoutTurnover.RateDesig,
             SPTPaymentWithValidationProviders.ValidationProviderNo, SPTPaymentWithValidationProviders.ValidationProvider,
             SPTPaymentWithValidationProviders.RateReduction, IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate
           
 --table joining
 from dbo.ParkingTransWithoutTurnover full outer join dbo.SPTPaymentWithValidationProviders on
      (ParkingTransWithoutTurnover.Time = SPTPaymentWithValidationProviders.time)
                               
 --time selection
 where CAST(ParkingTransWithoutTurnover.Time AS DATE) = '2015/09/23' ORDER BY TRANSACTIONNO 

Open in new window


in which you need to handle condition if ParkingDuration >=90, hence replace <yourvalue> with a value accordingly
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Missus Miss_Sellaneus

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John-S Pretorius

ASKER
Guys, thank you so much for your responses,

I'm still seeing the following errors :

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '>'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'RevRate'.

FYI, not sure if it matters but I'm using SQL 2104
Missus Miss_Sellaneus

Try "END AS RevRate", not "END RevRate".

If you still get an error please paste the whole statement again.
Ryan Chong

>>Try "END AS RevRate", not "END RevRate"
"AS" can be omitted in MS SQL environment...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
John-S Pretorius

ASKER
Still seeing an error.

Select
 case  ParkingTransWithoutTurnover.ParkingDuration
 when >=90 then 100
 when >=60 then 60
 when >=30 then 55
 else 50
 end AS RevRate ,
 
ParkingTransWithoutTurnover.Time, ParkingTransWithoutTurnover.DeviceDesig, ParkingTransWithoutTurnover.CarparkNo,
         ParkingTransWithoutTurnover.CarparkDesig, ParkingTransWithoutTurnover.TransactionNo,ParkingTransWithoutTurnover.ArticleNo,
             ParkingTransWithoutTurnover.ArticleDesig, RIGHT(ParkingTransWithoutTurnover.CardNo,6) AS SPTCardNo,
             SPTPaymentWithValidationProviders.CardKey AS ValCardNo,
             ParkingTransWithoutTurnover.ParkingDuration, ParkingTransWithoutTurnover.RateNo, ParkingTransWithoutTurnover.RateDesig,
             SPTPaymentWithValidationProviders.ValidationProviderNo, SPTPaymentWithValidationProviders.ValidationProvider,
             SPTPaymentWithValidationProviders.RateReduction, IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate
           
 --table joining
 from dbo.ParkingTransWithoutTurnover full outer join dbo.SPTPaymentWithValidationProviders on
      (ParkingTransWithoutTurnover.Time = SPTPaymentWithValidationProviders.time)
                               
 --time selection
 where CAST(ParkingTransWithoutTurnover.Time AS DATE) = '2015/09/23' ORDER BY TRANSACTIONNO
Missus Miss_Sellaneus

Do you still have that IIF .... AS RevRate at the end? Take that out.
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John-S Pretorius

ASKER
That worked, thank you guys - I apologize for not being clear from the star.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.