This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • 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

    StartDateEndDate
    2013-10-08 00:00:00.0002013-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.

  • 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?

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

    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?

  • 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.


  • 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  ....

    OR

    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  ....