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
John-S PretoriusSystems EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Missus Miss_SellaneusCommented:
add this column into the select:

IIF(ParkingTransWithoutTurnover.ParkingDuration<30,30,50) AS RevRate
John-S PretoriusSystems EngineerAuthor Commented:
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_SellaneusCommented:
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?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

John-S PretoriusSystems EngineerAuthor Commented:
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 PretoriusSystems EngineerAuthor Commented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Missus Miss_SellaneusCommented:
For more than 2 possibilities, use the CASE function instead of IIF.

I believe this should be correct.. I'm a bit rusty. The syntax might vary slightly depending on what you are using:

Just change it depending on your needs.. it will assign the value based on the first expression that matches. Make sure you have the ELSE at the end.

CASE duration WHEN <=30 THEN 50 WHEN <= 60 THEN 55  WHEN <= 90 THEN 85  ELSE 90 END AS RevRate


I still wonder why you were getting that error with the IIF!

Edit: beaten by 1 minute 19 seconds!
John-S PretoriusSystems EngineerAuthor Commented:
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_SellaneusCommented:
Try "END AS RevRate", not "END RevRate".

If you still get an error please paste the whole statement again.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Try "END AS RevRate", not "END RevRate"
"AS" can be omitted in MS SQL environment...
John-S PretoriusSystems EngineerAuthor Commented:
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_SellaneusCommented:
Do you still have that IIF .... AS RevRate at the end? Take that out.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

...
 case
 when ParkingTransWithoutTurnover.ParkingDuration >=90 then 100
 when ParkingTransWithoutTurnover.ParkingDuration  >=60 then 60
 when ParkingTransWithoutTurnover.ParkingDuration  >=30 then 55
 else 50
 end RevRate
...

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John-S PretoriusSystems EngineerAuthor Commented:
That worked, thank you guys - I apologize for not being clear from the star.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.