CODEWITHSUNDEEP
sqloracle-database
Siva
Siva

Reputation: 9101

Fetch sub query data inside a select statement using case

I have a data in below format

   ID  name   order    start Time                       end time
    2   null    1   30-SEP-19 11.13.39.000000000 PM  30-SEP-19 11.15.21.000000000 PM
    5   null    2   30-SEP-19 11.15.21.000000000 PM  30-SEP-19 11.19.25.000000000 PM
    2   null    4   30-SEP-19 11.20.08.000000000 PM  30-SEP-19 11.20.59.000000000 PM
    5   null    5   30-SEP-19 11.21.00.000000000 PM  30-SEP-19 11.25.02.000000000 PM

First row is the ID and third row is the order

My requirement is when control is at first row then for second column (name) I need to write a separate query where I want to select a record from a table where data is less than end time of first row and it should continue till all rows are completed.

I tried all approaches like writing sub query for second column but every attempt failed.

Edit

Table 2:

Name1   30-SEP-19 11.13.39.000000000 PM
name2   30-SEP-19 11.16.39.000000000 PM

If you see above table I need to query this table with the end time in table1, so final result would be

   ID  name   order    start Time                       end time
    2   name1   1   30-SEP-19 11.13.39.000000000 PM  30-SEP-19 11.15.21.000000000 PM //since name1 record end time is less than record 1 end time
    5   null    2   30-SEP-19 11.15.21.000000000 PM  30-SEP-19 11.19.25.000000000 PM
    2   null    4   30-SEP-19 11.20.08.000000000 PM  30-SEP-19 11.20.59.000000000 PM
    5   null    5   30-SEP-19 11.21.00.000000000 PM  30-SEP-19 11.25.02.000000000 PM

Now for second record name filed should be end time >= record1 endtime and end time < record2 end time

Edit

Table2:

ID  Name           Start Time                        End Time
2   780     30-SEP-19 11.13.39.000000000 PM 30-SEP-19 11.15.21.000000000 PM
5   782     30-SEP-19 11.15.21.000000000 PM 30-SEP-19 11.19.25.000000000 PM
2   786     30-SEP-19 11.20.08.000000000 PM 30-SEP-19 11.20.59.000000000 PM
5   788     30-SEP-19 11.21.00.000000000 PM 30-SEP-19 11.25.02.000000000 PM

Order of name is not correct, I was able to see correct number of records with wrong order.

Name column data in first record of table 2 should be added to first record of table 1 and so on, but this is not in correct order with the solution provided. Also end of both the table2 records can be same as table 1 and less than table 1 but can't be greater than table 1.

Edit

Table1:

   ID  Type Order      start time                          end time
    2   2   1      30-SEP-19 11.13.39.000000000 PM  30-SEP-19 11.15.21.000000000 PM
    5   2   2      30-SEP-19 11.15.21.000000000 PM  30-SEP-19 11.19.25.000000000 PM
    2   2   4      30-SEP-19 11.20.08.000000000 PM  30-SEP-19 11.20.59.000000000 PM
    5   2   5      30-SEP-19 11.21.00.000000000 PM  30-SEP-19 11.25.02.000000000 PM

Table 2:

ID  Name     start time                               end time
2   780  30-SEP-19 11.13.39.000000000 PM    30-SEP-19 11.15.21.000000000 PM
5   782  30-SEP-19 11.15.21.000000000 PM    30-SEP-19 11.19.25.000000000 PM
2   786  30-SEP-19 11.20.08.000000000 PM    30-SEP-19 11.20.59.000000000 PM
5   788  30-SEP-19 11.21.00.000000000 PM    30-SEP-19 11.25.02.000000000 PM

Now final result would be

ID  Type Order    Name          start time                          end time
 2    2    1      780   30-SEP-19 11.13.39.000000000 PM 30-SEP-19 11.15.21.000000000 PM
 5    2    2      782   30-SEP-19 11.15.21.000000000 PM 30-SEP-19 11.19.25.000000000 PM
 2    2    4      786   30-SEP-19 11.20.08.000000000 PM 30-SEP-19 11.20.59.000000000 PM
 5    2    5      788   30-SEP-19 11.21.00.000000000 PM 30-SEP-19 11.25.02.000000000 PM

Name column value in first row of table2 should be added to table1, till last row. Here end time is the key and I can't use inner join on end time because it can be same for table 1 and table 2 or table 2 end time can be less than table 1 but not greater than.

Edit

ID  Type Order    Name          start time                          end time
 2    2    4      780   30-SEP-19 11.20.08.000000000 PM 30-SEP-19 11.20.59.000000000 PM
 5    2    5      782   30-SEP-19 11.21.00.000000000 PM 30-SEP-19 11.25.02.000000000 PM

Upvotes: 1

Views: 128

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You may try below query - 

SELECT T1.ID, T2.name, T1.order, T1.start_Time, MAX(T1.end_time)
FROM TABLE_1 T1
JOIN TABLE_2 T2 ON T1.end_time >= T2.end_time
                AND T1.ID = T2.ID
GROUP BY T1.ID, T2.name, T1.order, T1.start_Time
ORDER BY T1.order

Alternatively, If you do not have any primary key in your table and is pretty sure that the order would always be deterministic by the end_time in both the table, You can generate a primary key and then join based on that column -

SELECT T1.ID, T2.name, T1.order, T1.start_Time, MAX(T1.end_time)
FROM (select *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY end_time) RN
      FROM TABLE_1)  T1
JOIN (select *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY end_time)
      FROM TABLE_2) T2 ON T1.ID = T2.ID
                       AND T1.RN = T2.RN
GROUP BY T1.ID, T2.name, T1.order, T1.start_Time
ORDER BY T1.order

Upvotes: 2

Related Questions