RustyShackleford RustyShackleford - 1 year ago 75
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?


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 Source

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 );

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

And the output will be:

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download