We have table SAMPLE in the Sybase database. The data is as follows:
We are trying to calculate the difference of each REP value and its previous one and display values in the original order of the source table. Below is the desired result:
In the original order, calculate the difference between REP on the current date and REP on the previous date for the same SECURITY_ID.
SQL written in Sybase:
SELECT SECURITY_ID, DATE, REP, REP - PREV_REP AS DIFF
FROM (
SELECT T1.SECURITY_ID, T1.DATE, T1.REP
, COALESCE(T2.REP, 0) AS PREV_REP
FROM SAMPLE T1
LEFT JOIN SAMPLE T2
ON T1.SECURITY_ID = T2.SECURITY_ID
AND T2.DATE = T1.DATE - 1
)
ORDER BY REP;
The intuitive solution is simple. For records with same SECURITY_ID, subtract REP value in the previous records (with the previous date) from the current REP value. Since SQL is based on unordered sets, it needs to turn to window functions to achieve this. Coding will be complicated. For this task, the worst thing is that Sybase does not support window functions. We need to perform a self-join and then calculate the difference, generating even more complicated SQL.
Yet it is simple to achieve the algorithm in the open-source esProc SPL:
SPL gives a direct support for ordered sets, and is convenient for achieving calculations between neighboring values/rows/records.
Top comments (0)