我有一个自定义SQL查询,该查询从“上个月”返回信息,现在我需要将日期范围从上个月更改为.....上个月的19日至当月的20号。
这就是显示“过去一个月”的方法,任何人都可以帮助您修改它以显示所需的日期范围。
SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
SUM(InterfaceTraffic.In_TotalBytes) AS SUM_of_Total_Bytes_Received,
SUM(InterfaceTraffic.Out_TotalBytes) AS SUM_of_Total_Bytes_Transmitted,
SUM((NullIf(In_TotalBytes,-2)+NullIf(Out_TotalBytes,-2))) AS SUM_of_TotalBytesRecvXmit,
Interfaces.Gig_Limit AS Gig_Limit,
SUM((NullIf(In_TotalBytes,-2)+NullIf(Out_TotalBytes,-2))) - (Interfaces.Gig_Limit * 1073741824) AS Diff
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
WHERE
( DateTime >= dateadd(mm,datediff(mm,0,getdate())-1,0) AND DateTime
<dateadd(mm,datediff(mm,0,getdate()),0) )
AND
(
(Interfaces.Caption LIKE '%SM%') OR
(Interfaces.Caption LIKE '%County Snow%')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption, Interfaces.Caption, Interfaces.Gig_Limit
Having SUM((NullIf(In_TotalBytes,-2)+NullIf(Out_TotalBytes,-2))) - (Interfaces.Gig_Limit * 1073741824) > 0
ORDER BY SummaryMonth ASC, 6 DESC
谢谢
戴夫
最佳答案
WHERE (DateTime >= 18+dateadd(mm,datediff(mm,0,getdate())-1,0)
AND DateTime < 19+dateadd(mm,datediff(mm,0,getdate()),0))
我从字面上看就是
19th of LAST to 20th of CURRENT
,尽管从倒数第20位到第19位可能更有意义。调整数字很容易。关于sql - 具有自定义日期范围的SQL查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12878622/