1

I have the following table in SQL

Date            Time       Value
======================================
01/01/2020    00:05:00      10
01/01/2020    10:05:00      20
01/01/2020    20:05:00      35
02/01/2020    00:05:00      10
02/01/2020    10:05:00      15
02/01/2020    20:05:00      35

Want to add a new column to find the difference between the current row value with previous row value for a day like below

Date            Time       Value   New_Value
=============================================
01/01/2020    00:05:00      10      10
01/01/2020    10:05:00      20      10  (20-10)
01/01/2020    20:05:00      35      15  (35-25)
02/01/2020    00:05:00      10      10
02/01/2020    10:05:00      15      5   (15-10)
02/01/2020    20:05:00      35      20  (35-15)

Can this be achieved in SQL without introducing any new columns.

Thom A
  • 88,727
  • 11
  • 45
  • 75
SM079
  • 412
  • 2
  • 7
  • 20
  • I don't think you actually want to do `ALTER TABLE ADD COLUMN` - I think you just want a `VIEW`. – Dai May 08 '23 at 18:03
  • How to get the difference from current row value to previous row value. do you have any idea ? – SM079 May 08 '23 at 18:03
  • Anyway, use `LAG` and `LEAD` for this - also tables do not have a meaningful sort-order: there is no concept of a "previous row" in an RDBMS - instead you have to provide an explicit `ORDER BY` clause for the `OVER` window. – Dai May 08 '23 at 18:03
  • ...dare I ask why your `date` and `time` columns are... separate columns? Is there a _good reason_ why you aren't using a singular `datetime2(7)` or `datetimeoffset(7)` column instead? – Dai May 08 '23 at 18:05
  • These table is an already existing structure and it is being used for different filtering purpose. if needed it can be modified – SM079 May 08 '23 at 18:09
  • 1
    Are you familiar with `VIEW` objects and table-valued functions? (kinda-similar-ish to `PROCEDURE` but also very different in its own way) – Dai May 08 '23 at 18:09
  • Okay @Dai, will look into VIEW as well – SM079 May 08 '23 at 18:19

1 Answers1

0

One of your methods is to use Lag and create a view

create view vw_tt
as 
select *,  value-lag(value) over (
partition by date  order by(CAST(Date AS datetime) + CAST(Time AS datetime)))  as New_Value

from tt

Example data:

create table tt(Date date,time time(0),value int)

insert into tt(Date,Time,Value)
values ('01/01/2020',    '00:05:00',      10) ,('01/01/2020',    '10:05:00',      20),('01/01/2020',    '20:05:00',      35)
,('02/01/2020',    '00:05:00',      10),('02/01/2020',    '10:05:00',      15),('02/01/2020',    '20:05:00',      35)
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20