LEFT OUTER JOIN on same table returns 0 records

Question

I already searched on other posts for my problem but nothing worked.

I have a table with customers and turnover forecasts data for restaurants, both planned and realized values are in the same table (had to keep it that way from the previous DB).

I need to get planned forecasts and realized forecasts of the last year for a specific restaurant (site) and a specific activity (restaurant, take away, click n collect) and for a range of date (usually a full month).

My issue is when the planned forecasts are non-existing and the last year realized exists, the request returns nothing.

Here's my request :

SELECT 
    P.AFO_ACTIVITY_ID,
    P.AFO_SITE_ID,
    P.AFO_FORECAST_DATE,
    P.AFO_SERVICE_ID,
    P.AFO_PLANNED_CUSTOMERS,
    P.AFO_REALIZED_CUSTOMERS,
    P.AFO_GROUP_CUSTOMERS,
    P.AFO_PLANNED_TURNOVER,
    P.AFO_REALIZED_TURNOVER,
    P.AFO_GROUP_TURNOVER,
    P.AFO_NB_EMPLOYEES,
    P.AFO_TURNOVER_EMPLOYEES,
    P.AFO_ID,
    p2.AFO_REALIZED_CUSTOMERS AS REAL_CUSTOMERS_PREVIOUS_YEAR,
    p2.AFO_REALIZED_TURNOVER  AS REAL_TURNOVER_PREVIOUS_YEAR
FROM 
    ACT_FORECAST P
LEFT OUTER JOIN 
    act_forecast p2 ON p2.AFO_FORECAST_DATE = p.AFO_FORECAST_DATE - 364
                    AND p2.AFO_SITE_ID = p.AFO_SITE_ID
                    AND p2.AFO_ACTIVITY_ID = p.AFO_ACTIVITY_ID
                    AND p2.AFO_SERVICE_ID = p.AFO_SERVICE_ID
WHERE 
    P.AFO_SITE_ID = :siteid
    AND P.AFO_ACTIVITY_ID = :activityid
    AND P.AFO_FORECAST_DATE BETWEEN :startdate AND :enddate

If I play the request for February 2018 I have my data (2018 and 2017).

But if I play it for February 2019 I have nothing, it's ok for the planned part of the year because the forecast is not done yet but for the previous year part, I should have some data.

I tried with RIGHT OUTER JOIN too but nothing cames out.

Sample Data :

ActivityId SiteId ForecastDate ServiceId PlannedCustomers RealizedCustomers GroupCustomers PlannedTurnover RealizedTurnover GroupTurnover NbEmployeeMeal TurnoverEmployeeMeal ID      RealizedCustomersPreviousYear RealizedTurnoverPreviousYear
1          58     01/02/18     1         0                0                 0              0               11               0             0              11                   2953773 0                             39,85
1          58     01/02/18     3         345              398               0              3467,25         4052,01          0             0              0                    2953774 328                           3291,53
1          58     01/02/18     5         10               16                0              140             194,04           0             16             60,9                 2953775 7                             91,12
1          58     01/02/18     7         125              151               0              1200            1413,93          0             0              0                    2953776 112                           1063,89

Any ideas?

Regards

Answer 1

If I understand correctly, you can use a FULL OUTER JOIN:

SELECT 
    COALESCE(P.AFO_ACTIVITY_ID, P2.AFO_ACTIVITY_ID) as AFO_ACTIVITY_ID,
    COALESCE(P.AFO_SITE_ID, P2.AFO_SITE_ID) as AFO_SITE_ID, 
    P.AFO_FORECAST_DATE,
    P.AFO_SERVICE_ID,
    P.AFO_PLANNED_CUSTOMERS,
    P.AFO_REALIZED_CUSTOMERS,
    P.AFO_GROUP_CUSTOMERS,
    P.AFO_PLANNED_TURNOVER,
    P.AFO_REALIZED_TURNOVER,
    P.AFO_GROUP_TURNOVER,
    P.AFO_NB_EMPLOYEES,
    P.AFO_TURNOVER_EMPLOYEES,
    P.AFO_ID,
    p2.AFO_REALIZED_CUSTOMERS AS REAL_CUSTOMERS_PREVIOUS_YEAR,
    p2.AFO_REALIZED_TURNOVER  AS REAL_TURNOVER_PREVIOUS_YEAR
