December 12, 2011 at 12:48 pm
I need to write a query that will select from a history table the results of the last 5 weeks, and display the time as follows:
query to see Info A from history table, the job starts on say sysdate (today) @ 12:00 EST ...here is how I need to see the data retrieved:
week 1 - 5 @ 11:30 - 12:30 - so essentially I need to see -30 minutes, and +30 minutes value returned
I have not had to do this before, I am not exactly sure how to get this done.
Thanks,
Austin
December 12, 2011 at 12:51 pm
I'm not clear on what you need. Are you just looking for a way to determine 30 minutes before and after the current time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 12:55 pm
Yes, when I pull the data from the history table for the previous 5 weeks, I will need to see the date with a time stamp that is within +/-30 minutes. So if a job runs every 5 minutes, 23 hours day, and I need to pull this info from a history table, who would I pull that data so that it showed only the data from say 12:00 is kick off, 11:30 to 12:30.
December 12, 2011 at 1:06 pm
If you have a Numbers table, you can do something like this:
SELECT DATEADD(DAY, Number, DATEADD(week, -5, CAST(GETDATE() AS DATE))),
DATEADD(MINUTE, (12 * 60) - 30,
CAST(DATEADD(DAY, Number,
DATEADD(week, -5, CAST(GETDATE() AS DATE))) AS DATETIME)),
DATEADD(MINUTE, (12 * 60) + 30,
CAST(DATEADD(DAY, Number,
DATEADD(week, -5, CAST(GETDATE() AS DATE))) AS DATETIME))
FROM dbo.Numbers
WHERE Number BETWEEN 0 AND 35 ;
If you're using SQL 2005 instead of SQL 2008, and can't use the Date datatype, it would be:
DECLARE @Today DATETIME ;
SET @Today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) ;
SELECT @Today ;
SELECT DATEADD(DAY, Number, DATEADD(week, -5, @Today)),
DATEADD(MINUTE, (12 * 60) - 30,
DATEADD(DAY, Number, DATEADD(week, -5, @Today))),
DATEADD(MINUTE, (12 * 60) + 30,
DATEADD(DAY, Number, DATEADD(week, -5, @Today)))
FROM dbo.Numbers
WHERE Number BETWEEN 0 AND 35 ;
The first calculation uses DateAdd and the Numbers table value to get all the dates in the last 5 weeks.
The second and third calculations add a number of minutes to that. I left the math in to make it more clear how many minutes, instead of hard-coding it. If you want a different hour than noon, just change the 12 to whatever time you need (but use "military time", so 1 PM = 13, and so on).
There are more complex but more readable ways to do this, using strings to add the time range, but this is faster since computers do numbers better than strings.
If you don't have a Numbers table (also called a Tally table, though it doesn't actually tally anything), you can find out about them with a simple online search. They're very useful for this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 1:11 pm
Would this also apply to Oracle? Sorry, I should have posted that in the beginning of the thread.
December 12, 2011 at 1:20 pm
austin.wimberly (12/12/2011)
Would this also apply to Oracle? Sorry, I should have posted that in the beginning of the thread.
You wouldn't use GetDate in Oracle, since it's a T-SQL extension. I think Oracle uses something like "CURRENT_TIMESTAMP" instead. Syntax on the DateAdd function would also need to be confirmed. Should otherwise work, but I'm not an Oracle expert (heck, I'm not even an Oracle newbie; more like an Oracle I've-Heard-Of-It), so test it and see.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply