May 24, 2011 SQL No Comments

Hi friends,

How to calculate difference between columns of current and previous/next row?

Let’s say, you want to monitor how often products are sold from your store.

i.e. 

You want difference between current and previous SELL_DATE then you can write query as follows:

SELECT TO_CHAR(SELL_DATE, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(LAG(SELL_DATE) OVER (ORDER BY SELL_DATE),
'DD-MON-YYYY HH24:MI:SS') AS SELL_DATE_PREV,
(SELL_DATE - LAG(SELL_DATE) OVER (ORDER BY SELL_DATE))
* 24 * 60 * 60 AS DIFF_IN_SECONDS
FROM PRODUCT_SALES
ORDER BY SELL_DATE DESC; 

More Info: Oracle-Base: Lag Lead Analytic Functions

Written by Sohilkumar Bhavsar
Cloud Architect at Zero Integration Ltd. 14+ years of experience with Oracle, Linux, Networking, Data Centre and Public Clouds. Played major role in development, implementation and management of SaaS CRM using AI and ML. Integrated 15+ Client CRM across United Kingdom and Australia.