5 Replies Latest reply on Nov 14, 2013 12:48 PM by Lawrence Garvin

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

    cczech

      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

        • Re: Is there a way to progmatically pull data between X dates?
          Lawrence Garvin

          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.

          1 of 1 people found this helpful
            • Re: Is there a way to progmatically pull data between X dates?
              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?

                • Re: Is there a way to progmatically pull data between X dates?
                  Lawrence Garvin

                  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.


                • Re: Is there a way to progmatically pull data between X dates?
                  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?

                    • Re: Is there a way to progmatically pull data between X dates?
                      Lawrence Garvin
                      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  ....