Hey,
I tried searching the forum for this, but I actually didn't even know what to search for, so I'm creating a new thread.
The SQL below displays a list of prices:
WITH T AS (
SELECT 1 AS ID, 'PERM' AS TYPE, 100 AS PRICE, SYSDATE + 1 AS START_DATE FROM DUAL UNION
SELECT 3 AS ID, 'TEMP' AS TYPE, 90 AS PRICE, SYSDATE + 2 AS START_DATE FROM DUAL UNION
SELECT 7 AS ID, 'TEMP' AS TYPE, 80 AS PRICE, SYSDATE + 3 AS START_DATE FROM DUAL UNION
SELECT 8 AS ID, 'PERM' AS TYPE, 75 AS PRICE, SYSDATE + 4 AS START_DATE FROM DUAL UNION
SELECT 16 AS ID, 'TEMP' AS TYPE, 70 AS PRICE, SYSDATE + 5 AS START_DATE FROM DUAL UNION
SELECT 20 AS ID, 'TEMP' AS TYPE, 60 AS PRICE, SYSDATE + 6 AS START_DATE FROM DUAL UNION
SELECT 34 AS ID, 'TEMP' AS TYPE, 50 AS PRICE, SYSDATE + 7 AS START_DATE FROM DUAL
)
SELECT T.ID
, T.TYPE
, T.PRICE
, TRUNC (T.START_DATE) AS START_DATE
, CASE
WHEN T.TYPE = 'PERM'
THEN T.ID
ELSE LAG (T.ID, 1, NULL) OVER (PARTITION BY NULL ORDER BY T.ID)
END AS BASE_ID
FROM T
ORDER BY T.START_DATE
The challenge is to produce this output:
ID TYPE PRICE BASE_ID
1 PERM 100 1
3 TEMP 90 1
7 TEMP 80 1
8 PERM 75 8
16 TEMP 70 8
20 TEMP 60 8
34 TEMP 50 8
What I want to achieve is to bring a column with the ID of the most recent PERM price for TEMP prices,
and It's own ID for PERM prices.
My attempt uses LAG to navigate back on the record set, but it uses 1 statically. If there was a way to come up with a number
for each TEMP price saying how far it was from the most recent PERM, then I could use that number instead of 1.
Something like:
ID TYPE PRICE DISTANCE_FROM_PREV_PERM
1 PERM 100 0
3 TEMP 90 1
7 TEMP 80 2
8 PERM 75 0
16 TEMP 70 1
20 TEMP 60 2
34 TEMP 50 3
Any help will be greatly appreciated.
Thanks.