sql - Get result from another query for each row without relation - Stack Overflow

I have this 2 tables:

Table 1 has a STARTTIME and ENDTIME representing the interval of time Table 2 has the same with an extra param COUNT. However Table 2 has a lot smaller intervals.

A visual representation of how the intervals could go:

Table 1	Table 2
row1	row1
row1	row2
row1	row3
row2	row4
row2	row5
row2	row6
row2	row7
row3	row8
row3	row9

I have this 2 tables:

Table 1 has a STARTTIME and ENDTIME representing the interval of time Table 2 has the same with an extra param COUNT. However Table 2 has a lot smaller intervals.

A visual representation of how the intervals could go:

Table 1	Table 2
row1	row1
row1	row2
row1	row3
row2	row4
row2	row5
row2	row6
row2	row7
row3	row8
row3	row9

I need a query that given a row_id of the table 1 gives me the sum of COUNT on table 2 corresponding to the interval of times

Table 1	Table 2
t_start	t_start to t_end
	t_start to t_end
t_end	t_start to t_end

What I did:

SELECT 
    STARTTIME,
    ENDTIME
FROM TABLE1
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND STARTTIME < :ENDTIME
ORDER BY STARTTIME DESC

And for each row that the query above I need this query:

SELECT
    SUM(COUNT) AS SUM_TOTAL
FROM TABLE2
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND ENDTIME <= :ENDTIME

In this second one I use the STARTTIME and ENDTIME from the rows of the first query.

Data example from Table2:

STARTTIME	ENDTIME	COUNT

Here is a visual representation of what I need:

For each state on the table1 (upper one) I need the sum of the value COUNT on the table2 (down one) from that specific period.

I have tried this unsuccessfully:

SELECT 
    STARTTIME,
    ENDTIME,
    MAX(
    SELECT SUM(DIFVALUE)
    FROM TABLE2
    WHERE ROOM_ID = :ROOM_ID
        AND :STARTTIME < ENDTIME
        AND ENDTIME <= :ENDTIME
    )
FROM TABLE1
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND STARTTIME < :ENDTIME
ORDER BY STARTTIME DESC

• sql
• oracle-database

Share Improve this question Follow edited Jan 29 at 14:47 Danielps1818 asked Jan 29 at 12:00 Danielps1818Danielps1818 1311 silver badge88 bronze badges 3

• 2 Please add proper table description , some data example and expected result. A fiddle with data example would be great. Add your attempt as well – Ergest Basha Commented Jan 29 at 12:05
• @ErgestBasha ok, give me a bit – Danielps1818 Commented Jan 29 at 12:06
• Please edit the question and provide a minimal reproducible example with the: CREATE TABLE and INSERT statements for your sample data in both tables (in a format we can copy-paste and execute - not as images); an English description of the logic that you want to implement (including edges cases where a table2 range overlaps two ranges in table1, if such a thing can occur); and the expected output for that sample data (again, as text, not images). – MT0 Commented Jan 29 at 14:55

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Join the two tables, something like:

SELECT t1.id,
       t1.starttime,
       t1.endtime,
       SUM(t2.count) AS total
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON (    t1.id = t2.id
           AND t1.starttime < t2.endtime
           AND t1.endtime > t2.starttime )
WHERE  t1.id        = :ID
AND    t1.endtime   > :STARTTIME
AND    t1.starttime < :ENDTIME
ORDER BY t1.starttime DESC

Note: Untested as you have not provided a minimal representative example with the DDL/DML statements for your tables or sample data.

Solution:

SELECT 
    TT.STARTTIME,
    MAX(TT.ENDTIME) AS ENDTIME,
    MAX(
        (
            SELECT SUM(CT.DIFVALUE)
            FROM DTIM_COUNTER_TABLE CT
            WHERE CT.ROOM_ID = :ROOM_ID
              AND (
                  (TT.STARTTIME < CT.ENDTIME AND :STARTTIME < CT.ENDTIME)
                  AND CT.ENDTIME <= TT.ENDTIME
              )
        )
    ) AS SUMTOTAL
FROM 
    DTIM_TIME_TABLE TT
WHERE 
    TT.ROOM_ID = :ROOM_ID
    AND :STARTTIME < TT.ENDTIME
    AND TT.STARTTIME < :ENDTIME
GROUP BY 
    TT.STARTTIME
ORDER BY 
    TT.STARTTIME DESC;

But I'm going to use a band join to improve efficiency.