Mark Wiggans replied on Wed, Jan 7 2009 3:49 PM

rated by 0 users

Here is one from the Content Sharing Zone that addresses the last 30 days...

http://thwack.com/media/p/28301.aspx

robguent replied on Thu, Jan 8 2009 5:13 AM

rated by 0 users

Here is a good source for the different time frame selects:

http://databaseadvisors.com/pipermail/dba-sqlserver/2003-September/001109.html

***snippet of my SQL report, which calculates 2hrs Utilizations for the Last Month

-- Reporting time frame - Last Month
-- ReportPeriod specifies for which month  the report should be generated, 1 = Last Month, 2 = Month before Last Month
DECLARE @ReportPeriod int
     Set @ReportPeriod=1

SELECT 
 
Bandwidth='1.) Utilization/120min >= 80% for Bandwidth <=2Mbit/s', 
A_BWpre.Region,
A_BWpre.SPID,
A_BWpre.NodeName,
A_BWpre.VRF,
A_BWpre.Recv_Bandwidth,
A_BWpre.SummaryDateTime,
A_BWpre.AVERAGE_of_Recv_Percent_Utilization
 
FROM
 
 (SELECT
 
  Nodes.Caption AS NodeName,
  Interfaces.VRF AS VRF,
  AVG(Case InBandwidth When 0 Then 0
   Else (In_Averagebps/InBandwidth) * 100
   End) AS AVERAGE_of_Recv_Percent_Utilization,
  Nodes.Region AS Region,
  Nodes.SPID AS SPID,
  Interfaces.InBandwidth AS Recv_Bandwidth,
  ---- Average over 2hrs ....*12)/12
  Convert(DateTime,Floor(Cast(DateTime as Float)*12)/12,0) AS SummaryDateTime
 
 FROM
  (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
 
 WHERE
  -- Last Month
  DatePart(m, DateTime) = DatePart(m, DateAdd(m, -@ReportPeriod, getdate())) AND
  DatePart(yyyy, DateTime) = DatePart(yyyy, DateAdd(m, -@ReportPeriod, getdate()))

savell replied on Thu, Jan 8 2009 8:17 AM

rated by 0 users

Here's an SQL where statement I use for some of our monthly reports (returns date range for previous month)..

WHERE
( DateTime >= dateadd(mm,datediff(mm,0,getdate())-1,0) AND DateTime
<dateadd(mm,datediff(mm,0,getdate()),0) )

Dave.

Edit: very similar to the method in the above post now I look at it!

pchaves replied on Thu, Jan 8 2009 8:56 AM

rated by 0 users

Thanks Everyone for your help. I was able to get this working...finally.

THANKS AGAIN!

cnorborg replied on Thu, Jan 8 2009 2:50 PM

rated by 0 users

I hate replying to a post that is already solved, but I didn't see the method I use to try and figure out how the Solarwinds guys accomplish things in reports.

For example, in this case you want to know how to do a report for the previous month.  There are a ton of Orion reports that are for "Last Month" already and they work quite well, wouldn't it be great to know how they did it?  You can quite easily!!

Just open any report in Report Writer that has the feature you want in it, in this case one of the reports that has a timeframe of "Last Month".   Lets say the Availability report "Availability: Last Month" for instance.   Once you have the report opened, go to the "Report" menu and choose "Show SQL".  

At this point, another tab will open in the report you were just looking at that will show the exact SQL that is used to produce that report.

Makes it easy to learn off the professionals, a great way to get ideas on how to accomplish something!!

savell replied on Thu, Jan 8 2009 5:03 PM

rated by 0 users

It's a good tip and something I also use. However it's unfortunately not quite that simple in some instances.

Have a look at how the SQL looks when Report Writer generates the report - any datetime ranges are generated at the time the report is run (i.e. an absolute date range is generated rather than a relative one).

So you end up with something like the following for yesterday's data....

WHERE
( DateTime BETWEEN 39819 AND 39820 )

This is fine if the SQL is run today - but if the same statement is run tomorrow, you may be disappointed with the result...

Dave.

rgward replied on Thu, Jan 8 2009 8:22 PM

rated by 0 users

Try these.  I got them from Support a while back.

Replace the generated absolute statement like...

WHERE
( DateTime BETWEEN 39819 AND 39820 ) 

with...

For 'Last 24 Hours':

WHERE ColumnName >= GetDate () -1

For 'Last 7 Days':

WHERE DateTime >= GetDate () -7

For 'Last 30 Days':

WHERE DateTime >= GetDate () -30

For 'Last Month':

WHERE MONTH(ColumnName) = (MONTH(GETDATE()-DAY(GETDATE())))
AND YEAR(ColumnName) = YEAR(GETDATE()-DAY(GETDATE()))

For 'This Month':

WHERE MONTH(ColumnName) = (MONTH(GETDATE())) AND YEAR(ColumnName) = YEAR(GETDATE())

Replace 'ColumnName' with the name of the column that contains the DateTime information.  In most cases, it is DateTime, but some may vary.  You would want to check the table columns to get the appropriate column name.

pchaves replied on Fri, Jan 9 2009 12:36 PM

rated by 0 users

Thanks for the suggestions but looks like I'm on another stumbling block...

I am trying to pull data from the previous month but only during the hours of 8am to 5pm, Monday thru Friday.

Here is what I have but no matter what I put in the DatePart section I get the same results.

________________________________________________________________

WHERE
DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
 AND 
(
  (Interfaces.Caption LIKE '%Qwest%') OR
  (Interfaces.Caption LIKE '%COX-20MB%') OR
  (Interfaces.Caption LIKE '%One-Communications%') OR
  (Interfaces.Caption LIKE '%Verizon-ISP%')

 AND
(
   (DatePart(Hour,DateTime) >= 8) AND
   (DatePart(Hour,DateTime) <= 5) AND
   (DATEPART(weekday, DateTime) <> 1) AND
   (DATEPART(weekday, DateTime) = 7))
)

_________________________________________________________________

Anyone have any suggestions?

Thanks.

sedmo replied on Fri, Jan 9 2009 2:50 PM

rated by 0 users

I think the last line of your query should say <> 7 instead of = 7.

pchaves replied on Fri, Jan 9 2009 3:14 PM

rated by 0 users

Yes, you are right. I made the change and it didn't make a difference.... It seems like the entire DATEPART section is being ignored. If I remove it the results are the same.

sedmo replied on Fri, Jan 9 2009 4:33 PM

rated by 0 users

Here is how I have specified this sort of thing in my queries.

          (DATEPART(weekday, DateTime) <> 7)AND

          (DATEPART(weekday, DateTime) <> 1) AND

          (CONVERT(Char, DateTime, 108) > '0800') AND

          (CONVERT(Char, DateTime, 108) < '1700')

pchaves replied on Tue, Jan 13 2009 10:07 AM

rated by 0 users

Hi Sedmo,

Thanks for the suggestion, I seem to be getting different results with this but I don't believe it's correct.

What does the "108" relate to in the Convert lines?

Thanks

sedmo replied on Tue, Jan 13 2009 10:30 AM

rated by 0 users

108 is a type code that determines the format of the time.  In this case it returns HH:MM:SS.  Would it be possible for you to post your complete query?