FROM (SELECT P.*
      FROM ACT_FORECAST P
      WHERE P.AFO_SITE_ID = :siteid AND
            P.AFO_ACTIVITY_ID = :activityid AND
            P.AFO_FORECAST_DATE BETWEEN :startdate AND :enddate
     ) P FULL JOIN
     (SELECT p2.*
      FROM act_forecast p2
      WHERE P2.AFO_SITE_ID = :siteid AND
            P2.AFO_ACTIVITY_ID = :activityid AND
            P2.AFO_FORECAST_DATE BETWEEN :startdate - 364 AND :enddate - 364
     ) p2
     ON p2.AFO_FORECAST_DATE = p.AFO_FORECAST_DATE - 364 AND
        p2.AFO_SITE_ID = p.AFO_SITE_ID AND
        p2.AFO_ACTIVITY_ID = p.AFO_ACTIVITY_ID AND
        p2.AFO_SERVICE_ID = p.AFO_SERVICE_ID

Answer 2

Your where clause is only find records in P in the range you specify. If there none, there are no source rows to left outer join from.

You could generate all possible dates in the period you are looking at, via an inline view or a CTE, and then look for records matching either sets of dates:

WITH CTE (this_year, last_year) AS (
  SELECT :startdate + level - 1, :startdate + level - 365
  FROM dual
  CONNECT BY level <= :enddate - :startdate + 1
)
SELECT 
    :activityid AS AFO_ACTIVITY_ID,
    :siteid AS AFO_SITE_ID,
    CTE.this_year AS AFO_FORECAST_DATE,
    P.AFO_SERVICE_ID,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_PLANNED_CUSTOMERS END) AS AFO_PLANNED_CUSTOMERS,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_REALIZED_CUSTOMERS END) AS AFO_REALIZED_CUSTOMERS,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_GROUP_CUSTOMERS END) AS AFO_GROUP_CUSTOMERS,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_PLANNED_TURNOVER END) AS AFO_PLANNED_TURNOVER,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_REALIZED_TURNOVER END) AS AFO_REALIZED_TURNOVER,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_GROUP_TURNOVER END) AS AFO_GROUP_TURNOVER,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_NB_EMPLOYEES END) AS AFO_NB_EMPLOYEES,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_TURNOVER_EMPLOYEES END) AS AFO_TURNOVER_EMPLOYEES,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = this_year
      THEN P.AFO_ID END) AS AFO_ID,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = last_year
      THEN P.AFO_REALIZED_CUSTOMERS END) AS REAL_CUSTOMERS_PREVIOUS_YEAR,
    MAX(CASE WHEN P.AFO_FORECAST_DATE = last_year
      THEN P.AFO_REALIZED_TURNOVER END) AS REAL_TURNOVER_PREVIOUS_YEAR
FROM
    CTE
JOIN
    ACT_FORECAST P
ON
    P.AFO_FORECAST_DATE = CTE.this_year
OR
    P.AFO_FORECAST_DATE = CTE.last_year
WHERE 
    P.AFO_SITE_ID = :siteid
    AND P.AFO_ACTIVITY_ID = :activityid
GROUP BY
    :activityid,
    :siteid,
    CTE.this_year,
    P.AFO_SERVICE_ID;

Rather than attempting to outer join (either way) it looks for the specific dates in both years, and does a kind of manual pivot to show the value from the relevant year in the right column. Notice the last two column expression use last_year, while the rest use this_year (except the ones being grouped on).

For SERVICE_ID you might want to coalesce to pick up last years value if there isn't one for this year. You could also coalesce the others to zero if you don't want to see nulls.

---

If you don't want to use a WITH clause then you can use an inline view instead:

SELECT
...
FROM
    (
        SELECT :startdate + level - 1 AS this_year,
               :startdate + level - 365 AS last_year
        FROM dual
        CONNECT BY level <= :enddate - :startdate + 1
    ) CTE
JOIN
    ACT_FORECAST P
...

though you might want a more descriptive name than CTE (with either version...)

---

With your sample data reworked as a CTE, with nulls for the values not shown in the question:

with act_forecast (afo_activity_id, afo_site_id, afo_forecast_date, afo_service_id,
  afo_planned_customers, afo_realized_customers, afo_group_customers,
  afo_planned_turnover, afo_realized_turnover, afo_group_turnover,
  afo_nb_employees, afo_turnover_employees, afo_id)
as (
            select 1, 58, date '2018-02-01', 1, 0, 0, 0, 0, 11, 0, 0, 11, 2953773 from dual
  union all select 1, 58, date '2017-02-02', 1, null, 0, null, null, 39.85, null, null, null, null from dual
  union all select 1, 58, date '2018-02-01', 3, 345, 398, 0, 3467.25, 4052.01, 0, 0, 0, 2953774 from dual
  union all select 1, 58, date '2017-02-02', 3, null, 328, null, null, 3291.53, null, null, null, null from dual
  union all select 1, 58, date '2018-02-01', 5, 10, 16, 0, 140, 194.04, 0, 16, 60.9, 2953775 from dual
  union all select 1, 58, date '2017-02-02', 5, null, 7, null, null, 91.12, null, null, null, null from dual
  union all select 1, 58, date '2018-02-01', 7, 125, 151, 0, 1200, 1413.93, 0, 0, 0, 2953776 from dual
  union all select 1, 58, date '2017-02-02', 7, null, 112, null, null, 1063.89, null, null, null, null from dual
)

