CODEWITHSUNDEEP
phpmysql
AftabHafeez
AftabHafeez

Reputation: 173

Split date range and get combined results in mysql

I have two tables

orders_status_history 

   id | order_id | status |   date 
   1  |  1201    |  2     | 2015-01-20
   2  |  1124    |  4     | 2015-02-01
   3  |  1245    |  1     | 2015-02-14
   4  |  1365    |  2     | 2015-03-10

saved_shipping_invoices

   id | order_id  | product_id |  date 
   1  |  1348     |  12541     | 2015-12-18
   2  |  1298     |  11485     | 2016-01-02
   3  |  1319     |  14521     | 2016-05-14
   4  |  1441     |  10124     | 2016-05-14

and one year date range i.e. 

$start_date = '2015-09-30';
$end_date   = '2016-09-30';

Data in table "shipping_invoice_history" started from 2015-12-18. Now I want to break date interval on 2015-12-18. There will be two intervals now 

$interval1_start = '2015-09-30';
$interval_end    = '2015-12-17';

$interval2_start = '2015-12-18';
$interval2_end   = '2016-09-30';

When it breaks the date interval, I want to get data from table "orders_status_history" on the first interval but on the second interval it gets data from "shipping_invoice_history" .Also I need to show the data in single table. 

  $week = '2015-09-30 @@ 2016-09-30';
     $week_ranges  = explode('@@', $week);
    if ($week_ranges[0] > '2015-12-18' )
                    {
                        $invoices_shipped_qty  = tep_db_query("SELECT  *  FROM `saved_shipping_invoices` WHERE `date_created` BETWEEN '".$week_ranges[0]."' AND '".$week_ranges[1]."'");
                        $invoices_shipped_arr = tep_db_fetch_array($invoices_shipped_qty);
                        $total_sent =  $invoices_shipped_arr['shiped_qty'];
                        }
                        else {  
                    $total_order_status_complete = tep_db_query("SELECT  *  FROM `orders_status_history` WHERE `orders_status_id` = '17'  AND `date_added` BETWEEN '".$week_ranges[0]." 00:00:00' AND '".$week_ranges[1]." 23;59:59'");
                    $total_order_status_complete_arr = tep_db_fetch_array($total_order_status_complete);

Please suggest me how can i break date interval into two, get result from two tables and display in single run.

Upvotes: 3

Views: 949

Answers (2)

Som Shekhar
Som Shekhar

Reputation: 138

Depending on how dynamic is your query, I like to do something like this instead of union.

I prefer this option:

select 

if(date <='2015-12-17', 'Phase 1',
if(date >='2015-12-18', 'Phase 2', 
'None')), 

  id, 
  order_id, 
  status, 
  null as product_id, 
  date from order_status_history
where 
 date between '2015-09-30' and '2016-09-30'

or

select 

if(date between '2015-09-30' and '2015-12-17', 'Phase 1',
if(date between '2015-12-18' and '2016-09-30', 'Phase 2', 
'None')), 

  id, 
  order_id, 
  status, 
  null as product_id, 
  date from order_status_history
where 
 date between '2015-09-30' and '2016-09-30'

Upvotes: 0

Jandisson
Jandisson

Reputation: 380

Probably this is what you want

select 
  'order_status_history' as table, 
  id, 
  order_id, 
  status, 
  null as product_id, 
  date from order_status_history
where 
 date between '2015-09-30' and '2015-12-17'
union all
select 
  'saved_shipping_invoices' as table, 
  id,
  order_id,
  null as status, 
  product_id, 
  date 
from saved_shipping_invoices
 where 
  date between '2015-12-18' and '2016-09-30'

Upvotes: 6

Related Questions