CODEWITHSUNDEEP
mysqlsql
Andy
Andy

Reputation: 27

Copying and shifting column in SQL query

I have table with a list of dates. These are not ordered in the database, but using a SQL SELECT query.

There are two intervals I want to calculate. The first is not a problem, which is calculating the interval between the dates in the same row.

Date 1      Date 2      (Interval 1)
1950-01-01  1960-01-01  (10.00)
1951-07-01  1962-01-01  (10.50)
1952-04-01  1964-07-01  (11.25)
1953-07-01  1968-10-01  (15.25)
1958-01-01  1970-01-01  (12.00)

However, I also want to calculate the difference between Date 1 of a row, and Date 2 of the row above it (in the SQL SELECT output). Essentially I want the Date 2 column to be copied and shifted down one row (or something to that effect) so that I can calculate Interval 2.

Date 1      Date 2      Interval 1  Date 2_shift  Interval 2
1950-01-01  1960-01-01  10.00       
1951-07-01  1962-01-01  10.50       1960-01-01    8.50 
1952-04-01  1964-07-01  11.25       1962-01-01    9.75
1953-07-01  1968-10-01  15.25       1964-07-01    11.00
1958-01-01  1970-01-01  12.00       1968-10-01    10.75

Upvotes: 0

Views: 768

Answers (3)

FutbolFan
FutbolFan

Reputation: 13723

Like @Mihai mentioned, you would need to use a row_number field and do a left join to get the previous date values:

SET @row_number:= 0;
SET @row_number1:= 0;

select q1.*
      ,q2.Date2 as PreviousDate
from
(SELECT @row_number:= @row_number + 1 AS row_number
        ,dt.*
FROM datetable dt
) q1
left join (SELECT @row_number1:= @row_number1 + 1 AS row_number
        ,dt.*
FROM datetable dt
) q2 on q1.row_number-1 = q2.row_number

SQL Fiddle Demo

Upvotes: 1

Mihai Ovidiu Drăgoi
Mihai Ovidiu Drăgoi

Reputation: 1317

What you need to do is add a row number column in your query, then left join it with itself on firstinstance.rownumber = secondinstance.rownumber + 1

Then you can do the calculus you need.

Hope this helps!

Upvotes: 1

Pradeep
Pradeep

Reputation: 90

ALTER TABLE table_name CHANGE column_name column_name datatype(length) AFTER column_name

eg: ALTER TABLE table_name CHANGE Date 2 Date 2 datatype(length) AFTER Interval 1

Upvotes: 0

Related Questions