then with a range in 2018 that gives:

AFO_ACTIVITY_ID AFO_SITE_ID AFO_FORECA AFO_SERVICE_ID AFO_PLANNED_CUSTOMERS AFO_REALIZED_CUSTOMERS AFO_GROUP_CUSTOMERS AFO_PLANNED_TURNOVER AFO_REALIZED_TURNOVER AFO_GROUP_TURNOVER AFO_NB_EMPLOYEES AFO_TURNOVER_EMPLOYEES     AFO_ID REAL_CUSTOMERS_PREVIOUS_YEAR REAL_TURNOVER_PREVIOUS_YEAR
--------------- ----------- ---------- -------------- --------------------- ---------------------- ------------------- -------------------- --------------------- ------------------ ---------------- ---------------------- ---------- ---------------------------- ---------------------------
              1          58 2018-02-01              3                   345                    398                   0              3467.25               4052.01                  0                0                      0    2953774                          328                     3291.53
              1          58 2018-02-01              7                   125                    151                   0                 1200               1413.93                  0                0                      0    2953776                          112                     1063.89
              1          58 2018-02-01              1                     0                      0                   0                    0                    11                  0                0                     11    2953773                            0                       39.85
              1          58 2018-02-01              5                    10                     16                   0                  140                194.04                  0               16                   60.9    2953775                            7                       91.12

and with a range in 2019 that gives:

AFO_ACTIVITY_ID AFO_SITE_ID AFO_FORECA AFO_SERVICE_ID AFO_PLANNED_CUSTOMERS AFO_REALIZED_CUSTOMERS AFO_GROUP_CUSTOMERS AFO_PLANNED_TURNOVER AFO_REALIZED_TURNOVER AFO_GROUP_TURNOVER AFO_NB_EMPLOYEES AFO_TURNOVER_EMPLOYEES     AFO_ID REAL_CUSTOMERS_PREVIOUS_YEAR REAL_TURNOVER_PREVIOUS_YEAR
--------------- ----------- ---------- -------------- --------------------- ---------------------- ------------------- -------------------- --------------------- ------------------ ---------------- ---------------------- ---------- ---------------------------- ---------------------------
              1          58 2019-01-31              3                                                                                                                                                                                                            398                     4052.01
              1          58 2019-01-31              5                                                                                                                                                                                                             16                      194.04
              1          58 2019-01-31              1                                                                                                                                                                                                              0                          11
              1          58 2019-01-31              7                                                                                                                                                                                                            151                     1413.93

Answer 3

Use somthing Like this below:

    SELECT P.AFO_ACTIVITY_ID,
    P.AFO_SITE_ID,
    P.AFO_FORECAST_DATE,
    P.AFO_SERVICE_ID,
    P.AFO_PLANNED_CUSTOMERS,
    P.AFO_REALIZED_CUSTOMERS,
    P.AFO_GROUP_CUSTOMERS,
    P.AFO_PLANNED_TURNOVER,
    P.AFO_REALIZED_TURNOVER,
    P.AFO_GROUP_TURNOVER,
    P.AFO_NB_EMPLOYEES,
    P.AFO_TURNOVER_EMPLOYEES,
    P.AFO_ID,
    p2.AFO_REALIZED_CUSTOMERS AS REAL_CUSTOMERS_PREVIOUS_YEAR,
    p2.AFO_REALIZED_TURNOVER  AS REAL_TURNOVER_PREVIOUS_YEAR
    FROM ACT_FORECAST P
    LEFT OUTER JOIN(SELECT DISTINCT p2.AFO_FORECAST_DATE 
        FROM ACT_FORECAST P2)
    ON p2.AFO_FORECAST_DATE = p.AFO_FORECAST_DATE - 364
                    AND p2.AFO_SITE_ID = p.AFO_SITE_ID
                    AND p2.AFO_ACTIVITY_ID = p.AFO_ACTIVITY_ID
                    AND p2.AFO_SERVICE_ID = p.AFO_SERVICE_ID
WHERE 
    P.AFO_SITE_ID = :siteid
    AND P.AFO_ACTIVITY_ID = :activityid
    AND P.AFO_FORECAST_DATE BETWEEN :startdate AND :enddate

Answer 4

I was astonished to see - 364 (my expertise might be less than yours) and expected:

p.AFO_FORECAST_DATE - INTERVAL 364 DAY

Maybe that was interpreted as (micro)seconds?

(The typical debugging is by leaving out a part of the conditions like on AFO_FORECAST_DATE.)