Where query time between 6am and 6pm

  • spyfly

    Mr or Mrs. 500

    Points: 533

    I am trying to modify my query so that it will only look at the data between 6am and 6pm instead of the whole 24 hours, I have tried a few things but have had no luck

    Here is my current query

    SET NOCOUNT OFF
    SET ROWCOUNT 0

    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
    SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
    SELECT Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      Maxbps_In95,
      Maxbps_Out95,
      Maxbps_95,
                                    Interfaces.InterfaceSpeed AS Interface_Speed,
                                    Recieve_Percentage = (Maxbps_In95 / Interfaces.InterfaceSpeed ) * 100,
                                    Transmit_Percentage = (Maxbps_Out95 / Interfaces.InterfaceSpeed) * 100                               
    FROM Nodes
    INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
    INNER JOIN (
     SELECT InterfaceID,
       dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
       dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
       dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
     FROM InterfaceTraffic
     WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
     GROUP BY InterfaceID
    ) TrafficStat
    ON Interfaces.InterfaceID = TrafficStat.InterfaceID
    WHERE (1=1)  AND 
    ( (Nodes.TBU = 'TBU') AND (Interfaces.Link_Type LIKE '%Primary%') )
  • ThomasRushton

    SSC-Insane

    Points: 22591

    What I would do is firstly change your Start/EndDate calculations to form a bit simpler to understand:

    SET @EndDate = DATEADD(MONTH,DATEDIFF(MONTH,'20100101',GETDATE()),'20100101'); -- first of this month
    SET @StartDate = DATEADD(MONTH,-1,@EndDate); -- first of last month


    and then change the WHERE clause:

    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime < @EndDate
    AND DATEPART(HOUR,InterfaceTraffic.DateTime) >= 6 AND DATEPART(HOUR, InterfaceTraffic.DateTime) < 18

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • anthony.green

    SSC Guru

    Points: 112212

    You will need to convert the time aspect into a time only column then filter out where >= 6 and < 18, something like the below.

    SET NOCOUNT OFF
    SET ROWCOUNT 0
    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
    SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
    SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    Nodes.Caption AS NodeName,
    Nodes.VendorIcon AS Vendor_Icon,
    Interfaces.Caption AS Interface_Caption,
    Interfaces.InterfaceIcon AS Interface_Icon,
    Maxbps_In95,
    Maxbps_Out95,
    Maxbps_95,
    Interfaces.InterfaceSpeed AS Interface_Speed,
    Recieve_Percentage = (Maxbps_In95 / Interfaces.InterfaceSpeed ) * 100,
    Transmit_Percentage = (Maxbps_Out95 / Interfaces.InterfaceSpeed) * 100
    FROM Nodes
    INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
    INNER JOIN (
    SELECT InterfaceID,
    dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
    dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
    dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate AND CONVERT (time, InterfaceTraffic.DateTime) >= '06:00:00' AND CONVERT(time, InterfaceTraffic.DateTime) < '18:00:00'
    GROUP BY InterfaceID
    ) TrafficStat
    ON Interfaces.InterfaceID = TrafficStat.InterfaceID
    WHERE (1=1) AND
    ( (Nodes.TBU = 'TBU') AND (Interfaces.Link_Type LIKE '%Primary%') )



    How to post data/code for the best help - Jeff Moden[/url]
    Need a string splitter, try this - Jeff Moden[/url]
    How to post performance problems - Gail Shaw[/url]
    Managing Transaction Logs - Gail Shaw[/url]
    Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger[/url]

  • spyfly

    Mr or Mrs. 500

    Points: 533

    Perfect, I was missing the DATEPART, I was using DateTime that was my issue

    thanks

  • ChrisM@Work

    SSC Guru

    Points: 186040

    -- Here's how to get the time portion of your datetime.

    -- Note that the TIME function has a parameter, check it

    -- is correct for you using BOL

    SELECT CAST(GETDATE() AS TIME(0))

    -- Here's how to use it

    SELECT [TimePart]

    FROM (VALUES (

    CAST('05:00:00' AS TIME(0))),

    ('05:59:59'),

    ('06:00:00'),

    ('17:59:59'),

    ('18:00:00'),

    ('18:01:01')) d ([TimePart])

    WHERE [TimePart] >= '06:00:00' AND [TimePart] <= '18:00:00'

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • spyfly

    Mr or Mrs. 500

    Points: 533

    I have tried the solutions above and no matter what time frame I put in my data does not change.

    Here is my current query

    SET NOCOUNT OFF
    SET ROWCOUNT 0

    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
    SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
    SELECT
      Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      Maxbps_In95,
      Maxbps_Out95,
      Maxbps_95,
      Interfaces.InterfaceSpeed AS Interface_Speed,
      round((Maxbps_In95/Maxbps_95*100),0) as In_Percent,
      round((Maxbps_Out95/Maxbps_95*100),0) as Out_Percent
                                
    FROM Nodes
    INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
    INNER JOIN (
     SELECT InterfaceID,
       dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
       dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
       dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
     FROM InterfaceTraffic
     WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate  AND DATEPART(HOUR,InterfaceTraffic.DateTime) >= 8 AND DATEPART(HOUR, InterfaceTraffic.DateTime) < 14
     GROUP BY InterfaceID
    ) TrafficStat
    ON Interfaces.InterfaceID = TrafficStat.InterfaceID
    WHERE (1=1)  AND 
    ( (Nodes.TBU = 'MVA') AND (Interfaces.Link_Type LIKE '%Primary%') )
  • ChrisM@Work

    SSC Guru

    Points: 186040

    Have you checked that the inner query is working correctly?

    SELECT InterfaceID, [DateTime],

    dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,

    dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,

    dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95

    FROM InterfaceTraffic

    WHERE [DateTime] >= @StartDate

    AND [DateTime] <= @EndDate

    AND DATEPART(HOUR,[DateTime]) >= 8

    AND DATEPART(HOUR, [DateTime]) < 14

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ThomasRushton

    SSC-Insane

    Points: 22591

    I thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • spyfly

    Mr or Mrs. 500

    Points: 533

    ThomasRushton - Thursday, October 26, 2017 8:02 AM

    I thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...

    Thomas, I changed it around just testing... Honestly I was just using 06:00 and 18:00 as a baseline, but even if I sent the time to 11:00 & 12:00 it still shows the same percentage of usage at it does for a 24 hour day, so I know that data is incorrect it should be much less for a 1 hour window.

  • ThomasRushton

    SSC-Insane

    Points: 22591

    spyfly - Thursday, October 26, 2017 9:26 AM

    ThomasRushton - Thursday, October 26, 2017 8:02 AM

    I thought you were looking for records where the hour was between 06:00 & 18:00, but your query says between 08:00 & 14:00...

    Thomas, I changed it around just testing... Honestly I was just using 06:00 and 18:00 as a baseline, but even if I sent the time to 11:00 & 12:00 it still shows the same percentage of usage at it does for a 24 hour day, so I know that data is incorrect it should be much less for a 1 hour window.

    Hang on - are these the percentages that are calculated in the functions GetInBps95 etc?  Those functions appear to take a date range only - perhaps this is where the problem is?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • This was removed by the editor as SPAM

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply