How to calculate time difference?

Question

How to calculate time difference?

Jobs90312

Mr or Mrs. 500

Points: 533
More actions
September 15, 2022 at 6:39 am
Go to Answer
#4087921
Hello everyone,
I have a table logs which maintains file names and date and time at which they were loaded into Sql server database table.
The table looks like this:
```
S.No        Step_name       File_name          Date_time            Minutes_difference
1           File Loaded     Individual_1.xml   9-15-22 10:50 AM       ?
2           File Loaded     Individual_2.xml   9-15-22 11:00 AM       ?
3           File Loaded     Individual_3.xml   9-15-22 11:20 AM       ?
```
I want to know how can I calculate minutes difference between latest file and previous file in the last column.
Like here 1st file was loaded at 10.50 Am, next was loaded after 10 mins, and last one was loaded after 20 mins so last column can show result like this:
```
Minutes_difference
0
10
20
```
Any way to achieve this?
- This topic was modified 2 years, 2 months ago by Jobs90312.
- This topic was modified 2 years, 2 months ago by Jobs90312.
Phil Parkin

SSC Guru

Points: 246960
More actions
September 15, 2022 at 7:17 am
Answer
#4087935
Using getdate() three times repeats exactly the same datetime three times. Notwithstanding that, this should work:
```
SELECT l.id
      ,l.step_name
      ,l.file_names
      ,l.date_time
      ,minutes_difference = DATEDIFF(MINUTE, LAG(l.date_time, 1, l.date_time) OVER (ORDER BY l.id), l.date_time)
FROM logs l
ORDER BY l.id;
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

A combination of DATEDIFF() and LAG() will get this done for you. If you are prepared to produce your sample data in consumable format (ie, in the form of CREATE TABLE and INSERT statements), someone will no doubt provide a coded solution.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jobs90312 Mr or Mrs. 500 Points: 533 More actions · Answer 2

Phil Parkin wrote:

A combination of DATEDIFF() and LAG() will get this done for you. If you are prepared to produce your sample data in consumable format (ie, in the form of CREATE TABLE and INSERT statements), someone will no doubt provide a coded solution.

create table logs(
id int identity (1,1),
step_name nvarchar(200),
file_names nvarchar(200),
date_time datetime,
minutes_difference int
)

insert into logs ( step_name, file_names, date_time)
values
('File Loaded', 'Individual_1.xml',getdate()),
('File Loaded', 'Individual_2.xml',getdate()),
('File Loaded', 'Individual_3.xml',getdate())

This reply was modified 2 years, 2 months ago by Jobs90312.