I have a table whose schema is like this:
CREATE TABLE product_shipping(
product_id CHAR(10),
product_name CHAR(10),
deal_dt DATETIME,
deal_reason_no SMALLINT,
cancel_dt DATETIME,
cancel_reason_no SMALLINT
);
Once the shipping date is arranged, the deal_dt and deal_reason_no will be set and cancel_dt and cancel_reason_no is null. However product_id is not unique in the table since the every product shipping can be cancelled. Once cancelled cancel_dt and cancel_reason_no will be set. If the cancelled product is bought by other customer then the shipping is recorded in the new row, and product_name can be changed in the new shipping.
I want to query the product_id and deal_dt whose deal_reason_no is 1, 3 or 5 and deal_dt is between
'2014-04-01 00:00:00' and '2014-04-11 00:00:00' and the **latest previous** deal_dt and latest previous cancel_dt whose cancel_reason_no is 2 or 4 for the product_id.
I think the SQL could be like this:
SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
R.deal_dt as previous_deal_dt,
R.cancel_dt as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (???)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'
But I'm not sure how to join to get latest previous record.
I'm using Informix 11.70.
Edit: Add my idea. Is this reasonable?
SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
MAX(R.deal_dt) as previous_deal_dt,
MAX(R.cancel_dt) as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (C.product_id = R.product_id and TO_CHAR(R.cancel_reason_no) MATCHES ('2', '4') and R.cancel_dt <= C.deal_dt)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'
GROUP BY 1,2
Asked by Marcus Thornton
(151 rep)
Jul 17, 2014, 01:58 AM
Last activity: Jul 2, 2021, 09:25 PM
Last activity: Jul 2, 2021, 09:25 PM