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
– Gordon Linoff, Commented Feb 25, 2020 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
– dnoeth, Commented Feb 25, 2020 at 22:19

Gordon Linoff · Accepted Answer · 2020-02-26 11:43:50Z

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, 2020 at 11:43

answered Feb 25, 2020 at 11:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nirvana Over a year ago

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

Fred Over a year ago

What if you drop "and current row" from the window?

Gordon Linoff Over a year ago

@Fred . . . I think it works, perhaps without the between.

Nirvana Over a year ago

I dropped both between and current row. Nothing changed. rows unbounded preceding --and current row

Gordon Linoff Over a year ago

@Nirvana . . . I think range between does what you want. I modified the answer.

Collectives™ on Stack Overflow

Interesting logic to be solved in teradata SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related