CODEWITHSUNDEEP
sqloracle-database
AbdulRaheem
AbdulRaheem

Reputation: 115

how to find time difference between two tables column?

i have two tables having same column name and find the difference between them ?

class_time (column name) from class_time(parent table)

0200AM
0230AM
0300AM
0330AM
0400AM
0430AM
0500AM
0530AM

class_time (column name) from class_period(child table)

0330AM
0200AM:0230AM:0300AM

the difference between them as i want will be as

0400AM
0430AM
0500AM
0530AM

i have tried this query but this query only subtract 0330AM because 0330AM without colon separated

Select main.Class_time
from (
Select class_time from class_time where class_uid=:P216_CLASS_UID
)main
WHERE NOT EXISTS
(
select distinct  trim(regexp_substr(class_time,'[^:]+', 1, level) ) class_time, level
  from class_period where class_uid=:P216_CLASS_UID 
  and class_time=main.Class_time
   connect by regexp_substr(class_time, '[^:]+', 1, level) is not null
);

Upvotes: 0

Views: 109

Answers (3)

Fact
Fact

Reputation: 2460

If I understood your question then you are looking at minus

With a as 
(Select '0200AM' col From dual union all
 Select '0230AM' From dual union all
 Select '0300AM' From dual union all
 Select '0330AM' From dual union all
 Select '0400AM' From dual union all
 Select '0430AM' From dual union all
 Select '0500AM' From dual union all
 Select '0530AM' From dual 
)
, b as 
 (Select '0330AM' col From dual union all
  Select '0200AM' From dual union all
  Select '0230AM' From dual union all
  Select '0300AM' From dual
 )
Select * from a
minus 
Select * from b

Output

 COL   
------
0400AM
0430AM
0500AM
0530AM

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

By seeing the sample value and your attempt, i assume the class_time(Column) is of VARCHAR datatype. In that case, you can achieve this using NOT IN. Check the below query.

SELECT * FROM parent_table 
WHERE  class_time NOT IN (SELECT Regexp_substr(class_time, '[^:]+', 1, LEVEL) AS class_time 
                          FROM   child_table 
                          CONNECT BY Regexp_substr(class_time, '[^:]+', 1, LEVEL) IS NOT NULL) 
ORDER  BY class_time;

CHECK DEMO HERE

Upvotes: 1

prashant
prashant

Reputation: 966

You can find the difference in above case using following two steps:

1) create a stored procedure to explode and store colon (:) separated records in a temp table:

DELIMITER |

CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)    BEGIN
  DROP TABLE IF EXISTS temp_explode;
  CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));
  SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
  PREPARE myStmt FROM @sql;
  EXECUTE myStmt;
END |

DELIMITER ;

2) then select exploded class times from child table: 

SET @str  = (SELECT GROUP_CONCAT(class_time SEPARATOR ':') FROM class_period); 
SET @delim = ":"; 

CALL explode(@delim,@str);
SELECT word FROM temp_explode;

Now you can replace class_period with temp_explode and class_period.class_time with temp_explode.word in your query

Upvotes: 0

Related Questions