Divide two records in teradata sql and print one record

Question

I need divide two records from a table in sql query I have a the next records in a table

Table1

Id    field1  field3
1     20      20161001  
1     35      20161101
2     20      20161001
2     30      20161101
5     19      20161001 
5     33      20161101

(Please notice that the dates in the example are 2016-OCT-01 and 2016-NOV-01)

I neeed a sql query that shuld next result:

Id   fieldnew
1    0.75
2    0.5
5    0.7368

For calculate the"fieldnew" the rule is: Id 1 example

(35 ÷ 20) -1 = 0.75

Field1 from field3 with date 20161101 ÷ field1 from field3 with date 20161001 - 1 of record with id 1

Could you help with thw query for obtain the result required please ????

Answer 1

Something like this.

with
--  start of test data (not part of the solution)
     table1 ( id, field1, field3 ) as (
       select 1, 20, 20161001 from dual union all 
       select 1, 35, 20161101 from dual union all 
       select 2, 20, 20161001 from dual union all 
       select 2, 30, 20161101 from dual union all 
       select 5, 19, 20161001 from dual union all 
       select 5, 33, 20161101 from dual
     ),
--  end of test data (not part of the solution)
     prep ( id, field1, lag_field1, field3 ) as (
       select id, field1, lag(field1) over (partition by id order by field3), field3
       from   table1
       where  field3 in (20161001, 20161101)
     )
select id, round(field1 / lag_field1, 4) - 1 as fieldnew
from   prep
where  field3 = 20161101
;

ID   FIELDNEW
--   --------
 1       0.75
 2        0.5
 5     0.7368

Answer 2

I think the following solves the problem:

select id,
       (max(case when field3 = '20161101' then field1 end) /
        max(case when field3 = '20161001' then field1 end)
       ) - 1 as fieldnew
from table1 t1
group by id;

This is using conditional aggregation to get the values.

Answer 3

Oracle

select      id
           ,round
            (
                            min (field1) keep (dense_rank last  order by field3)
                /   nullif (min (field1) keep (dense_rank first order by field3),0)
                -   1   
               ,4
            )      as fieldnew

from        mytable

where       field3 in 
            (
                add_months (trunc (current_date,'mm'),-2)  
               ,add_months (trunc (current_date,'mm'),-1)
            ) 

group by    id
;

or

select      id
           ,round
            (
                            min (case when field3 = add_months (trunc (current_date,'mm'),-1) then field1 end)
                /   nullif (min (case when field3 = add_months (trunc (current_date,'mm'),-2) then field1 end),0)
                -   1   
               ,4
            )      as fieldnew

from        mytable

where       field3 in 
            (
                add_months (trunc (current_date,'mm'),-2)  
               ,add_months (trunc (current_date,'mm'),-1)
            ) 

group by    id
;

---

+----+----------+
| ID | FIELDNEW |
+----+----------+
| 1  | 0.75     |
+----+----------+
| 2  | 0.5      |
+----+----------+
| 5  | 0.7368   |
+----+----------+

Teradata

select          id

               ,1.0000

            *   last_value (field1) over
                (
                    partition by    id
                    order by        field3
                    rows between    unbounded preceding 
                                and unbounded following
                )

            /   nullifzero (field1)

            -   1   as fieldnew

from        mytable

where       field3 = td_month_begin (current_date) - interval '2' month
         or field3 = td_month_begin (current_date) - interval '1' month 

qualify     field3 = min (field3) over (partition by id)
;

or

select          id

               ,1.0000

            *   min (field1) over
                (
                    partition by    id
                    order by        field3
                    rows between    1 following
                                and 1 following
                )

            /   nullifzero (field1)

            -   1   as fieldnew

where       field3 = td_month_begin (current_date) - interval '2' month
         or field3 = td_month_begin (current_date) - interval '1' month 

from        mytable

qualify     field3 = min (field3) over (partition by id)
;

---

+----+----------+
| Id | fieldnew |
+----+----------+
| 1  | 0.7500   |
+----+----------+
| 2  | 0.5000   |
+----+----------+
| 5  | 0.7368   |
+----+----------+