Calculations between rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculations between rows

Author

Topic

vicspencer
Starting Member

3 Posts

Posted - 2014-06-03 : 15:48:10

I am very new to SQL and have followed a few tutorials, but don't have the practice or specific instruction on how to do the following.

I have a table that can be reduced to something similar to below that includes; serial, miles, repair date.

I want to have a fourth column with the calculated miles since last repair date.

Not sure where to start, I don't want to bore with what I have attempted.

VIN Repair date Total miles Miles since last repair
1001 5/23/2013 15000
1235 8/30/2013 856
1236 2/4/2014 3645
1001 4/6/2014 15800 800

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-03 : 16:04:43

What version of SQL are you using?

If 2012+ you can use the LAG/LEAD functions. If you are using a lower version you can do some "fancy" stuff with the ROW_NUMBER function. let us know and we can give you a sample.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-03 : 16:35:30

Maybe one of these will Work for you.

Generic:

select a.VIN
      ,a.[repair date]
      ,a.[Total miles]
      ,a.[Total miles]-max(b.[Total miles]) as [Miles since last repair]
  from yourtable as a
       left outer join yourtable as b
                    on b.VIN=a.VIN
                   and b.[repair date]<=a.[repair_date]
                   and b.[Total miles]<a.[Total miles]
 group by a.VIN
         ,a.[repair date]
         ,a.[Total miles]

row_number:

select VIN
      ,[repair date]
      ,[Total miles]
      ,[Miles since last repair]
  from (select a.VIN
              ,a.[repair date]
              ,a.[Total miles]
              ,a.[Total miles]-b.[Total miles] as [Miles since last repair]
              ,row_number() over(partition by a.VIN
                                 order by a.[repair date] desc
                                         ,a.[Total miles] desc
                                ) as rn
          from yourtable as a
               left outer join yourtable as b
                            on b.VIN=a.VIN
                           and b.[repair date]<=a.[repair_date]
                           and b.[Total miles]<a.[Total miles]
       ) as a
 where rn=1

lag:

select VIN
      ,[repair date]
      ,[Total miles]
      ,[Total miles]-lag([Total miles],1,0) over(partition by VIN
                                                 order by [repair date] desc
                                                         ,[Total miles] desc
                                                ) as [Miles since last repair]
  from yourtable

vicspencer
Starting Member

3 Posts

Posted - 2014-06-03 : 16:52:46

quote:
Originally posted by Lamprey

What version of SQL are you using?

If 2012+ you can use the LAG/LEAD functions. If you are using a lower version you can do some "fancy" stuff with the ROW_NUMBER function. let us know and we can give you a sample.

I am using 2012

vicspencer
Starting Member

3 Posts

Posted - 2014-06-03 : 19:55:37

quote:

Originally posted by bitsmed

Maybe one of these will Work for you.

Generic:

select a.VIN
      ,a.[repair date]
      ,a.[Total miles]
      ,a.[Total miles]-max(b.[Total miles]) as [Miles since last repair]
  from yourtable as a
       left outer join yourtable as b
                    on b.VIN=a.VIN
                   and b.[repair date]<=a.[repair_date]
                   and b.[Total miles]<a.[Total miles]
 group by a.VIN
         ,a.[repair date]
         ,a.[Total miles]

row_number:

select VIN
      ,[repair date]
      ,[Total miles]
      ,[Miles since last repair]
  from (select a.VIN
              ,a.[repair date]
              ,a.[Total miles]
              ,a.[Total miles]-b.[Total miles] as [Miles since last repair]
              ,row_number() over(partition by a.VIN
                                 order by a.[repair date] desc
                                         ,a.[Total miles] desc
                                ) as rn
          from yourtable as a
               left outer join yourtable as b
                            on b.VIN=a.VIN
                           and b.[repair date]<=a.[repair_date]
                           and b.[Total miles]<a.[Total miles]
       ) as a
 where rn=1

lag:

select VIN
      ,[repair date]
      ,[Total miles]
      ,[Total miles]-lag([Total miles],1,0) over(partition by VIN
                                                 order by [repair date] desc
                                                         ,[Total miles] desc
                                                ) as [Miles since last repair]
  from yourtable

Thanks for giving all the examples, I am using the lag method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources