1

so i have this query that group company and find the latest two row from periods for each company from the salesreport

App\salesreport::join(DB::RAW('(SELECT company_id, GROUP_CONCAT(periods ORDER BY periods DESC) grouped_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`salesreport`.`periods`, `latest_report`.`grouped_periods`)'), [1, 2]);
    })->get();

and its returning table that looks like this

+----+------------+-------+------------+
| id | company_id | value |  periods   |
+----+------------+-------+------------+
|  1 | A1         |   100 | 2017-02-02 |  
|  2 | A1         |   150 | 2017-01-01 |  
|  3 | A2         |    80 | 2017-06-01 |  
|  4 | A2         |    60 | 2017-04-01 | 
+----+------------+-------+------------+

and for the next step i want to find a differences for each value (which in this simple example is only value column but the real project is containing a lot of column

to make it to be something like this table

+----+------------+-----------+------------+
| id | company_id |   value   |  periods   |
+----+------------+-----------+------------+
|  1 | A1         | 100 (-50) | 2017-02-02 |
|  3 | A2         | 80 (+20)  | 2017-06-01 |
+----+------------+-----------+------------+

how to do that? is it possible in one request to the database? as i think we already got the data on hand but need to find a elegant way to do calculation between first row and second row for each company group within the sales report and turn it into some new fields

so maybe something like

App\salesreport::join(DB::RAW('(SELECT company_id, GROUP_CONCAT(periods ORDER BY periods DESC) grouped_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`salesreport`.`periods`, `latest_report`.`grouped_periods`)'), [1, 2]);
    })->addSelect([DB::raw('(row1.value - row2.value) as row_differences')])->get();
PamanBeruang
  • 1,531
  • 5
  • 27
  • 64

1 Answers1

0

It's possible (but probably not a good idea):

App\salesreport::join(DB::RAW('(SELECT company_id, MAX(periods) AS max_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
    $join->on('salesreport.company_id','=','latest_report.company_id');
    $join->on('salesreport.periods','=','latest_report.max_periods');
})->select('*')->selectRaw(
    'CAST(`value` AS SIGNED)-CAST((
        SELECT `value`
        FROM `salesreport`
        WHERE `company_id`=`latest_report`.`company_id`
        ORDER BY `periods` DESC
        LIMIT 1,1
    ) AS SIGNED) as `difference`'
)->get();

If the value column is SIGNED, you can remove the casting.

Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109