Sequence Change based on CASE logic

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)?

 

OrderOrder_DatePriceSequence 
11/1/20101001 
21/2/20102001 
31/3/20103001 
41/4/20101002Price gets dropped from 300 to 100 - so sequence increased to 2
51/5/20105002Price gets increased from 100 to 500 - so sequence number remain unchanged
61/6/20102003Price gets dropped from 500 to 200 - so sequence increased to 3
71/7/20101004Price gets dropped from 200 to 100 - so sequence increased to 4
81/8/20106004Price gets increased from 100 to 600 - so sequence number remain unchanged
91/9/20105005Price gets dropped from 600 to 500 - so sequence increased to 5
101/10/20107005Price gets increased from 500 to 700 - so sequence number remain unchanged
111/11/20107005Price remain unchanged in 700 - so sequence number remain unchanged
121/12/20103006Price 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
1 REPLY
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