Annie Jeba Annie Jeba - 1 month ago 6
SQL Question

Set default value in SQL Query

Assume there is a table

Emp_id Name Comments
1 Ana Test
2 Dana Test1
3 Fiona Test2


The below query will retrieve all the records from my table, and now i have to retrieve date SysDate + 5 Working days (Excluding Saturdays and Sundays) as well which is not in table

select emp_id,Name,Comments from Employees


And the expected output is

Emp_id Name Comments Date
1 Ana Test 28.10.2016
2 Dana Test1 28.10.2016
3 Fiona Test2 28.10.2016


Your help is much appreciated

Answer

Try below query:

select emp_id, Name, Comments, 
  sysdate + 
    decode(to_char(sysdate,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 1,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 2,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 3,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 4,'DY'), 'SAT',2, 'SUN', 2, 1) My_Date 
from Employees

Above query will return you Date field in Date format, if you need it the way you have mentioned please try below query:

select emp_id, Name, Comments, 
  to_char(sysdate + 
    decode(to_char(sysdate,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 1,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 2,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 3,'DY'), 'SAT',2, 'SUN', 2, 1) +
    decode(to_char(sysdate + 4,'DY'), 'SAT',2, 'SUN', 2, 1),'DD.MM.YYYY') My_Date 
from Employees

I'm not sure if this is the best way to do it, but this is the one way to do it.