WITH a AS (
SELECT 'A' ty , 7 stime ,10 etime, 10 price FROM dual
UNION ALL
SELECT 'A' ty , 10 stime ,12 etime, 25 price FROM dual
UNION ALL
SELECT 'A' ty , 12 stime ,18 etime, 15 price FROM dual
UNION ALL
SELECT 'A' ty , 18 stime ,24 etime, 20 price FROM dual
UNION ALL
SELECT 'B' ty , 7 stime ,12 etime, 110 price FROM dual
UNION ALL
SELECT 'B' ty , 12 stime ,18 etime, 115 price FROM dual
UNION ALL
SELECT 'B' ty , 18 stime ,24 etime, 120 price FROM dual
)
SELECT CASE WHEN b.betime-a.stime > 0 THEN betime WHEN b.betime-a.stime <0 THEN a.stime END stime,CASE WHEN b.estime-a.etime < 0 THEN b.estime WHEN b.estime-a.etime >0 THEN a.etime END etime,a.price, LEAST(a.etime,b.estime) -greatest(a.stime,b.betime) hours,(LEAST(a.etime,b.estime) -greatest(a.stime,b.betime))*price, b.betime,b.estime FROM a a,
(SELECT c.stime,8 betime,19 estime ,d.etime FROM (SELECT max(a.stime) stime FROM a a WHERE a.ty='A' AND stime < 8 ) c , (SELECT MIN(a.etime) etime FROM a a WHERE a.ty='A' AND 19 < etime ) d) b
WHERE a.stime >= b.stime AND a.etime <= b.etime AND a.ty='A'