user1029167 user1029167 - 1 month ago 4
SQL Question

Trying to create a Teradata view to aggregate how long rows of a specific ID have had a certain value

I have a test report table, that writes a row after each run of a test.

Let's say this is the data:

| main_id | status | date |
|---------|--------|---------|
| 123 | pass | Jan 1st |
| 123 | fail | Jan 2nd |
| 123 | fail | Jan 3rd |
| 123 | fail | Jan 4th |


I want to make a view that for each test, will list how long it has been failing.

Essentially, the corresponding row for the above data would look like this:

| main_id | days_failing |
|---------|--------------|
| 123 | 3 |


Using Teradata SQL, how could check each row in the source table, looking for the last success, and then sum up all the subsequent failures?

Edit: Note that there would be many different "main_id"s in the source table, I would need 1 row in the view for every unique failing test in the source table.

Thanks

Answer
select      main_id
           ,count (*) - 1   as days_failing

from       (select      main_id
                       ,"date"

            from        t

            qualify     "date" >= max (case status when 'pass' then "date" end) over (partition by main_id)
            ) t

group by    main_id

order by    main_id
;
Comments