CODEWITHSUNDEEP
sqloracle-databaseplsql
Zedan
Zedan

Reputation: 221

I want to join data from two tables but not going well this

Hi Dear I have a data of like this also on dbfiddle which is briefly you can seen what is data looks like I have 3 table T1, T2, T3 T1 have a date like this

01-01-2021 
02-01-2021 
03-01-2021 
04-01-2021 
05-01-2021 
06-01-2021 
07-01-2021 
08-01-2021 
09-01-2021 
10-01-2021 
…
10 rows of 31

And T2 have data like this

11  03-JAN-21   A
12  03-JAN-21   B
13  03-JAN-21   C
11  10-JAN-21   B
12  10-JAN-21   C
13  10-JAN-21   A
11  20-JAN-21   C
12  20-JAN-21   A
13  20-JAN-21   B

what I want is like this

11  03-JAN-21   A
12  03-JAN-21   B
13  03-JAN-21   C
11  04-JAN-21   A
12  04-JAN-21   B
13  04-JAN-21   C
11  05-JAN-21   A
12  05-JAN-21   B
13  05-JAN-21   C
continued...
11  10-JAN-21   B
12  10-JAN-21   C
13  10-JAN-21   A
11  11-JAN-21   B
12  11-JAN-21   C
13  11-JAN-21   A
continued ..
11  19-JAN-21   B
12  19-JAN-21   C
13  19-JAN-21   A
11  20-JAN-21   C
12  20-JAN-21   A
13  20-JAN-21   B
continued so on

I tried lot of way all type of joins showed on dbfiddle might be wrong what I want is just first 3 ids repeat till the next date when next date comes it will move to next and repeat in all dates same which it have pleassseeeeeee Help on this I am very very thank full to you

Upvotes: 1

Views: 59

Answers (1)

Littlefoot
Littlefoot

Reputation: 143033

Would this do? Read comments within code.

SQL> with
  2  types as
  3    -- distinct types
  4    (select distinct
  5       emp_type,
  6       dt_2,
  7       nvl(lead(dt_2, 3) over (order by dt_2), dt_2) next_dt_2,
  8       row_number() over (partition by dt_2 order by dt_2, emp_id, emp_type) rn
  9     from t2),
 10  ids as
 11    -- distinct IDs
 12    (select emp_id, row_number () over (order by emp_id) rn
 13     from (select distinct emp_id from t2)
 14    )
 15  -- final query
 16  select i.emp_id,
 17         nvl(a.dt, t.next_dt_2) dt,
 18         t.emp_type
 19  from ids i join types t on t.rn = i.rn
 20        left join t1    a on a.dt >= t.dt_2 and a.dt < t.next_dt_2
 21  order by 2, 1, 3;

which results in

    EMP_ID DT        EM
---------- --------- --
        11 03-jan-21 A     --> 3rd - A-B-C
        12 03-jan-21 B
        13 03-jan-21 C
        11 04-jan-21 A
        12 04-jan-21 B
        13 04-jan-21 C
        11 05-jan-21 A
        12 05-jan-21 B
        13 05-jan-21 C
        11 06-jan-21 A
        12 06-jan-21 B
        13 06-jan-21 C
        11 07-jan-21 A
        12 07-jan-21 B
        13 07-jan-21 C
        11 08-jan-21 A
        12 08-jan-21 B
        13 08-jan-21 C
        11 09-jan-21 A
        12 09-jan-21 B
        13 09-jan-21 C
        11 10-jan-21 B     --> 10th - switch to B-C-A
        12 10-jan-21 C
        13 10-jan-21 A
        11 11-jan-21 B
        12 11-jan-21 C
        13 11-jan-21 A
        11 12-jan-21 B
        12 12-jan-21 C
        13 12-jan-21 A
        11 13-jan-21 B
        12 13-jan-21 C
        13 13-jan-21 A
        11 14-jan-21 B
        12 14-jan-21 C
        13 14-jan-21 A
        11 15-jan-21 B
        12 15-jan-21 C
        13 15-jan-21 A
        11 16-jan-21 B
        12 16-jan-21 C
        13 16-jan-21 A
        11 17-jan-21 B
        12 17-jan-21 C
        13 17-jan-21 A
        11 18-jan-21 B
        12 18-jan-21 C
        13 18-jan-21 A
        11 19-jan-21 B
        12 19-jan-21 C
        13 19-jan-21 A
        11 20-jan-21 C     --> 20th - switch to C-A-B
        12 20-jan-21 A
        13 20-jan-21 B

54 rows selected.

SQL>

Upvotes: 2

Related Questions