|
안녕하세요. 저는 SQL 에 대해서는 아무것도 모르는 완전 초짜입니다.
네크워크 쪽에 일하고 있고 Solarwins라는 프로그램을 사용해서 네트워크 모니터를 합니다.
다음의 SQL 구문은 달(Month)에 쓰여지는 네트워크 사용량을 알아내는 다섯개중의 SQL Function중에
시간을 정하는 두개의 Function 구문입니다. 이 구문들은 달(Month) 로는 잘 돌아가고 있지만,
어떻게 하면 이 구문들을 주로(Week)로 바꿀수 있는지요???
/* Month Begin */
CREATE FUNCTION dbo.monthBegin( @monthPast integer ) RETURNS DateTime
AS
BEGIN
declare @ret datetime
set @ret = GetDate();
set @ret = DateAdd( hour,
-(datepart( hour, @ret ) ),
@ret )
set @ret = DateAdd( minute,
-(datepart( minute, @ret ) ),
@ret )
set @ret = DateAdd( second,
-(datepart( second, @ret ) ),
@ret )
set @ret = DateAdd( millisecond,
-(datepart( millisecond, @ret ) ),
@ret )
set @ret = DateAdd( day,
-(datepart( day, @ret ) -1 ),
@ret )
set @ret = DateAdd( month,
-@monthPast,
@ret )
return @ret
END
/* Month End */
CREATE FUNCTION dbo.monthEnd( @monthPast integer ) RETURNS DateTime
AS
BEGIN
declare @ret datetime
set @ret = dbo.monthBegin( @monthPast );
set @ret = DateAdd( month, 1, @ret );
set @ret = DateAdd( day, -1, @ret );
set @ret = DateAdd( hour, 23, @ret );
set @ret = DateAdd( minute, 59, @ret );
set @ret = DateAdd( second, 59, @ret );
set @ret = DateAdd( millisecond, 998, @ret );
return @ret
END
/*Business Hour*/
CREATE FUNCTION dbo.isBusinessHours(@time DATETIME, @offset INTEGER)
RETURNS INTEGER
AS
BEGIN
DECLARE @theDay INTEGER
DECLARE @theHour INTEGER
DECLARE @adjDate DATETIME
-- Adjust the date with the GMT offset.
SET @adjDate = DATEADD(hour, @offset, @time)
-- Get the day and make sure it's not Saturday or Sunday.
SET @theDay = DATEPART(day, @adjDate)
IF (@theDay = '1')
RETURN 0
IF (@theDay = '7')
RETURN 0
-- Get the hour and make sure it's between 8AM and 6PM.
SET @theHour = DATEPART(hour, @adjDate)
IF (@theHour < 8)
RETURN 0
IF (@theHour > 17)
RETURN 0
RETURN 1
END
/*
/*Inbound 95th*/
CREATE FUNCTION dbo.in95th (@node_name VARCHAR(30), @cir FLOAT, @start_time DATETIME, @end_time DATETIME)
RETURNS FLOAT
AS
BEGIN
DECLARE @value FLOAT
SELECT @value = MAX(In_Averagebps)
FROM (
SELECT TOP 95 PERCENT In_Averagebps
FROM
Nodes INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN InterfaceTraffic ON Interfaces.InterfaceID = InterfaceTraffic.InterfaceID
WHERE Nodes.Caption = @node_name
AND DateTime BETWEEN @start_time AND @end_time
AND dbo.isBusinessHours(DateTime, Interfaces.gmtOffset) = '1'
AND Interfaces.isOnUtilRept = '1'
AND Interfaces.CIR = @cir
ORDER BY InterfaceTraffic.In_Averagebps ASC
) AS AA
RETURN @value
END
/*Outbound 95th*/
CREATE FUNCTION dbo.out95th (@node_name VARCHAR(30), @cir FLOAT, @start_time DATETIME, @end_time DATETIME)
RETURNS FLOAT
AS
BEGIN
DECLARE @value FLOAT
SELECT @value = MAX(Out_Averagebps)
FROM (
SELECT TOP 95 PERCENT Out_Averagebps
FROM
Nodes INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN InterfaceTraffic ON Interfaces.InterfaceID = InterfaceTraffic.InterfaceID
WHERE Nodes.Caption = @node_name
AND DateTime BETWEEN @start_time AND @end_time
AND dbo.isBusinessHours(DateTime, Interfaces.gmtOffset) = '1'
AND Interfaces.isOnUtilRept = '1'
AND Interfaces.CIR = @cir
ORDER BY InterfaceTraffic.Out_Averagebps ASC
) AS AA
RETURN @value
RETURN @value
END
/*Monthly Report*/
SELECT DISTINCT
Interfaces.FunctionDesc AS FunctionDesc,
Nodes.SiteName AS SiteName,
Nodes.NodeID,
Nodes.Caption,
Interfaces.FunctionCode,
Interfaces.Burst as Burst,
Interfaces.CIR AS CIR,
dbo.in95th(Nodes.Caption, Interfaces.CIR, dbo.monthBegin(0), dbo.monthEnd(0)) / (Interfaces.CIR * 1048576) as in95th,
dbo.out95th(Nodes.Caption, Interfaces.CIR, dbo.monthBegin(0), dbo.monthEnd(0)) / (Interfaces.CIR * 1048576) as out95th,
dbo.in95th(Nodes.Caption, Interfaces.CIR, dbo.monthBegin(0), dbo.monthEnd(0))/1048576 as in95thMbps,
dbo.out95th(Nodes.Caption, Interfaces.CIR, dbo.monthBegin(0), dbo.monthEnd(0))/1048576 as out95thMbps
FROM
Nodes INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
WHERE
Interfaces.isOnUtilRept = '1'
ORDER BY
Interfaces.FunctionDesc, Nodes.SiteName
고수님들의 조언 부탁드립니다.! |
