Database

## Sequence Change based on CASE logic

I have following 3 columns (Order, Date, Price) in my table - Sequence I need to generate.
Based on Order_Date sorting (Ascending), if my Price drops, then the Sequence should change else remains unchanged.
Can we be able to write this in a single/multiple SELECT sql statement (with or without Recursive)?

 Order Order_Date Price Sequence 1 1/1/2010 100 1 2 1/2/2010 200 1 3 1/3/2010 300 1 4 1/4/2010 100 2 Price gets dropped from 300 to 100 - so sequence increased to 2 5 1/5/2010 500 2 Price gets increased from 100 to 500 - so sequence number remain unchanged 6 1/6/2010 200 3 Price gets dropped from 500 to 200 - so sequence increased to 3 7 1/7/2010 100 4 Price gets dropped from 200 to 100 - so sequence increased to 4 8 1/8/2010 600 4 Price gets increased from 100 to 600 - so sequence number remain unchanged 9 1/9/2010 500 5 Price gets dropped from 600 to 500 - so sequence increased to 5 10 1/10/2010 700 5 Price gets increased from 500 to 700 - so sequence number remain unchanged 11 1/11/2010 700 5 Price remain unchanged in 700 - so sequence number remain unchanged 12 1/12/2010 300 6 Price gets dropped from 700 to 300 - so sequence increased to 5

Accepted Solutions
Senior Apprentice

## Re: Sequence Change based on CASE logic

You need nested OLAP-functions, first add a 0/1 flag based on your logic and then sum it:

```SELECT ...
Sum(flag) Over (ORDER BY Order_col ROWS Unbounded Preceding)
FROM
(
SELECT ...
CASE WHEN Min(Price) Over (ORDER BY Order_col ROWS BETWEEN 1 Preceding AND 1 Preceding) <= Price THEN 0 ELSE 1 END AS flag
FROM tab
) AS dt```

1 ACCEPTED SOLUTION
Senior Apprentice

## Re: Sequence Change based on CASE logic

You need nested OLAP-functions, first add a 0/1 flag based on your logic and then sum it:

```SELECT ...
Sum(flag) Over (ORDER BY Order_col ROWS Unbounded Preceding)
FROM
(
SELECT ...
CASE WHEN Min(Price) Over (ORDER BY Order_col ROWS BETWEEN 1 Preceding AND 1 Preceding) <= Price THEN 0 ELSE 1 END AS flag
FROM tab
) AS dt```