I have two tables with the structures as below
table one
╔════╦═══════════╦═══════╦══╗ ║ ID ║ Date ║ value ║ ║ ╠════╬═══════════╬═══════╬══╣ ║ 1 ║ 1/1/2015 ║ 234 ║ ║ ║ 2 ║ 1/20/2015 ║ 267 ║ ║ ║ 3 ║ 1/25/2015 ║ 270 ║ ║ ╚════╩═══════════╩═══════╩══╝
second table
╔════════════╦═══════════╗ ║ start_date ║ end date ║ ╠════════════╬═══════════╣ ║ 1/1/2015 ║ 1/20/2015 ║ ║ 1/20/2015 ║ 1/25/2015 ║ ╚════════════╩═══════════╝
My output has to be
╔════════════╦═══════════╦════════════╗ ║ start_date ║ end date ║ difference ║ ╠════════════╬═══════════╬════════════╣ ║ 1/1/2015 ║ 1/20/2015 ║ 33 ║ ║ 1/20/2015 ║ 1/25/2015 ║ 3 ║ ╚════════════╩═══════════╩════════════╝
So here I have to join table number two to table number one based on date and then calculate difference between the two values(that is corresponding to the date) in the column and then display it in one sql query.
Problem is I am not able to join all of them in one query and display them together.
This is what i have s far
select start_date, end_date, ((SELECT
table_one.value
FROM
table_one,
table_two
WHERE
table_one.date= table_two.end_date(+)
) - (
SELECT
table_one.value
FROM
table_one,
table_two
WHERE
table_one.date = table_two.start_date(+)
))from table_two,table_one where table_two.start_date(+)=table_one.date
I am getting ORA- 01427 with the above query. What am I doing wrong and how do I achieve the result?