I want to SELECT for 2 columns from two status
ORDER_NUMBER | STATUS | DATE
-------- ----------- --------------
001 | submit | 2021-08-31
113 | submit | 2021-09-01
113 | approve | 2021-09-03
001 | cancel | 2021-09-02
112 | submit | 2021-09-01
112 | cancel | 2021-09-10
112 | resubmit | 2021-09-13
112 | approve | 2021-09-15
Expected result:
ORDER_NUMBER | APPROVE_DATE | CANCEL_DATE
------------- -------------- --------------
001 | null | 2021-09-02
112 | 2021-09-15 | 2021-09-10
113 | 2021-09-03 | null
My code:
SELECT ORDER_NUMBER,
CASE WHEN STATUS = 'approve' THEN DATE END AS APPROVE_DATE,
CASE WHEN STATUS = 'cancel' THEN DATE END AS CANCEL_DATE
FROM
TABLE
But the result it show
ORDER_NUMBER | APPROVE_DATE | CANCEL_DATE
------------- -------------- --------------
001 | null | null
001 | null | 2021-09-02
112 | null | null
112 | null | 2021-09-10
112 | null | null
112 | 2021-09-15 | null
113 | null | null
113 | 2021-09-03 | null
It shows duplicate ORDER NUMBER which not have my target status , How can I fix it?
CodePudding user response:
Must be an ID in there somewhere to match off the approvals with the cancels? The logic below will show the approval date followed by the next earliest cancel. So doesn't match your 2nd row, but you get the idea.
select a.date Approve_date,
c.Date Cancel_date
from Status a
left join Status c
on c.order_number = a.order_number
and c.status = 'cancel'
where a.status = 'approve'
order by a.date
CodePudding user response:
The main problem you're facing here is that there's absolutely no connections between your different entries inside the first table: you cannot tell which approve
entry a given cancel
entry corresponds to.
If what you actually want is classing an entry into the right column while preserving chronological order, you first need to sort your table according to this order, then specifying NULL in the other column:
SELECT
CASE WHEN STATUS = 'approve' THEN DATE ELSE NULL END AS APPROVE_DATE,
CASE WHEN STATUS = 'cancel' THEN DATE ELSE NULL END AS CANCEL_DATE
FROM
TABLE
ORDER BY
DATE ASC
…which will give you:
APPROVE | CANCEL |
---|---|
2021-08-31 | NULL |
2021-09-03 | NULL |
NULL | 2021-09-04 |
NULL | 2021-09-06 |
2021-09-09 | NULL |
NULL | 2021-09-20 |
Also, DATE
is a reserved keyword, so you'd better choose another name for you column. This will save you later troubles. ;-)
CodePudding user response:
Now you have edited your question, you now have an "order number" which is the actual ID of each entity you have stored attributes in your table.
So what you want to do now is first selecting all of these numbers, then pick "approve" and "cancel" dates for each of them. You typically want to do that with "joins". The trick here being the fact that you're totally allowed to perform a self-join, against the same table. So something like the following would do:
SELECT DISTINCT
t1.order_number,
t2.status AS Approval_Date,
[…] AS Cancel_Date
FROM table t1
LEFT OUTER JOIN table t2
ON t2.order_number = t1.order_number
AND t2.status = 'approve'
[…]
ORDER BY
order_number ASC
Since this is possibly a homework, I'll let you complete this query by yourself. You just need to add another "LEFT OUTER JOIN" block like the one described here and complete SELECT statement for "Cancel_Date" row. ;-)