Calculating the time difference between consecutive records in a RecordSet
-
Hi all,
I have a taxing question for you
If I have a query that returns me the following (which is a list of all Inspections on a given day), what I would like to be able to do is to calculate the time difference between consecutive records. So for example, if I get the following back...
1) Inspection 1, 1/1/2006 09:00
2) Inspection 1, 1/1/2006 10:05
3) Inspection 1, 1/1/2006 12:10
4) Inspection 1, 1/1/2006 13:15
...what I want, probs as a Calculated column, is
1) Inspection 1, 1/1/2006 09:00, None, as there is nothing before it
2) Inspection 1, 1/1/2006 10:05, 01:05
3) Inspection 1, 1/1/2006 12:10, 02:05
4) Inspection 1, 1/1/2006 13:15, 01:05
... a record always calculates the time difference between itself, and the previous record, apart from when it is the first record in the resultset.
Is this at all possible? As I really don't know where to start.
Thanks
Tryst
-
Hope you are using SQL Server 2000. If so here is one of the way to solve ur problem....
declare @test-2 table
(
taskvarchar(30),
datedatetime
)
insert into @test-2 values ('Inspection 1', '1/1/2006 09:00')
insert into @test-2 values ('Inspection 1', '1/1/2006 10:05')
insert into @test-2 values ('Inspection 1', '1/1/2006 12:10')
insert into @test-2 values ('Inspection 1', '1/1/2006 13:15')
select a.task, a.date,
convert(varchar(5), dateadd(mi, isnull((select datediff(mi, MAX(date),a.date) from @test-2 b
where b.Task= a.Task
and b.date < a.date),0), cast('01/01/2006' as datetime)),114) as diff
from @test-2 a
order by date
-
Select T1.Inspection, T1.Log_Date, T1.Log_Date - MAX(T2.Log_Date) as TimeGap
FROM InspectionRecord T1
LEFT JOIN InspectionRecord T2 ON T1.Inspection = T2.Inspection AND T1.Log_Date > T2.Log_Date
GROUP BY T1.Inspection, T1.Log_Date
_____________
Code for TallyGenerator -
Good morning all
Nagabhushanam Ponnapalli - your query works perfectly. But ideally, I'd like to wrap it up in a query without using a temp table - a bit like how Sergiy has done. But if that isn't possible, then I will def use this query.
Sergiy, it seems your query doesn't work when I actually change it to map across to my column names. I just get NULL values un the TimeGap column. The query is as follows...
Select T1.InspectionUID,
T1.Description,
T1.CreatedDate,
T1.CreatedDate - MAX(T2.CreatedDate) as TimeGap
FROM tbl_NSP_Inspection T1
LEFT JOIN tbl_NSP_Inspection T2 ON T1.Description = T2.Description AND T1.CreatedDate > T2.CreatedDate
GROUP BY T1.InspectionUID, T1.Description, T1.CreatedDate
Thanks for the replies.
Tryst
-
>Nagabhushanam Ponnapalli - your query works perfectly. But ideally, I'd like to wrap it up in a query without using a temp table - a bit like how Sergiy has done.
The temp table is for test data - not part of the example query.
>Sergiy, it seems your query doesn't work when I actually change it to map across to my column names. I just get NULL values un the TimeGap column.
You would expect to get null values (with meaning: 'not applicable') for the first record in each group. But if you're getting NULLs on every row, it's probably either because:
(a) Description is unique in the table. you should check that this is the correct grouping column. I would expect the grouping to be on a foreign key.
(b) Each group of Inspection records with the same description all have the same CreatedDate.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi stax68,
yes it is because the Description column in the table is Unique. Is this the problem then?
Tryst
-
Yes. The query is working fine, but every record is in a group of 1. You need to decide what you are grouping on - probably whatever entity it is that gets inspected. Complete the following sentence:
I want to show a list of inspections for each ______
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I want to show a list of inspections for each for each day where each Inspection undertaken has a calculated column added to it that determines the time difference between the currect Inspection in question, and the one before it (examples of this are provided in my initial post). But there is an exception where we have the first inspection of the day will not have a duration value (default it to 00:00) as it can't be calulated because it doens't have an Inspection before it on that day.
Hope this has clarified things, if not, let me know.
Thanks
Tryst
-
Yes.
I could perhaps have guessed that by rereading your original post, but putting time into speculative answers which are potentially irrelevant is beyond the call of duty as far as I'm concerned! Try this:
- select T1.InspectionUID,
- dateadd
- T1.CreatedDate InspDateTime,
- T1.CreatedDate - max(T2.CreatedDate) TimeGap
- from
- left
- on
- and
- group
(day,0,datediff(day,0,T1.CreatedDate)) InspDate,
tbl_NSP_Inspection T1
join tbl_NSP_Inspection T2
T2.CreatedDate >= dateadd(day,0,datediff(day,0,T1.CreatedDate))
T2.CreatedDate < T1.CreatedDate
by T1.InspectionUID, T1.CreatedDate
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi All,
Here is a solution that uses a correlated subquery instead of joins.
SELECT
[task]
,[date]
,[date] - (select top 1 [date]
from [test_dt]
where [task] = main.[task]
and [date] < main.[date]
order by [date] desc) as timegap
FROM [test_dt] as main
I haven't compared its performance against the other solutions but I find it easier to read.
Antonio Macedo
-
There is a need for the time day as it is used for reporting on. I don't want to write this data into a separate column each time an inspection is undertaken as it is written on a mobile device, so the less amount of trips to the database, the better.
The query worked a treat by the way.
Thanks
Tryst
-
Hi All,
I've just compared the performance between the correlated subquery and the left join solutions.
With no index, the left join performs better than correlate subquery.
Correlated Subquery = 75%
Left Join = 25%
With a index on the date field, the correlate subquery performs better than left join.
Correlated Subquery = 38%
Left Join = 62%
Correlate Subquery Solution
SELECT
[task]
,[date]
,[date] - (select top 1 [date]
from [test_dt]
where [task] = main.[task]
and [date] < main.[date]
order by [date] desc) as timegap
FROM [test_dt] as main
Left Join Solution
select T1.task,
T1.date,
T1.date - max(T2.date) TimeGap
from test_dt T1
left join test_dt T2
on T2.date >= dateadd(day,0,datediff(day,0,T1.date))
and T2.date < T1.date
group by T1.task, T1.date
Antonio Macedo
-
Those queries aren't equivalent. The column you are calling 'task' is supposed to be unique. So your use of it in the subquery should mean that the query returns no records.
It is true that correleated subqueries sometimes give better performance though, especially when they are of the 'TOP 1' variety.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Not in this case.
If to make queries identical:
IF Object_ID('[test_dt]') IS NOT NULL
DROP TABLE [test_dt]
CREATE TABLE [test_dt](
Task nvarchar(50),
date datetime)
INSERT INTO [test_dt]
SELECT 'Inspection 1', '2006-07-14 09:00'
UNION
SELECT 'Inspection 2', '2006-07-14 09:15'
UNION
SELECT 'Inspection 4', '2006-07-14 09:50'
UNION
SELECT 'Inspection 3', '2006-07-14 11:20'
UNION
SELECT 'Inspection 6', '2006-07-14 11:23'
UNION
SELECT 'Inspection 10', '2006-07-14 11:39'
UNION
SELECT 'Inspection 7', '2006-07-14 12:30'
UNION
SELECT 'Inspection 5', '2006-07-14 14:22'
CREATE CLUSTERED INDEX T_Date ON [test_dt](date)
CREATE INDEX T_Task ON [test_dt](Task)
SELECT [task]
,[date]
,[date] - (select top 1 [date]
from [test_dt]
where date >= dateadd(day,0,datediff(day, 0, main.date))
and [date] < main.[date]
order by [date] desc) as timegap
FROM [test_dt] as main
--Left Join Solution
select T1.task,
T1.date,
T1.date - max(T2.date) TimeGap
from test_dt T1
left join test_dt T2 on T2.date < T1.date
and T2.date >= dateadd(day,0,datediff(day,0,T1.date))
group by T1.task, T1.date
DROP TABLE [test_dt]
it takes:
Correlated Subquery = 50.09%
Left Join = 49.91%
But my experience does not allow me to trust the numbers from execution plan.
So I put it into real performance test.
DECLARE @I int
SET @I = 1000
WHILE @I > 0
BEGIN
SELECT [task]
,[date]
,[date] - (select top 1 [date]
from [test_dt]
where date >= dateadd(day,0,datediff(day, 0, main.date))
and [date] < main.[date]
order by [date] desc) as timegap
FROM [test_dt] as main
SET @I = @I-1
END
--Left Join Solution
--DECLARE @I int
SET @I = 1000
WHILE @I > 0
BEGIN
select T1.task,
T1.date,
T1.date - max(T2.date) TimeGap
from test_dt T1
left join test_dt T2 on T2.date < T1.date
and T2.date >= dateadd(day,0,datediff(day,0,T1.date))
group by T1.task, T1.date
SET @I = @I-1
END
Result:
Correlated Subquery = 18 sec
Left Join = 12 sec
_____________
Code for TallyGenerator -
Hi this query was for SQL Server Mobile and it doesn't make use of Sub-queries or TOP -
Thanks
Tryst
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply