CODEWITHSUNDEEP
sqloracle-databaseoracle12c
JagaSrik
JagaSrik

Reputation: 718

Can you write a single query without using any 'with' clause

This is the table DATA_TABLE 

      Date        Value
01-01-2018        31.64
01-02-2018        32.09
01-03-2018        36.9
   ..              ..

Need output in single query as shown below

Date is a Date column, d-1 column has the value of a day before value, d for specific date value (01-02-2018) and d+2 has the value of next date in DATA_TABLE that is 36.9

O/P Needed in a single query (dates having sat and sunday should not be considered)

  Date             d-1           d           d+2
01-02-2018        31.64        32.09        36.9

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You should be able to use LEAD and LAG here:

SELECT
    Date,
    LAG(Value) OVER (ORDER BY Date) "d-1",
    Value,
    LEAD(Value) OVER (ORDER BY Date) "d+2"
FROM DATA_TABLE

Upvotes: 1

Related Questions