CODEWITHSUNDEEP
phpmysqlsql
Undefined Variable
Undefined Variable

Reputation: 4267

how to write query to check another table based on condition?

I have an order_status table like so:

order_id   order_status
1          100
2          1000
3          1000

I have another table order_status_history which stores historical data for order:

id  order_id   order_status  status_date
1   1          100           2016-01-01
2   2          100           2016-02-01
3   2          300           2016-04-01
4   3          100           2016-04-01
5   3          400           2016-04-01
6   3          1000          2016-04-01
7   2          1000          2016-05-01

I am trying to build a daily report, where for a given date, I want to see if an order was completed or in progress. I am pretty bad with SQL and not able to figure out how to do it. If for a date, the status of the order is < 1000 then it is in progress, else it is completed.

Eg: For date 2016-04-01, the report would be:

order_id    status
1           in-progress (because as of report date, its latest status is 100)
2           in-progress (because as of report date, its latest status is 300)
3           completed   (because as of report date, its latest status is 1000)

I am trying something like following to try to at least find in progress orders:

select distinct s.order_id, s.order_status
from order_status 
    left join order_status_history h on h.order_id = s.order_id
where max(h.status_date) <'2016-04-01'
  and status < 1000

Any help is greatly appreciated!

Upvotes: 2

Views: 81

Answers (4)

Shadow
Shadow

Reputation: 34294

Assuming that even the current order status is recorded in the history table (based this on the data for order 1):

select distinct s.order_id, s.order_status as current_order_status, if(h2.order_status<1000,'in progress','completed') as status_at_report_date
from order_status left join
    (select order_id, max(id) as max_id from order_status_history h1
    where h1.status_date<='2016-04-01') t on t.order_id=s.order_id
    left join order_status_history h2 on t.max_id=h2.id

Upvotes: 0

jitendra joshi
jitendra joshi

Reputation: 687

Try this

    select m.order_id,IF(order_done < order_status,'in-progress (because as of report date, its latest status is '||m.order_done,'completed (because as of report date, its latest status is '||m.order_done) status
    from order_status m left join (select osh.order_id, max(osh.order_status) order_done
    from order_status_history osh
where osh.status_date <'2016-04-01' and osh.status < 1000
    group by osh.order_id) n on m.order_id = n.order_id

Upvotes: 0

fabulaspb
fabulaspb

Reputation: 1263

If you want to get status of order_id for specified date you can use next query

SELECT OS.order_id,
        CASE WHEN 
            (SELECT MAX(OSH.order_status) 
             FROM order_status_history AS OSH
             WHERE OSH.order_id = OS.order_id
                       AND OSH.status_date <= @date)) < 1000
             THEN 'in-progress'
             ELSE 'completed' END status
FROM order_status AS OS

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I don't know what your statuses mean. But for a given order they seem to always be increasing. Hence, the most recent one would have the largest value:

select osh.order_id, max(osh.order_status)
from order_status_history osh
group by osh.order_id;

If the assumption of increasing statuses is not true, then you can use an aggregation and join:

select osh.*
from order_status_history osh join
     (select order_id, max(status_date) as maxsd
      from order_status_history
      group by order_id
     ) o
     on osh.order_id = osh.order_id;

As for the text description, you should really be storing this information in a reference table and joining that it in. (I suspect that is what order_status is, but just poorly described.) Otherwise, you use a case statement:

select (case when osh.order_status = 100 then 'in-progress'
             when osh.order_status = 100 then 'in-progress'
             when osh.order_status = 1000 then 'completed'
        end)

Upvotes: 1

Related Questions