| Author |
Topic |
|
rkim
Starting Member
1 Post |
 Posted - 2010-01-15 : 17:10:33
|
| I have the following sql statement in order to calculate the network traffic report. But I just want to calculate 8 am to 5pm of Monday - Friday only. Can anyone know which statement(s) have to be added or removed to do that??Thank you in advance.SELECT TOP 10000 DatePart(Week,DateTime) AS SummaryWeek,Nodes.Caption AS NodeName,Interfaces.InterfaceName AS Interface_Name,Interfaces.InterfaceIcon AS Interface_Icon,AVG(Case InBandwidth When 0 Then 0 Else (In_Averagebps/InBandwidth) * 100End) AS AVERAGE_of_Recv_Percent_Utilization,AVG(Case OutBandwidth When 0 Then 0 Else (Out_Averagebps/OutBandwidth) * 100End) AS AVERAGE_of_Xmit_Percent_UtilizationFROM (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)WHERE ( DateTime BETWEEN 40176 AND 40183.7916666667 )AND ( (Interfaces.InterfaceName = 'Serial0/0/0') AND)GROUP BY DatePart(Week,DateTime), Nodes.Caption, Interfaces.InterfaceName, Interfaces.InterfaceIconORDER BY SummaryWeek ASC |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-01-18 : 22:42:07
|
add this to your where clause:and datepart(weekday, MyDateCol) in (1,2,3,4,5) -- mon thru friand datepart(hour, MyDateCol) between 8 and 16 -- 8:00am thru 4:59pm
elsasoft.org |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-18 : 23:15:17
|
quote:
WHERE ( DateTime BETWEEN 40176 AND 40183.7916666667 )
Try to use human reader form of date (like ISO format of YYYYMMDD) rather than numbersWHERE DateTime >= '20091231' AND DateTime <= '20100107 19:00'
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-01-19 : 18:37:07
|
it's very weird to have a column named DateTime, since that's a type name. Is the col really named DateTime?
elsasoft.org |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Sean Frost
Starting Member
19 Posts |
Posted - 2010-01-20 : 07:33:43
|
quote:
Originally posted by X002548
SELECT TOP 100,000????You da man....or is it chick?In any case...hope you got a lot of Mamories...ummmm...memoryPlease...do tell...what you plan to do with that....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
More often than not, you see select top 100000 or select top 10 trillion when people want to create a view that returns a record set that is ordered. Of course, SQL 2005 "fixed" that feature.Another reason for doing this may be for regulatory or compliance reasons - to save into another archive table or even to print it out to comply with legal requirements.Or, it just might be lack of familiarity with SQL. When I started learning SQL, I had no idea that you could do anything other than "select *" to select data from a table. Then, one day the light went on when I saw someone else explicitly listing the columns that they wanted to select.Expressing sarcasm without knowing the business reasons or skill levels of the OP for doing what s/he is doing, even when it appears out of the norm is really uncalled for. Even more so when the response contains no constructive or useful information that answers the question. Coming from well-respected and knowledgeable members of this forum makes it much worse. That type of responses make this forum a less welcoming place, much more so for those of us who are new, many of whom are reluctant to post questions for fear of exposing their inadequate language skills and/or SQL skills. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-01-20 : 09:06:49
|
>> Coming from well-respected and knowledgeable members of this forum makes it much worseBrett is well respected? yea right. :)
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-01-20 : 09:10:54
|
>> when people want to create a view that returns a record set that is orderedbtw - if you are relying on any particular view to return its rows ordered by using TOP 100 PERCENT or some other trick, you are setting yourself up for trouble. order is only guaranteed if there is an order by in the query that targets the view. see: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
elsasoft.org |
|
|
|
Sean Frost
Starting Member
19 Posts |
Posted - 2010-01-20 : 11:32:40
|
| jezemine, thank you for that link. I thought that trick worked prior to SQL 2005, but the blog seems to indicate that even in SQL 2000, it is not guaranteed.Regardless, the point I was trying to make was that every query or question that may appear to be ill-conceived or even silly to an expert could have been created by a novice after much struggle and efforts. They are turning to the forum to tap into the collective expertise that thankfully is in abundance on this forum. So it behooves the people who respond to the questions to treat the novices, as perhaps the OP is - it is his/her very first posting - with kindness and compassion rather than sarcasm and holier than thou attitude.If a question or query seems silly or annoys someone, they always have the option to not respond to that question. Leave it for someone else to answer. That cliched maternal advice about not saying anything at all if you have nothing good to say is perhaps just as applicable here.Please don't get me wrong, I am grateful and amazed at the high degree of skill and the friendly and polite disposition of most people on this forum. Even though I haven't posted much, I have followed this forum for a while, and have learned so much from many of the people here. |
|
|
|
|
Help - New on SQL