RustyShackleford RustyShackleford - 2 months ago 6
SQL Question

How to do mathematical operations in same column on different rows in database?

As the questions suggests, is there a way to do mathematical operations on the same column except different rows?

Example.

Table A:
X Y Time New_time
0 aaa bbb 5 3
1 aaa bbb 2 1
2 aaa bbb 1 null


i want to find the difference in time for each row. So row 0 would have new_time as 3min. How would I go about doing this?

Thank you in advance.

Answer

You can use LEAD analytic function introduced in SQL Server 2012:

CREATE TABLE [dbo].[Times]
    (
      [ID] [INT] IDENTITY(1, 1)
                 NOT NULL ,
      [X] [NVARCHAR](50) NOT NULL ,
      [Y] [NVARCHAR](50) NOT NULL ,
      [Time] [INT] NOT NULL
    ); 


INSERT  INTO [dbo].[Times]
        ( X, Y, [Time] )
VALUES  ( 'aaa', 'bbb', 5 ),
        ( 'aaa', 'bbb', 2 ),
        ( 'aaa', 'bbb', 1 );

SELECT  * ,
        New_Time = [Time] - ( LEAD([Time]) OVER ( ORDER BY ID ) )
FROM    [Times];

And the output will be:

enter image description here