Where query time between 6am and 6pm

Question

Where query time between 6am and 6pm

spyfly

Mr or Mrs. 500

Points: 593
More actions
October 16, 2017 at 6:20 am

#333266

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%') )
April 20, 2018 at 4:51 pm

This was removed by the editor as SPAM

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

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

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

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%') )

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 3

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

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 5

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: 186127 More actions · Answer 6

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 7

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: 593 More actions · Answer 8

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.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 9

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