CODEWITHSUNDEEP
sqloracle-databasedatetime
Nikolas Leontiou
Nikolas Leontiou

Reputation: 25

Get max date from another table based on date of current row

I have two tables, CALL_DATA and MASTER_DATA.

Master data has daily data of a customer and CALL_DATA has some calls of customer along with some other data. I want to get for every customer and for every daily row of him in MASTER_DATA the row with the most recent date (maximum date till now) from CALL_DATA.

I have come up with this:

SELECT MAX(CALL_DATA.CALL_DATE) OVER (PARTITION BY MASTER_DATA.CUSTOMER_ID 
                                          ORDER BY MASTER_DATA.DAILY_START_DATE)
        AS max_date_agg
     , MASTER_DATA.*
     , CALL_DATA.*
  FROM MASTER_DATA, CALL_DATA
 WHERE CALL_DATA.CALL_DAT <= MASTER_DATA.DAILY_START_DATE 
   AND CALL_DATA.CUSTOMER_ID = MASTER_DATA.CUSTOMER_ID;

But this returns me more than one live for every daily start date. In fact it returns as many rows as there are inside CALL_DATA table up until the DAILY_START_DATE in MASTER_DATA.

Example : 

   MAX_DATE_AGG             CUSTOMER_ID  DAILY_START_DATE
24/10/17 00:00:00,000000000 100664970   24/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   25/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   26/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   27/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   28/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   29/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   30/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   31/10/17 00:00:00
24/10/17 00:00:00,000000000 100664970   01/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   02/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   03/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   04/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   05/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   06/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   07/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   08/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   09/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   10/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   11/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   12/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   13/11/17 00:00:00
24/10/17 00:00:00,000000000 100664970   14/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   15/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   15/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   16/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   16/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   17/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   17/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   18/11/17 00:00:00
15/11/17 00:00:00,000000000 100664970   18/11/17 00:00:00

Upvotes: 0

Views: 158

Answers (2)

RGruca
RGruca

Reputation: 204

This query should return this what you want

SELECT T.*
  FROM (
          SELECT RANK() OVER (PARTITION BY MASTER_DATA.CUSTOMER_ID, MASTER_DATA.DAILY_START_DATE ORDER BY CALL_DATA.CALL_DATE DESC) AS myrank,
                MASTER_DATA.CUSTOMER_ID,
                MASTER_DATA.DAILY_START_DATE,
                CALL_DATA.CALL_DATE
           FROM MASTER_DATA,
                CALL_DATA
          WHERE CALL_DATA.CALL_DATE   <= MASTER_DATA.DAILY_START_DATE 
            AND CALL_DATA.CUSTOMER_ID = MASTER_DATA.CUSTOMER_ID
       ) T
 WHERE myrank = 1  
  ;

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272296

You can convert your query to correlated sub query:

SELECT MASTER_DATA.*, (
    SELECT MAX(CALL_DATE)
    FROM CALL_DATA
    WHERE CALL_DATA.CUSTOMER_ID = MASTER_DATA.CUSTOMER_ID
    AND CALL_DATA.CALL_DAT <= MASTER_DATA.DAILY_START_DATE
) AS MOST_RECENT_CALL_TILL_NOW
FROM MASTER_DATA

Upvotes: 4

Related Questions