Adding a column in a SQL statement

John-S Pretorius
John-S Pretorius used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
add this column into the select:

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

Author

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 ?
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John-S PretoriusSystems Engineer

Author

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 Engineer

Author

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 ChongSoftware Team Lead

Commented:
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
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 Engineer

Author

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
Try "END AS RevRate", not "END RevRate".

If you still get an error please paste the whole statement again.
Ryan ChongSoftware Team Lead

Commented:
>>Try "END AS RevRate", not "END RevRate"
"AS" can be omitted in MS SQL environment...
John-S PretoriusSystems Engineer

Author

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
Do you still have that IIF .... AS RevRate at the end? Take that out.
Software Team Lead
Commented:
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

John-S PretoriusSystems Engineer

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial