0

Original Table X as :

ID START_DATE END_DATE GRP
001 2017-10-15 2018-10-21 1
001 2017-10-15 2019-10-21 0
001 2020-05-18 2021-05-03 1 
select ID, start_date, end_date,
sum(grp) over(partition by ID order by start_date, end_date) as island
from X
```


In Teradata the result is:


| ID       | START_DATE   | END_DATE | island |
| -------- | ------------ | -------- |------- |
| 001      | 2017-10-15   |2018-10-21|  2     |
| 001      | 2017-10-15   |2019-10-21|  2     |
| 001      | 2020-05-18   |2021-05-03|  2     |


In Oracle and MySQL the results are: 
| ID       | START_DATE   | END_DATE | island |
| -------- | ------------ | -------- |------- |
| 001      | 2017-10-15   |2018-10-21|  1     |
| 001      | 2017-10-15   |2019-10-21|  1     |
| 001      | 2020-05-18   |2021-05-03|  2     |

Any suggestions to fix the Teradata result? Many thanks !!
Yumeng Xu
  • 179
  • 1
  • 2
  • 11
  • The problem is in Teradata the order by function is not properly executed as in other SQL – Yumeng Xu Mar 15 '22 at 00:23
  • 1
    Are you looking for a running sum? We may be getting into semantics here, but I would say the Teradata result is exactly what you asked it to do. Try this: `sum(grp) over(partition by ID order by start_date, end_date ROWS UNBOUNDED PRECEDING) as island` – Andrew Mar 15 '22 at 02:23
  • 2
    Teradate defauls to `rows between unbounded preceding and unbounded following` while Standard SQL uses a default `RANGE unbounded preceding` when ORDER BY is used. – dnoeth Mar 15 '22 at 08:09
  • Thank you guys, the problem gets solved! – Yumeng Xu Mar 15 '22 at 14:29

0 Answers0