CODEWITHSUNDEEP
sqlhana
user1375218
user1375218

Reputation: 141

SQL script to find previous value, not necessarily previous row

is there a way in SQL to find a previous value, not necessarily in the previous row, within the same SELECT statement?

See picture below. I'd like to add another column, ELAPSED, that calculates the time difference between TIMERSTART, but only when DEVICEID is the same, and I_TYPE is viewDisplayed. e.g. subtract 1 from 2, store difference in 3, store 0 in 4 because i_type is not viewDisplayed, subtract 2 from 5, store difference in 6, and so on.

It has to be a statement, I can't use a stored procedure in this case.

SELECT DEVICEID, I_TYPE, TIMERSTART,
 O AS ELAPSED -- CASE WHEN <CONDITION> THEN TIMEDIFF() ELSE 0 END AS ELAPSED
FROM CLIENT_USAGE
ORDER BY TIMERSTART ASC

I'm using SAP HANA DB, but it works pretty much like the latest version of MS-SQL. So, if you know how to make it work in SQL, I can make it work in HANA.

enter image description here

Upvotes: 0

Views: 1907

Answers (2)

michael.moyer
michael.moyer

Reputation: 98

You can make a subquery to find the last time entered previous to the row in question.

select deviceid, i_type, timerstart, (timerstart - timerlast) as elapsed.
from CLIENT_USAGE CU
join ( select top 1 timerstart as timerlast
       from CLIENT_USAGE C 
       where (C.i_type = CU.i_type) and 
         (C.deviceid = CU.deviceid) and (C.timerstart < CU.timerstart)
       order by C.timerstart desc 
     ) as temp1
on temp1.i_type = CU.i_type
order by timerstart asc

This is a rough sketch of what the sql should look like I do not know what your primary key is on this table if it is i_type or i_type and deviceid. But this should help with how to atleast calculate the field. I do not think it would be necessary to store the value unless this table is very large or the hardware being used is very slow. It can be calculated rather easily each time this query is run.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

SAP HANA supports window functions:

select DEVICEID,
       TIMERSTART, 
       lag(TIMERSTART) over (partition by DEVICEID order by TIMERSTART) as previous_start
from CLIENT_USAGE

Then you can wrap this in parentheses and manipulate the data to your hearts' content

Upvotes: 0

Related Questions