how to create new Columns by using if

I have a view called termdate: (termid,startingdate,endingdate,pre_edning) looks like:

termid  |  startingdate  |   endingdate   |    pre_edning  |        
-----+------------+------------+------------+---------------
 227    | 1950-01-31     | 1950-12-31     |                | 
 228    | 1951-01-31     | 1951-12-31     | 1950-12-31     | 
 229    | 1952-01-31     | 1952-12-31     | 1951-12-31     |
 230    | 1953-01-31     | 1953-12-31     | 1952-12-31     | 
 231    | 1954-01-31     | 1954-12-31     | 1953-12-31     | 
 232    | 1955-01-31     | 1955-12-31     | 1954-12-31     | 
 233    | 1956-01-31     | 1956-12-31     | 1955-12-31     |

I wish to create 2 new Columns called new_starting, new_ending by follow the rules below:

if startingdate - pre_ending>=7 
then 
new_starting := startingdate-7
new_ending := new_starting -1

I’am new to sql, Can someone please help me to achive that?

Answer

If I understand correctly, this is just case expressions:

select t.*,
       (case when startingdate >=  pre_ending + interval '7 day'  
             then startingdate - interval '7 day'
        end) as new_starting,
       (case when startingdate >= pre_ending + interval '7 day'  
             then startingdate - interval '8 day'
        end) as new_ending,
from t

Advertisement

Answer