I am trying to create a new 95th Percentile traffic report that does the following each month for the last month:
1. Gives me the top ten only
2. Limits the report to Serial interfaces
How would I adjust the below SQL to do what I need?
TIA
klc2009
-----------------------------------------------------
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @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))
set nocount on
create table #tmpJoin (fromDate datetime, toDate datetime)
insert into #tmpJoin values(@StartDate,@EndDate)
set nocount off
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
FROM Nodes
INNER JOIN Interfaces
ON (Nodes.NodeID = Interfaces.NodeID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_IN
ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_OUT
ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_MAX
ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
ORDER BY NodeName, Interface_Caption
Drop table #tmpJoin
