Question: I have a record in my table:

[![enter image description here](https://i.stack.imgur.com/3U3TU.png)](https://i.stack.imgur.com/3U3TU.png)

What I need is to create a column with order state: '1' if order was created, '0' if order was cancelled.

So for this example, when there was both creation and cancellation I need two states. The final table should be:

[![enter image description here](https://i.stack.imgur.com/nEDfu.png)](https://i.stack.imgur.com/nEDfu.png)

How can I do this?

Answer: 
I think you can simply do a UNION like this:

```
select OrderCreateDate, OrderCancelDate, ReportDate, 1 as OrderState
  from your_table
  where orderCreateDate is not null
union all
select OrderCreateDate, OrderCancelDate, ReportDate, 0 as OrderState
  from your_table
  where orderCancelDate is not null

```
Join to the table a query that returns the values `1` and `0`:

```
SELECT t.*, s.OrderState
FROM tablename AS t
INNER JOIN (SELECT 1 AS OrderState UNION ALL SELECT 0) AS s
ON (s.OrderState = 1 AND t.OrderCreateDate IS NOT NULL)
OR (s.OrderState = 0 AND t.OrderCancelDate IS NOT NULL)

```