We have 2 very simple SELECT statements:
SELECT value from table where date between DATE1 and DATE2
SELECT value from table where date between DATE3 and DATE4
We need a way to write a single SQL statement which will get the difference of the "value" that is returned from these two SQL statements.
We tried the following but it was not successful:
SELECT value from table where date between DATE1 and DATE2
minus
SELECT value from table where date between DATE3 and DATE4
Any suggestion is highly appreciated.
Untested but should work:
SELECT
(SELECT value from table where date between DATE1 and DATE2) -
(SELECT value from table where date between DATE3 and DATE4)
FROM dual;
Assuming that your SELECT value
is guaranteed to return a single value
SELECT value FROM TBL WHERE date BETWEEN DATE1 and DATE2
AND value NOT IN (SELECT value FROM TBL WHERE date BETWEEN DATE3 AND DATE4)
If you think your inner queries can give multiple values, use below
SELECT
(SELECT sum(value) from table where date between DATE1 and DATE2) -
(SELECT sum(value) from table where date between DATE3 and DATE4) as answer
FROM dual;
Please try this:
SET SERVEROUTPUT ON
DECLARE
V_FIRST NUMBER := 0;
V_SECOND NUMBER := 0;
BEGIN
SELECT value into V_FIRST from table where rownum=1 and date between DATE1 and DATE2;
SELECT value into V_SECOND from table where rownum=1 and date between DATE3 and DATE4;
DBMS_OUTPUT.PUT_LINE (V_FIRST-V_SECOND);
END;
(
SELECT value from table where date between DATE1 and DATE2
minus
SELECT value from table where date between DATE3 and DATE4
)
union all
(
SELECT value from table where date between DATE3 and DATE4
minus
SELECT value from table where date between DATE1 and DATE2
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With