Oracle DENSE_RANK()

Question

I'm trying to use the DENSE_RANK() function in Oracle as a way of attaching an arbitrary "primary key" onto unique groupings of data. My dataset is a list of transactions, performed against different "ASSETS", at different "LOCATIONS" over the past several days. The dataset is pre-ordered by ASSET ASC, DATE ASC as seen below.

ASSET       LOCATION        DATE
A           LOC_A       9/1/2017
A          LOC_A        9/1/2017
A          LOC_B        9/5/2017
A          LOC_B        9/7/2017
A          LOC_A        9/11/2017
B          LOC_A        8/22/2017
B          LOC_C        8/25/2017
B          LOC_C        8/26/2017
B          LOC_A        9/2/2017
B          LOC_A        9/3/2017

When I use DENSE_RANK() OVER (ORDER BY ASSET, LOCATION), I get the following output:

ASSET       LOCATION        DATE        ACTUAL
A          LOC_A        9/1/2017        1
A          LOC_A        9/1/2017        1
A          LOC_B        9/5/2017        2
A          LOC_B        9/7/2017        2
A          LOC_A        9/11/2017       1
B          LOC_A        8/22/2017       3
B          LOC_C        8/25/2017       4
B          LOC_C        8/26/2017       4
B          LOC_A        9/2/2017        3
B          LOC_A        9/3/2017        3

However, I'm trying to figure out a way to get the value in the "EXPECTED" value shown here:

ASSET       LOCATION        DATE        ACTUAL      EXPECTED
A          LOC_A        9/1/2017        1          1
A          LOC_A        9/1/2017        1          1
A          LOC_B        9/5/2017        2          2
A          LOC_B        9/7/2017        2          2
A          LOC_A        9/11/2017       1          3
B          LOC_A        8/22/2017       3          4
B          LOC_C        8/25/2017       4          5
B          LOC_C        8/26/2017       4          5
B          LOC_A        9/2/2017        3          6
B          LOC_A        9/3/2017        3          6

Any help in working towards this would be greatly appreciated.

---

My SQL is

SELECT ASSET, LOCATION, TXNDATE, 
       DENSE_RANK() OVER (ORDER BY ASSET, LOCATION) AS DENSERANK 
FROM TEMPTABLE 
ORDER BY ASSET, TXNDATE 

and yes, I am getting the "ACTUAL" result shown above.

Answer 1

You are looking for changes in asset and location. One method uses lag() and a cumulative sum:

SELECT ASSET, LOCATION, TXNDATE, 
       SUM(CASE WHEN Asset = prev_asset AND location = prev_location THEN 0
                ELSE 1
           END) OVER (ORDER BY TXNDATE) AS DENSERANK 
FROM (SELECT tt.*,
             LAG(ASSET) OVER (ORDER BY TXNDATE) as prev_asset,
             LAG(LOCATION) OVER (ORDER BY TXNDATE ) as prev_location
      FROM TEMPTABLE 
     ) tt
ORDER BY ASSET, TXNDATE;

Oracle defaults to RANGE BETWEEN rather than ROWS BETWEEN. This is the behavior you want because you have rows with the same date and they should all be treated the same.