I have data related material transactions in one table and log history header data related to materials is in another table and detailed log history data in third table. I'm trying to get different status update dates matched to material table but I get duplicate rows for one material transaction

Original material transaction table:

Original Log History Header transaction table:

Status code 1 refers to Opened and code 2 to Closed

Detailed Log History table:

With following SQL query:

SELECT 
       TO_CHAR (m.order_no) order_no,
       m.material,
       a.date opened_date,
       ab.closed_date
  FROM MATERIAL_TRANSACTIONS m
       INNER JOIN HISTORY_LOG t
          ON m.ORDER_NO = t.ORDER_NO
       INNER JOIN HISTORY_LOG_DETAILED a
          ON     t.LOG_ID = a.LOG_ID
             AND a.STATUS_CODE = '1'
       INNER JOIN HISTORY_LOG_DETAILED ab
          ON     t.LOG_ID = ab.LOG_ID
             AND ab.STATUS_CODE = '2'

I get following result:

And I would like to get the status dates to the same row as below:

I would appreciate all the help I can get and am very sorry if there already is topic for similar issue.

CodePudding user response：

Your problem occurs because you join the history table, which holds 2 records for the order. You could flatten this if you use 2 inline tables that hold exactly 1 record.

with opened_dates as (
  select h.order_id, d.date
  from history h
  inner join details d on h.log_id = d.log_id and d.status_code = '1'
), closed_dates as (
  select h.order_id, d.date
  from history h
  inner join details d on h.log_id = d.log_id and d.status_code = '2'
)
select to_char (m.order_no) order_no,
       m.material,
       o.date opened_date,
       c.date closed_date
from material_transactions m
join opened_dates o on m.order_no = o.order_no
join closed_dates c on m.order_no = c.order_no
;

CodePudding user response：

Just an idea :

I joined HISTORY_LOG and HISTORY_LOG_DETAILED tables to get dates for specific status, and set as OPENED_DATE and CLOSED_DATE (if status 1 , then opened date is DATE column, otherwise set it as 01.01.0001)

After that grouped those records by ORDER_NO and maxed the date values to get actual OPENED_DATE and CLOSED_DATE .

Finally joined this subquery with MATERIAL_TRANSACTIONS table :

SELECT 
       TO_CHAR (M.ORDER_NO) ORDER_NO,
       M.MATERIAL,
       QTY,
       L_T.OPENED_DATE,
       L_T.CLOSED_DATE
  FROM MATERIAL_TRANSACTIONS M  
  INNER JOIN 
  (
  SELECT  L.ORDER_NO , 
 MAX( CASE WHEN LD.STATUS_CODE = 1 THEN LD.DATE ELSE TO_DATE('01.01.0001','dd.mm.yyyy')  END ) OPENED_DATE
MAX( CASE WHEN LD.STATUS_CODE = 2 THEN LD.DATE ELSE TO_DATE('01.01.0001','dd.mm.yyyy')  END ) CLOSED_DATE 
   FROM 
  HISTORY_LOG L 
  INNER JOIN HISTORY_LOG_DETAILED LD ON LD.LOG_ID = L.LOG_ID
 GROUP BY L.ORDER_NO
 ) L_T on L_T.ORDER_NO = M.ORDER_NO

Note: I didnt test it. So there can be small syntax errors. Please check it and for better help add a fiddle so i can test my query