SQL Difference between using two dates

My database has a table called 'clientordermas'. Two columns of that table are as follows.

execid and filledqty.

Three records of those two fields are as follows.

E02011/03/12-05:57_24384 : 1000

E02011/03/12-05:57_24384 : 800

E02011/03/09-05:57_24384 : 600

What i need to do is get the filledqty diffrence btween latest date and 1 before latest date which is 400(1000-400).

I have extracted the date from the execid as follows:

(SUBSTR (execid, 3, 10)

I tried so hard but but I was unable to write the开发者_Python百科 sql query to get 400. Can someone please help me to do this???

P.S I need to select maximum filled quantity from the same date. That is 1000 not, 800.

---

You can use window functions to access "nearby" rows, so if you first clean up the data in a subquery and then use window functions to access the next row, you should get the right results. But unless you have an index on substr(execid, 3, 10), this is going to be be slow.

WITH datevalues AS
(
  SELECT max(filledqty) maxfilledqty, substr(execid, 3, 10) execiddate
  FROM clientordermas
  GROUP BY substr(execid, 3, 10)
)
SELECT
  execiddate,
  maxfilledqty -
  last_value(maxfilledqty) over(ORDER BY execiddate DESC ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
FROM datevalues
ORDER BY execiddate DESC;

---

WITH maxqtys AS (
SELECT substr(a.execid,3,10) AS date, MAX(a.filledqty) AS maxqty
FROM clientordermas a
GROUP BY substr(a.execid,3,10)
)
SELECT a.maxqty-b.maxqty
FROM maxqtys a, maxqtys b
WHERE a.date <> b.date AND ROWNUM=1
ORDER BY a.date DESC, b.date DESC

This first creates a subquery (maxqty) which contains the max filledqty for each unique date, then cross join this subquery to itself, excluding the same rows. This results in a table containing pairs of dates (excluding the same dates).

Sort these pairs by date descending, and the top row till contain the last and 2nd-to-last date, with the appropriate quantities.