Join multiple Tables by date

join;oracle

i got some problems on my approach to join a couple of tables by a date range. This are my tables:

RETURNS:

RETURN_DATE     RETURN_AMOUNT
02.05.14        2
02.05.14        3
02.05.14        1
02.05.14        7
02.05.14        5
02.05.14        2
02.05.14        1

and

ORDERS:

ORDER_DATE      ORDER_AMOUNT
02.05.14        50

As a first step, i created a date range and then tried to join these two tables to it. This is my SELECT statement:

SELECT
  DAT,
  RETURN_AMOUNT,
  ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM 
      all_objects

      WHERE 
      ROWNUM <= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) LEFT JOIN RETURNS ON RETURN_DATE = DAT
    LEFT JOIN ORDERS ON ORDER_DATE = DAT

    ORDER BY DAT;

This was the Result:

RESULT:

DAT         RETURN_AMOUNT   ORDER_AMOUNT
02.05.14    1               50
02.05.14    2               50
02.05.14    5               50
02.05.14    2               50
02.05.14    1               50
02.05.14    3               50
02.05.14    7               50

Thats a problem because when i GROUP BY DAT and then sum up the amounts, i got a wrong ORDER_AMOUNT for this day.

How can i achive, that ORDER_AMOUNT is only resulted once?

Thanks,
Stefan.

Best Answer

You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) 
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY 
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount) 
              FROM returns  r 
              WHERE r.dat = d.dat
            ), 0) 
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount) 
              FROM orders  o 
              WHERE o.dat = d.dat
            ), 0) 
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY 
  d.dat ;

Related Solutions

Sql-server – Why is this Full Outer Join not working

You should do an isnull on both policynumbers so that you can group properly.

Since it's an outer-join, there's the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Best Answer

Related Solutions

Sql-server – Why is this Full Outer Join not working

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Related Question