Delta between records in one column
-
Hi I have a column where are values regarding to time. And In my query I want to add additional column where I will have delta between each record. So e.g. between one record and another remain 34 seconds. For this one I was trying to use DateDiff function, and compare my column with times to the same column but offset by one record. And there is the problem. It looks like date DateDIFF(seconds, column, [subquery]) and I have error that subquery return more than 1 value and it its not appropriate where are following; =, != or something like that. Do you have any ideas? how to manage this problem? Dnorus
-
Hi I have a column where are values regarding to time. And In my query I want to add additional column where I will have delta between each record. So e.g. between one record and another remain 34 seconds. For this one I was trying to use DateDiff function, and compare my column with times to the same column but offset by one record. And there is the problem. It looks like date DateDIFF(seconds, column, [subquery]) and I have error that subquery return more than 1 value and it its not appropriate where are following; =, != or something like that. Do you have any ideas? how to manage this problem? Dnorus
If you use a subquery to fetch the previous (or next) record you must ensure that only 1 record is fetched. So this means that you have to be able to order the rows based on some column and take only the first, for example using
TOP 1. So your query could look something like:SELECT ...
DATEDIFF(seconds,
datecolumn1,
(SELECT TOP 1 datecolumn2
FROM YourTable a1
WHERE ...
ORDER BY datecolumn2 DESC))
FROM YourTable
WHERE ...The need to optimize rises from a bad design.My articles[^]
-
If you use a subquery to fetch the previous (or next) record you must ensure that only 1 record is fetched. So this means that you have to be able to order the rows based on some column and take only the first, for example using
TOP 1. So your query could look something like:SELECT ...
DATEDIFF(seconds,
datecolumn1,
(SELECT TOP 1 datecolumn2
FROM YourTable a1
WHERE ...
ORDER BY datecolumn2 DESC))
FROM YourTable
WHERE ...The need to optimize rises from a bad design.My articles[^]
It isnt good solution. subquery returns one record for example first one. and Each record in datecolumn1 is compared to only one record from datecolumn2. I want to create some query that I will have data from datecolumn from ID 1 to 10, then create subquery to have data from the same datecolumn from ID 2 to 11, and then use datediff to obtain time delta between following records.
-
It isnt good solution. subquery returns one record for example first one. and Each record in datecolumn1 is compared to only one record from datecolumn2. I want to create some query that I will have data from datecolumn from ID 1 to 10, then create subquery to have data from the same datecolumn from ID 2 to 11, and then use datediff to obtain time delta between following records.
This is why I wrote WHERE ... inside the subquery. What you must do is to add necessary conditions into the subquery using correlation to the outer query. Since you didn't provide the query and the columns I'm just quessing the columns but try adding something like the following to the subquery
...
WHERE a1.datecolumn1 < a.datecolumn1
...A1 is the alias for you table in the subquery and A is alias for your table in the main query.
The need to optimize rises from a bad design.My articles[^]
-
This is why I wrote WHERE ... inside the subquery. What you must do is to add necessary conditions into the subquery using correlation to the outer query. Since you didn't provide the query and the columns I'm just quessing the columns but try adding something like the following to the subquery
...
WHERE a1.datecolumn1 < a.datecolumn1
...A1 is the alias for you table in the subquery and A is alias for your table in the main query.
The need to optimize rises from a bad design.My articles[^]
Oh it is working;) thx.. but it is working for to long.... maybe another way? something like:
....
Datediff(seconds, datecolumn, (Delete first row from datecolumn and add at the end null))Is there any possibility to do that? Because now I have almost the same searching in main query and subquery, and I think this main issue for this long working.
-
Oh it is working;) thx.. but it is working for to long.... maybe another way? something like:
....
Datediff(seconds, datecolumn, (Delete first row from datecolumn and add at the end null))Is there any possibility to do that? Because now I have almost the same searching in main query and subquery, and I think this main issue for this long working.
If it's taking a long time, it's probably because for each row in the main query a scan is done to the table to fetch the previous/next row. If possible, can you add an index to the table for the column you're using in the subquery. If several columns are used in the subquery condition consider having more than one column in the index.
The need to optimize rises from a bad design.My articles[^]
-
Hi I have a column where are values regarding to time. And In my query I want to add additional column where I will have delta between each record. So e.g. between one record and another remain 34 seconds. For this one I was trying to use DateDiff function, and compare my column with times to the same column but offset by one record. And there is the problem. It looks like date DateDIFF(seconds, column, [subquery]) and I have error that subquery return more than 1 value and it its not appropriate where are following; =, != or something like that. Do you have any ideas? how to manage this problem? Dnorus
You can build it on this principle.
WITH Ordered AS (
Select IDColumn,DateColumn,ROW_NUMBER() OVER(ORDER BY DateColumn) as rn
FROM YourTable
)
SELECT o2.IDColumn,DateDiff(seconds,o2.DateColumn - o1.DateColumn)
FROM ordered o1
join ordered o2
on o1.rn = o2.rn - 1 -
You can build it on this principle.
WITH Ordered AS (
Select IDColumn,DateColumn,ROW_NUMBER() OVER(ORDER BY DateColumn) as rn
FROM YourTable
)
SELECT o2.IDColumn,DateDiff(seconds,o2.DateColumn - o1.DateColumn)
FROM ordered o1
join ordered o2
on o1.rn = o2.rn - 1Okey it seems working and also in a very fast way. But I have a another question. In section With ordered as( ) I have some values declared... and now I dont know where can I put Declare and Set variable.. Ah Ok I didnt have ";".. before With or after declarations So now it is perfect;)
modified on Monday, March 28, 2011 4:59 AM