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

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?

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

SOLUTION

Missus Miss_Sellaneus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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...

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John-S Pretorius

ASKER

That worked, thank you guys - I apologize for not being clear from the star.