title: How to get active orders for past year
body: <p>I have a table which has start and end date. Based on that table, I have to get the active orders for the past 12 months. </p>
  
  <p>In end date NULL means it is active order until now.</p>
  
  <p>Table Basic idea</p>
  
  <p><a href="https://i.stack.imgur.com/Qheaa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qheaa.png" alt="enter image description here"></a></p>
  
  <p>Expected Output</p>
  
  <p><a href="https://i.stack.imgur.com/4r6A4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4r6A4.png" alt="enter image description here"></a></p>
  
  <p>Based on the past experience I'm able to write the query like this for the current year January but i need it for all the months as shown in the above picture</p>
  
  <pre><code>select
        mnth.num, count(*)
  from (
      select 1 AS num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all 
      select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12
      ) mnth
  left join (    
        select
              o.id
            , case 
                 when o.start_date &lt; date_format(current_date(), '%Y-01-01') then 1
                 else month(o.start_date)
                 end AS start_month
            , case 
                 when o.end_date &lt; date_format(current_date(), '%Y-01-01') then 0
                 when o.end_date &gt;= date_format(current_date(), '%Y-01-01') then month(o.end_date)
                 else month(current_date())
                 end AS end_month
        from order o
        ) active_work_orders on mnth.num between active_work_orders.start_month and active_work_orders.end_month  
  where mnth.num &lt;= month(current_date())
  group by
        mnth.num
  ;
  </code></pre>
  
  <p>Based on the output I have to generate a graph. Kindly help on this</p>
  
  <p>Fiddle link <a href="http://dbfiddle.uk/?rdbms=mariadb_10.2&amp;fiddle=b24018efb9cde45b688068f9ccb701fd" rel="nofollow noreferrer">click here</a></p>