Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
08:37 PM

12-08-2016
08:37 PM

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 |

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
11:42 PM

12-08-2016
11:42 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-08-2016
11:42 PM

12-08-2016
11:42 PM

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.