SQL to get a difference in value for the same column but for different dates within the same table [duplicate]

250
March 01, 2017, at 08:49 AM

This question already has an answer here:

  • MySQL difference between two rows of a SELECT Statement 4 answers

How do I get a difference in value of a particular column for a particular date to the value for the same column for the previous date?

Current Table:
Date          Qty
8-Jan-17      100
9-Jan-17      120
10-Jan-17     180
Desired Output:
Date         Diff Qty
9-Jan-17      20
10-Jan-17     60
Answer 1

This will work assuming dates are no repeated and there is no gap in between them either.

    --Sample data as provided. This script works in SQL Server 2005+
    CREATE TABLE #Table1
        ([Date] datetime, [Qty] int)
    ;
    INSERT INTO #Table1
        ([Date], [Qty])
    VALUES
        ('2017-01-08 00:00:00', 100),
        ('2017-01-09 00:00:00', 120),
        ('2017-01-10 00:00:00', 180)
    ;
   --This script is plain SQL for any DMBS 
   select y.Date, y.Qty-x.Qty as 'Diff Qty'
   from #table1 x inner join #Table1 y 
     on x.Date+1=y.Date

Result

+-------------------------+----------+
|          Date           | Diff Qty |
+-------------------------+----------+
| 2017-01-09 00:00:00.000 |       20 |
| 2017-01-10 00:00:00.000 |       60 |
+-------------------------+----------+
READ ALSO
How to define a key/value table with 10 billion rows in mysql?

How to define a key/value table with 10 billion rows in mysql?

k is a unique string(varchar 32), v is also a string(varchar 50),

320
MySQL trigger works on one field, not on second field, in the same table

MySQL trigger works on one field, not on second field, in the same table

I have a MySQL database with a table inventory with multiple triggers set up to capture changes in a second table inventory_historyI'm updating two of the fields (both in a single query, and in two separate queries), and the trigger consistently works on only one of the two fields...

313
Performance using Firebase UID (Varchar) as PK in MySQL

Performance using Firebase UID (Varchar) as PK in MySQL

I would like to use the Firebase Authentication System and store at least 3 Million users in a relational database

415
Codeigniter 3 Insert query fail

Codeigniter 3 Insert query fail

my problem is so weirdi am trying to insert a row in mysql table (using MyISAM)

450