July 2, 2010 at 9:40 am
(note: @time = application passes number of minutes)
Im using the following query and need help to filter my data
select distinct * from table name
where DATEDIFF(mi, time, getdate()) < @time
How do I take my set of results and only show the data that is between @time and 120minutes. I only need to display the last 2 hours of my results.
For example
This query would display ALL data in the table between the current time and 240minutes(4hours)
select distinct * from table name
where DATEDIFF(mi, time, getdate()) < 240
I then need to minus 120 minutes as I only want to display the last 120minutes 2Hours
Another example
select distinct * from table name
where DATEDIFF(mi, time, getdate()) < 480
I then need to minus 360 minutes as I only want to display the last 120minutes 2Hours
To summarise our application will pass us x amount of minutes using the @time variable,I don’t want to display all the data but only the last 120 minutes(2hours)of data
Can anyone help.
July 2, 2010 at 10:06 am
I am not sure this is specific enough to your question but the following would give you the date minus 240 minutes
select dateadd(mi,-240,Getdate())
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 2, 2010 at 10:50 pm
Hi,
this migh help
Select distinct * from table
Where Date =DATEADD(minute,-120,getdate())
July 6, 2010 at 3:54 am
I havent managed to resolve this yet. This is what I currently have, can anyone help – or have I got this comeplete wrong.
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_myprocedure]
@time int
AS
DECLARE @mytime int
If @time =120
BEGIN
select distinct * from v_mytable
where DATEDIFF(mi, time, getdate()) <= @time
order by time desc
END
else
if @time =240
BEGIN
select distinct * from v_mytable
WHERE DATEADD(mi, -120, getdate()) <= @time
order by time desc
END
else
if @time =360
BEGIN
select distinct * from v_mytable
WHERE DATEADD(mi, -240, getdate()) <= @time
order by time desc
END
else
if @time =480
BEGIN
select distinct * from v_mytable
WHERE DATEADD(mi, -360, getdate()) <= @time
order by time desc
END
I want to take my @time varable and minus a certain amount of minutes depending on the vairable passed.
July 6, 2010 at 4:23 am
Hey there. I can't quite understand what you want here. So, check this out
DECLARE @table AS TABLE(
TIME DATETIME)
INSERT INTO @table
SELECT '2010-07-01 09:21'
UNION ALL SELECT '2010-07-02 09:11'
UNION ALL SELECT '2010-07-03 09:31'
UNION ALL SELECT '2010-07-04 09:21'
UNION ALL SELECT '2010-07-05 09:11'
UNION ALL SELECT '2010-07-06 11:19'
DECLARE @time AS INT
SET @time = 120
IF @time = 120
BEGIN
SELECT DISTINCT *
FROM @table
WHERE Datediff(mi, TIME, Getdate()) <= @time
ORDER BY TIME DESC
END
ELSE
IF @time = 240
BEGIN
SELECT DISTINCT *
FROM @table
WHERE Dateadd(mi, -120, Getdate()) <= @time
ORDER BY TIME DESC
END
ELSE
IF @time = 360
BEGIN
SELECT DISTINCT *
FROM @table
WHERE Dateadd(mi, -240, Getdate()) <= @time
ORDER BY TIME DESC
END
ELSE
IF @time = 480
BEGIN
SELECT DISTINCT *
FROM @table
WHERE Dateadd(mi, -360, Getdate()) <= @time
ORDER BY TIME DESC
END
What is your expected result from running that? The actual result is shown below, but what do you want to display?
/*
RESULT
TIME
-----------------------
2010-07-06 11:19:00.000
*/
July 6, 2010 at 5:19 am
Still no luck with this, I will try to simplify in another example.
I want to get the current datetime (where DATEDIFF(minute,time, getdate()) ), then minus 240 minutes (I don’t know how to do this).
I want to take the new datetime to display all entries in my table that are less that or equal to 120 minutes.
So somewhere in the below t-sql I need to minus 240 minutes when SQL has retrieved the GETDATE part of the statement.
select distinct * from my-table-name
where DATEDIFF(minute,time, getdate()) <= 240
Order by time desc
July 6, 2010 at 5:58 am
Mr_Bacon (7/6/2010)
Still no luck with this, I will try to simplify in another example.I want to get the current datetime (where DATEDIFF(minute,time, getdate()) ), then minus 240 minutes (I don’t know how to do this).
I want to take the new datetime to display all entries in my table that are less that or equal to 120 minutes.
So somewhere in the below t-sql I need to minus 240 minutes when SQL has retrieved the GETDATE part of the statement.
select distinct * from my-table-name
where DATEDIFF(minute,time, getdate()) <= 240
Order by time desc
Minus 240 minutes from what? GetDate? The data in the column? The variable?
Please refer back to my previous post and tell me what your expected outcome would be from running the query.
July 6, 2010 at 6:41 am
Im struggling to explain this so I will try again
the following sql is my procedure, an application passes through the @time variable which is in minutes.
The below procedure works. for example, I would expect this query to return the following. Assuming the time is 13:00 and the @time variable is 240 (4Hours) I would expect all rows from v_myview to be returned between 09:00 and 13:00 – which it does.
CREATE PROCEDURE [dbo].[sp_myprocedure]
@time int
AS
DECLARE @mytime int
BEGIN
select distinct * from v_myview
where
DATEDIFF(mi, time, getdate()) < @time
order by time desc
END
GO
Another example: Assuming the time is 13:00 and the @time variable is 360 (6 Hours) I would expect all rows from v_myview to be returned between 07:00 and 13:00. Which they are.
Now the part I cannot crack. I don’t want to show ALL entries only the oldest 2 hours (120 minutes)
SO in my first example I only want to display between 09:00 and 11:00
In my second example I only want to display between 07:00 and 09:00
This is why I want to find out how to GETDATE() then minus X amount of minutes.
Im new to T-SQL so I might be completely wrong in my approach.
July 6, 2010 at 7:03 am
Mr_Bacon (7/6/2010)
Im struggling to explain this so I will try again
Hmmm.
Is this what you're after?
--Test Data
DECLARE @table AS TABLE(
TIME DATETIME)
INSERT INTO @table
SELECT '2010-07-01 09:21'
UNION ALL SELECT '2010-07-02 09:11'
UNION ALL SELECT '2010-07-03 09:31'
UNION ALL SELECT '2010-07-04 09:21'
UNION ALL SELECT '2010-07-05 09:11'
UNION ALL SELECT '2010-07-06 11:19'
UNION ALL SELECT '2010-07-06 12:19'
UNION ALL SELECT '2010-07-06 13:19'
--Declare time as 360
DECLARE @time INT
SET @time = 360
--Query
SELECT TOP 2 TIME
FROM @table
WHERE Datediff(mi, TIME, Getdate()) < @time
ORDER BY TIME ASC
*EDIT*
Nevermind, just re-read what you wrote and this isn't what you wanted. I'll look into it when I have some more free time.
*EDIT 2*
This gives you the oldest two records.
;WITH cte
AS (SELECT TIME,
Row_number() OVER(ORDER BY TIME ASC) AS theorder
FROM @table)
SELECT TIME
FROM cte
WHERE theorder <= 3
AND Datediff(mi, TIME, Getdate()) < @time
ORDER BY TIME ASC
*EDIT 3*
lol, I think I've been over-complicating it.
This will give you the records that are older than 2 hours.
SELECT TIME
FROM @table
WHERE Datediff(mi, TIME, Getdate()) < @time
AND Datediff(mi, TIME, Getdate()) > 120
ORDER BY TIME ASC
July 6, 2010 at 8:52 am
I think I have cracked it, might be overcomplicating things but the below seems to work, pending further testing. The below covers me for the 24 hour period required.
I just need to test. Thanks for your help.
USE [DATABASENAME]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_procedurename]
@time int
AS
DECLARE @mytime int
If @time <=120
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) <= @time
order by time desc
END
else
If @time =240
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 120 AND 240
order by time desc
END
else
if @time =360
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 240 AND 360
order by time desc
END
else
if @time =480
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 360 AND 480
order by time desc
END
else
if @time =600
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 480 AND 600
order by time desc
END
else
if @time =720
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 600 AND 720
order by time desc
END
else
if @time =840
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 720 AND 840
order by time desc
END
else
if @time =960
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 840 AND 960
order by time desc
END
else
if @time =1080
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 960 AND 1080
order by time desc
END
else
if @time =1200
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 1080 AND 1200
order by time desc
END
else
if @time =1320
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 1200 AND 1320
order by time desc
END
else
if @time =1440
BEGIN
select distinct * from v_myview
where DATEDIFF(minute,time, getdate()) between 1320 AND 1440
order by time desc
END
July 6, 2010 at 11:04 am
Mr_Bacon (7/6/2010)
I think I have cracked it, might be overcomplicating things but the below seems to work, pending further testing. The below covers me for the 24 hour period required.I just need to test. Thanks for your help.
You might find this a little bit easier to work with. Since every interval is with 120 minute periods, just do it like this:
declare @StartTime datetime,
@EndTime datetime
if @time <= 120 begin
set @EndTime = GETDATE()
set @StartTime = DATEADD(minute, @time*-1, GETDATE())
end else begin
set @EndTime = DATEADD(minute, (@time-120) * -1, GETDATE())
set @StartTime = DATEADD(minute, @time*-1, GETDATE())
end
select distinct *
from v_myview
where time BETWEEN @StartTime and @EndTime
order by time desc
By NOT using the DateDiff on the field being searched, this means that you can now utilize an index on the field. In order to do this, you need a date range to search on, hence the @StartTime and @EndTime variables. Since the queries are all the same (the only difference is the range being searched), this would make it simpler also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2010 at 11:22 am
declare @max-2 int
set @max-2 = 240
select *
from (
select '2010-01-01 10:00:00' [time]
union all
select '2010-01-01 11:00:00'
union all
select '2010-01-01 12:00:00'
union all
select '2010-01-01 13:00:00'
union all
select '2010-01-01 14:00:00'
) blah
where [time]
between dateadd(minute, -@max - 120, getdate())
and dateadd(minute, -@max, getdate())
you can't use datediff() like that - it will force a table scan
indexes only work with simple = <> > < type stuff
July 7, 2010 at 3:05 am
This is great. Thanks WayneS this worked a treat.
Doobya, thanks for your feedback. Just for my info what do you mean when you say ‘you can't use datediff() like that - it will force a table scan’
Thanks for all your help.
July 7, 2010 at 4:06 am
Mr_Bacon (7/7/2010)Doobya, thanks for your feedback. Just for my info what do you mean when you say ‘you can't use datediff() like that - it will force a table scan’
think of the operators that work with an index:
equals, not equals, greater than, less than, between
where datediff(minute, [field], getdate()) < 240
think what the query must do in order to calculate this ...
it has to scan every row in the table/index and calculate the result of the datediff() function
even though the optimizer is smart enough to only call getdate() once it still has to call datediff() for EVERY ROW
whereas if you change it to:
where [field] > dateadd(minute, -240, getdate())
it only has to calculate the result of dateadd() ONCE
and can then use that scalar result to efficiently seek the index - no more scanning
on a large table it could run 1000 times faster
July 7, 2010 at 5:02 am
It will work using between clause
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply