October 16, 2017 at 6:20 am
Here is my current query
SET NOCOUNT OFF
SET ROWCOUNT 0
October 16, 2017 at 6:40 am
What I would do is firstly change your Start/EndDate calculations to form a bit simpler to understand:
SET @EndDate = DATEADD(MONTH,DATEDIFF(MONTH,'20100101',GETDATE()),'20100101'); -- first of this month
SET @StartDate = DATEADD(MONTH,-1,@EndDate); -- first of last month
and then change the WHERE clause:
WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime < @EndDate
AND DATEPART(HOUR,InterfaceTraffic.DateTime) >= 6 AND DATEPART(HOUR, InterfaceTraffic.DateTime) < 18
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 16, 2017 at 6:49 am
You will need to convert the time aspect into a time only column then filter out where >= 6 and < 18, something like the below.
SET NOCOUNT OFF
SET ROWCOUNT 0DECLARE @StartDate DateTime
DECLARE @EndDate DateTimeSET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95,
Interfaces.InterfaceSpeed AS Interface_Speed,
Recieve_Percentage = (Maxbps_In95 / Interfaces.InterfaceSpeed ) * 100,
Transmit_Percentage = (Maxbps_Out95 / Interfaces.InterfaceSpeed) * 100
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
SELECT InterfaceID,
dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM InterfaceTraffic
WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate AND CONVERT (time, InterfaceTraffic.DateTime) >= '06:00:00' AND CONVERT(time, InterfaceTraffic.DateTime) < '18:00:00'
GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE (1=1) AND
( (Nodes.TBU = 'TBU') AND (Interfaces.Link_Type LIKE '%Primary%') )October 16, 2017 at 6:54 am
Perfect, I was missing the DATEPART, I was using DateTime that was my issue
thanks
October 16, 2017 at 6:58 am
-- Here's how to get the time portion of your datetime.
-- Note that the TIME function has a parameter, check it
-- is correct for you using BOL
SELECT CAST(GETDATE() AS TIME(0))
-- Here's how to use it
SELECT [TimePart]
FROM (VALUES (
CAST('05:00:00' AS TIME(0))),
('05:59:59'),
('06:00:00'),
('17:59:59'),
('18:00:00'),
('18:01:01')) d ([TimePart])
WHERE [TimePart] >= '06:00:00' AND [TimePart] <= '18:00:00'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2017 at 6:45 am
Here is my current query
SET NOCOUNT OFF
SET ROWCOUNT 0
October 26, 2017 at 7:09 am
Have you checked that the inner query is working correctly?
SELECT InterfaceID, [DateTime],
dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM InterfaceTraffic
WHERE [DateTime] >= @StartDate
AND [DateTime] <= @EndDate
AND DATEPART(HOUR,[DateTime]) >= 8
AND DATEPART(HOUR, [DateTime]) < 14
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2017 at 8:02 am
I thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 26, 2017 at 9:26 am
ThomasRushton - Thursday, October 26, 2017 8:02 AMI thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...
Thomas, I changed it around just testing... Honestly I was just using 06:00 and 18:00 as a baseline, but even if I sent the time to 11:00 & 12:00 it still shows the same percentage of usage at it does for a 24 hour day, so I know that data is incorrect it should be much less for a 1 hour window.
October 26, 2017 at 9:34 am
spyfly - Thursday, October 26, 2017 9:26 AMThomasRushton - Thursday, October 26, 2017 8:02 AMI thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...Thomas, I changed it around just testing... Honestly I was just using 06:00 and 18:00 as a baseline, but even if I sent the time to 11:00 & 12:00 it still shows the same percentage of usage at it does for a 24 hour day, so I know that data is incorrect it should be much less for a 1 hour window.
Hang on - are these the percentages that are calculated in the functions GetInBps95 etc? Those functions appear to take a date range only - perhaps this is where the problem is?
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 20, 2018 at 4:51 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy