Interesting logic to be solved in teradata SQL

Question

Data and question

In the image the target is top calculate years since last DX. A year level we want to calculate thw number of years since we have found a DX. Eg There was a dx for id 1 in 2014 so in 2015,2016 and 2017 we are populating the value in incremental manner of year. we find one more DX in 2017 so in the following year we populate 1. the same cycle beging in next ID

The Idea is to implement this logic in Teradata SQL Can someone help how this could be done?

Your `date` certainly does not look like a "date". Please explain how years are calculated. — Gordon Linoff, Feb 25 '20 at 11:32
How do you calculate the years, calendar years or actual difference? E.g. when is a year over after 2012-12-13? 2013-12-13 or 2013-01-01? — dnoeth, Feb 25 '20 at 22:19

Gordon Linoff · Answer 1 · 2020-02-26T11:43:50.833

0

You can get the largest value for the most recent "DX" value using analytic functions:

select t.*,
       max(case when code = 'DX' then date end) over
           (partition by id
            order by date
            range between unbounded preceding and 1 preceding
           ) as prev_dx
from t;

I have no idea how to convert this to "years". The date format makes no sense to me.

edited Feb 26 '20 at 11:43

answered Feb 25 '20 at 11:34

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon, Your solution helped me resolve most of it. I am not able to achieve one case That is in Year 2017 there was a DX the present code resets to 0 but it should be counted to 3. Would there be anything that we could do?# – Nirvana Feb 25 '20 at 13:47
What if you drop "and current row" from the window? – Fred Feb 25 '20 at 17:02
@Fred . . . I think it works, perhaps without the `between`. – Gordon Linoff Feb 25 '20 at 18:52
I dropped both between and current row. Nothing changed. rows unbounded preceding --and current row – Nirvana Feb 26 '20 at 11:30
@Nirvana . . . I think `range between` does what you want. I modified the answer. – Gordon Linoff Feb 26 '20 at 11:44

Interesting logic to be solved in teradata SQL

1 Answers1