Get differences for each value from two row

88
March 24, 2018, at 6:31 PM

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();
READ ALSO
Order of columns in an index

Order of columns in an index

I am working on a database with large number of rows (6 Mil+)

184
How to Select selctbox based on DB value in angularJS?

How to Select selctbox based on DB value in angularJS?

I am new to AngularI want to develop similar functionality in my local host

95
How to update the status automatically after 10 days using Event schedular?

How to update the status automatically after 10 days using Event schedular?

I have sent_date and status in my tableI Wanted to update the status automatically after 10 days

115
I receive an error, but I do not know what it is

I receive an error, but I do not know what it is

I must form a single row table with elements of the mysql server chosen by the ID, the table runs perfectly (it is displayed in the html file)I execute it in javascript file through an ajax function, when I call the function , my javascript collapses,...

113