SQL Query with a custom date range

Question

I have a custom SQL query that returns information from "Last Month" I now need to change the date range from last month to....the 19th of the previous month to the 20th of the current month.

This is what is working to show "Last month" Can anyone help with how to modify it to show the required date range.

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

Thanks Dave

Do you want to always apply one month filter using system's current date i.e. if query is run on 22nd March, you need to use 21st Feb to 22nd March filter ? — jags
– jags, Commented Oct 14, 2012 at 1:52

RichardTheKiwi · Accepted Answer · 2012-10-14 01:52:18Z

1

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

I'm taking you literally, i.e. 19th of LAST to 20th of CURRENT, although it would probably make more sense from 20th-last to 19th current. It's easy to tweak the numbers though.

answered Oct 14, 2012 at 1:52

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query with a custom date range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related