November 17, 2011 at 2:35 pm
I am new here and am VERY new to any type of programming.
My database has a timepoint that incorporates data and time; e.g., 2010-12-01 17:00:00. I would like to search the name field and price field in my dbase for a certain hour; e.g. 17:00 in a certain month across several years and return the top values for that hour for all years. The return value is calculated; e.g., dPrice-rPrice AS Delta. I would like to also get the top values of the average delta.
This looks like an awful lot to me so if I could some help on just the time question, I would be able to keep working on the problem.
Chris
November 17, 2011 at 2:41 pm
It's actually a lot simpler than you think:
SELECT Col1, Col2, Col3...
FROM dbo.MyTable
WHERE DATEPART(hh, Col1) = 12
Just need to remember that in my example, the "hh" is looking at the hours of the day as 0-24 - so in the example above, this would be all records for "noon"
Does this help?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 17, 2011 at 2:48 pm
chris 81958 (11/17/2011)
I am new here and am VERY new to any type of programming.My database has a timepoint that incorporates data and time; e.g., 2010-12-01 17:00:00. I would like to search the name field and price field in my dbase for a certain hour; e.g. 17:00 in a certain month across several years and return the top values for that hour for all years. The return value is calculated; e.g., dPrice-rPrice AS Delta. I would like to also get the top values of the average delta.
This looks like an awful lot to me so if I could some help on just the time question, I would be able to keep working on the problem.
Chris
Sorry Chris, you kinda lost me here. Let me rephrase what I understand and see if you can help me fill in the blanks. Also, if you take a look at the first link in my signature, it will show you how to setup consumable data and the like that makes our lives easier. We're all volunteers.
There is an existing table with a single DATETIME column that also contains a price and a name field. What you're looking to do is filter the data to only a certain month, and within that month, a certain time/hour. From that result, you want the maximum value across all the data within the filter for price.
Now, you're going to have to explain what you mean by rPrice and dPrice to determine your deltas. Is rPrice the price now? Is it the lowest vs. highest? For the top values, is it top values by 'name', top values across all data, or something else?
So, a quick example of what the filter would look like. Mind you, this isn't something I'd want to run everyday because it's completely non-SARGable, but I don't know your year volumes so you have to do this to be generic.
-- This gets you your filtered starting point.
SELECT
name,
price
FROM
table
WHERE
MONTH( DateField) = 11 -- November as an example
AND Hour( DateField) = 17 -- From your example
-- To get the average from it:
SELECT
name,
MAX( price) AS MaxPrice
FROM
(SELECT
name,
price
FROM
table
WHERE
MONTH( DateField) = 11 -- November as an example
AND Hour( DateField) = 17 -- From your example
) AS drv
GROUP BY
name
-- Which can be combined once you're used to staring at SQL enough to:
SELECT
name,
MAX( price) AS MaxPrice
FROM
table
WHERE
MONTH( DateField) = 11 -- November as an example
AND Hour( DateField) = 17 -- From your example
GROUP BY
name
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2011 at 6:56 am
Dear "It's actually a lot simpler than you think:"
I did not see your name so...the quoted section. Yes this helps very much - thank yo for the quick reply. I really did not expect such fast attention and I really appreciate it. I will keep working on my problem and try out your code. I will not be able to do this until this afternoon but will write back.
Thanks again.
Chris
November 18, 2011 at 7:00 am
Dear Craig,
I wanted to write back and thank your for your efforts and quick reply before I went to your link. i appreciate your comment about being a volunteer and agree that it's only fair that I provide information in a way that is easy for you to understand. Let me take some time to digest the information on your link and then re-write my request. I do not believe that I did a good job communicating what it is that I would like to do.
I will write back this morning, if at all possible.
Thanks again.
Chris
November 18, 2011 at 7:13 am
Dear Craig,
I just read the "Forum Etiquette" section that you recommended. I can see how that would help a lot for both the poster and the helper. To write out my question in this form will take me sometime. I am in the process of reading a couple of books and learning from a friend. I doubt there that I have the knowledge today to provide this to you - however, with a little diligence, I think I can by next week. And since my goal is to learn SQL, this will be a help to me.
Thanks again and I will write back as soon as I think I have my question formed correctly.
Chris
November 18, 2011 at 1:10 pm
chris 81958 (11/18/2011)
Dear Craig,I just read the "Forum Etiquette" section that you recommended. I can see how that would help a lot for both the poster and the helper. To write out my question in this form will take me sometime. I am in the process of reading a couple of books and learning from a friend. I doubt there that I have the knowledge today to provide this to you - however, with a little diligence, I think I can by next week. And since my goal is to learn SQL, this will be a help to me.
Thanks again and I will write back as soon as I think I have my question formed correctly.
Chris
My pleasure. Your politeness and willingness to learn and try will take you far here.
Welcome.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply