Is there a way to progmatically pull data between X dates?

I've been asked to generate a report for NetFlow data, that will pull info for devices between the 16th of Month A to the 15th of Month B.

They'd like to also see weekly reports, finishing with a month end report.

This is the SQL commands for the data I am working with... the example below shows a 30 day interval, but I am not sure how it comes up with the numbers.

SELECT TOP 10000 FlowCorrelation.FullHostname AS Full_Hostname,
FlowCorrelation.IPAddress AS IP_Address,
Nodes.Caption AS NodeName,
SUM(NetflowEndpointsSummary.TotalBytesDst) AS SUM_of_Bytes_Transferred_Rx,
SUM(NetflowEndpointsSummary.TotalBytesSrc) AS SUM_of_Bytes_Transferred_Tx,
SUM(NetflowEndpointsSummary.TotalBytes) AS SUM_of_Bytes_Transferred_Total,
Nodes.NodeID AS NodeID
FROM
(NetflowEndpointsSummary LEFT OUTER JOIN FlowCorrelation ON (NetflowEndpointsSummary.IPSort = FlowCorrelation.IPAddressSort)) INNER JOIN Nodes ON (NetflowEndpointsSummary.NodeID = Nodes.NodeID)
WHERE
( DateTime BETWEEN 41553 AND 41584 )
AND
(
(FlowCorrelation.FullHostname LIKE '%mycingular.net%')
)
AND
(
(EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=InterfaceID AND NetFlowSources.Enabled=1))
)
GROUP BY FlowCorrelation.FullHostname, FlowCorrelation.IPAddress, Nodes.Caption, Nodes.NodeID
ORDER BY 6 DESC

Find more posts tagged with

monthly_report

endpoints

endpoint_traffic

netflow

monthly

bytes

mycingular.net

Accepted answers

All comments

LGarvin

WHERE ( DateTime BETWEEN 41553 AND 41584 )

This DateTime range is based on how SQL Server stores dates internally. All text date formats are simply presentations of a stored integer value. SmallDateTime values are stored as 4 byte integers (max value = 2^16 or 65536 days) and DateTime values are stored as 2 4-byte integers (max value = 2^32 or 4.2+ billion days). The day count for the DateTime value is zero based from January 1, 1900.

As such, the value 41553 is approximately 113.8 years which puts it somewhere in the second half of 2013. The second value is 31 days later, giving you a one month date range.

You demonstrate this using SQL with the command

SELECT DATEADD(day, 41553, 0) AS StartDate, DATEADD(day, 41584, 0) AS EndDate

which will return

StartDate	EndDate
2013-10-08 00:00:00.000	2013-11-08 00:00:00.000

If you're writing a report to use specific dates from last month to this month, you'll probably find it more useful to declare the WHERE clause with explicit date strings:

WHERE DateTime >= '20131016' AND DateTime < '20131116' -- returns all events dated Oct 16 thru Nov 15

or you can calculate the start date (16th of previous month) and end date (16th of current month), from the current date, so the WHERE filter is always relevant to the current date:

For example, we can obtain the first day of the current month using

DATEADD(month, DATEDIFF(month,0,GETDATE()), 0)

From there we can adapt that to obtain the 16th of the current month by adding 15 days using

DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) + 15

And using the calculated value for the first day of the current month, we can obtain the first day of the previous month by subtracting a month using

DATEADD(month, DATEDIFF(month,0,GETDATE())-1, 0)

And from there, we can adapt that to get the 16th of that month, by adding 15 more days using

DATEADD(month, DATEDIFF(month,0,GETDATE()), -1) + 15

which then gives us

WHERE DateTime >= DATEADD(month, DATEDIFF(month,0,GETDATE()), -1) + 15 AND DateTime < DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) + 15

which will always return a report that runs from the 16th of the previous month through the end of the 15th of the current month.

cczech

Thanks for the help... this I think points me in the right direction, but in my SQL 2008 R2 environment, when using the final calculation

WHERE DateTime >= DATEADD(month, DATEDIFF(month,0,GETDATE()), -1) + 15 AND DateTime < DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) + 15

It does not return any results... Any ideas?

cczech

Ended up doing this... and it looks like it's working now. I think...

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1) -15

SET @EndDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) +15

WHERE

( DateTime BETWEEN @StartDate AND @EndDate )

AND

....

Now, just to be sure I am pulling the data from the correct dates, is there a way to post the @StartDate, and @EndDate in the report, either in the title of the report, or in a column?

LGarvin

in my SQL 2008 R2 environment, when using the final calculation
WHERE DateTime >= DATEADD(month, DATEDIFF(month,0,GETDATE()), -1) + 15 AND DateTime < DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) + 15
It does not return any results... Any ideas?

Argh.. a transcription error when I copied the DATEDIFF() function calls.

This command is incorrect:

And from there, we can adapt that to get the 16th of that month, by adding 15 more days using
DATEADD(month, DATEDIFF(month,0,GETDATE()), -1) + 15

It should be

DATEADD(month, DATEDIFF(month,0,GETDATE())-1, 0) + 15

and that would make the full clause

WHERE DateTime >= DATEADD(month, DATEDIFF(month,0,GETDATE())-1, 0) + 15 AND DateTime < DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) + 15

SET @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1) -15

Minor technicality here... while this does work for this month, because October has 31 days, and thus Nov 1 minus 15 days is actually October 16th, it won't work next month, because November has only 30 days, so Dec 1 minus 15 days will actually be November 15th, not November 16th. In this case, it's critical to calculate the 16th of the month from the front of the start month rather than the front of the end month.

LGarvin

Now, just to be sure I am pulling the data from the correct dates, is there a way to post the @StartDate, and @EndDate in the report, either in the title of the report, or in a column?

Yes. You could force it into additional columns of the report on every row, such as

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) +15

SET @EndDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) +15

SELECT TOP 10000 @StartDate, @EndDate, FlowCorrelation.FullHostname AS Full_Hostname, FlowCorrelation.IPAddress AS IP_Address, Nodes.Caption AS NodeName,

SUM(NetflowEndpointsSummary.TotalBytesDst) AS SUM_of_Bytes_Transferred_Rx,

SUM(NetflowEndpointsSummary.TotalBytesSrc) AS SUM_of_Bytes_Transferred_Tx,

SUM(NetflowEndpointsSummary.TotalBytes) AS SUM_of_Bytes_Transferred_Total,

Nodes.NodeID AS NodeID

FROM (NetflowEndpointsSummary ....

just script it as two separate queries (really depends on what you're ultimately doing with the output

SELECT @StartDate AS StartDate, @EndDate as EndDate

SELECT TOP 10000 FlowCorrelation.FullHostname AS Full_Hostname, FlowCorrelation.IPAddress AS IP_Address, Nodes.Caption AS NodeName,

SUM(NetflowEndpointsSummary.TotalBytesDst) AS SUM_of_Bytes_Transferred_Rx,

SUM(NetflowEndpointsSummary.TotalBytesSrc) AS SUM_of_Bytes_Transferred_Tx,

SUM(NetflowEndpointsSummary.TotalBytes) AS SUM_of_Bytes_Transferred_Total,

Nodes.NodeID AS NodeID

FROM (NetflowEndpointsSummary ....

or you could force the @StartDate, @EndDate into Row 1 of the report by using a UNION ALL operator

SELECT @StartDate, @EndDate, '', '', '', '', '' -- SELECT statements in a UNION must have the same number of fields, so we add five empty fields to the first row

UNION ALL

SELECT TOP 10000 FlowCorrelation.FullHostname AS Full_Hostname, FlowCorrelation.IPAddress AS IP_Address, Nodes.Caption AS NodeName,

SUM(NetflowEndpointsSummary.TotalBytesDst) AS SUM_of_Bytes_Transferred_Rx,

SUM(NetflowEndpointsSummary.TotalBytesSrc) AS SUM_of_Bytes_Transferred_Tx,

SUM(NetflowEndpointsSummary.TotalBytes) AS SUM_of_Bytes_Transferred_Total,

Nodes.NodeID AS NodeID

FROM (NetflowEndpointsSummary ....