Home > Enterprise >  SELECT for 2 columns from different status
SELECT for 2 columns from different status

Time:11-16

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

enter image description here

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. ;-)

  • Related