SQL- convert wide to long table with 3 special rules-CodePudding

I have the following table:

Individual   Organisation   Time1        Time2        T_diff   Time1_original
111          2              01-04-2020   01-05-2020   1        01-04-2020
111          2              01-05-2020   01-04-2021   11       01-04-2020
122          2              01-04-2020   01-01-2021   9        01-04-2020
122          3              01-01-2021                         01-01-2021

I would like to convert it to the following one:

Individual      Organisation      Times      
111             2                 01-04-2020 (Time1-line1)
111             2                 01-04-2021 (Time2-line2)
122             2                 01-04-2020 (Time1)
122             2                 01-01-2021 (Time2)
122             3                 01-01-2021 (Time1)

3 conditions:

For each Individual-Organisation,

If (T_diff <= 3 OR T_diff IS NULL) AND Time1 EQUAL TO Time1_original bring Time1

If T_diff > 3 & Time1 NOT EQUAL TO Time1_original bring Time2

If T_diff > 3 & Time1 EQUAL TO Time1_original bring Time1 & Time2

How can I do this in SQL?

CodePudding user response：

Your requirements involve some pivoting of columns into rows.

Your column names are a little misleading having dates not times; Ideally you would specify dates as YYYYMMDD, it looks like you are using DD-MM-YYYY.

A lateral join makes this easy, in SQL Server this is done using apply().

You've also edited your sample data after posting your question, I worked with the data you originally posted.

with timeywimey as (
    select * 
    from t
    cross apply(values
      (case 
        when (t_diff <= 3 or t_diff is null) and time1 = time1_original then time1 
        else 
          case when t_diff > 3 then
            iif(time1 = time1_original, time1, time2)
          end 
        end
        ),
        (case when t_diff > 3 and time1 = time1_original then time2 end)
    )v(Times)
)
select individual, organisation, times 
from timeywimey 
where Times is not null;

See Demo DB<>Fiddle

CodePudding user response：

Adding another answer as it's easy to convert my other answer to a more ANSI-compliant version using a union. This is much more likely to work in HiveQL, I would think.

with timey as (
  select *, 
    case 
      when (T_diff <= 3 or T_diff is null) and Time1 = Time1_original then Time1 
      else 
        case when T_diff > 3 then
          case when Time1 = Time1_original then Time1 else Time2 end
        end 
      end as Times
    from t
  union all
  select *, 
    case when T_diff > 3 and Time1 = Time1_original then Time2 end as Times
  from t
)
select Individual, Organisation, Times
from timey
where Times is not null
order by Individual, Times;

DB FIDDLE