cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

SQL Query Translation - DateTrunc

Hello,

 

Can someone please help me get this query adjusted for SQL statement? It works in a web-based report in Solar Winds but not in SQL.

 

DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())

 

The full query is:

SELECT
       Interfaces.InterfaceId,
       Nodes.NodeID,
       Nodes.Caption AS NodeName,
       Interfaces.Caption AS Interface_Caption,
       Interfaces.CustomProperties.Important_Interfaces AS Important_Ints,
       Maxbps_In,
       Maxbps_Out,
       Maxbps_In95,
       Maxbps_Out95,
       Maxbps_In99,
       Maxbps_Out99
     FROM Orion.Nodes
     INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID     INNER JOIN (
       SELECT
         OuterInterfaceTraffic.InterfaceID,
         (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
           SELECT TOP 100 PERCENT InAverageBps
           FROM Orion.NPM.InterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InAveragebps ASC )
           ) AS Maxbps_In,         (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
           SELECT TOP 100 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutAveragebps ASC )
           ) AS Maxbps_Out,
         (SELECT MAX(InAveragebps) as maxInMaxbps FROM (
           SELECT TOP 95 PERCENT InAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InMaxbps ASC )
           ) AS Maxbps_In95,
         (SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (
           SELECT TOP 95 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutMaxbps ASC )
           ) AS Maxbps_Out95,
         (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
           SELECT TOP 99 PERCENT InAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InAveragebps ASC )
           ) AS Maxbps_In99,         (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
           SELECT TOP 99 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutAveragebps ASC )
             ) AS Maxbps_Out99           FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON OuterInterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
           AND ONICP.Important_Interfaces = 'Field_WAN'
           GROUP BY OuterInterfaceTraffic.InterfaceID
         ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

 

Any help is greatly appreciated! Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: SQL Query Translation - DateTrunc

Hi @kbig02 

If you want to convert the following code to SQL code,

DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())

 

You could try the following SQL in SQL Server

BETWEEN 
DATEADD(MONTH,DATEDIFF(month, 0,GETUTCDATE() )-1,0) 
AND 
DATEADD(month, DATEDIFF(month, 0,GETUTCDATE() ), 0)

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: SQL Query Translation - DateTrunc

Hi @kbig02 

If you want to convert the following code to SQL code,

DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())

 

You could try the following SQL in SQL Server

BETWEEN 
DATEADD(MONTH,DATEDIFF(month, 0,GETUTCDATE() )-1,0) 
AND 
DATEADD(month, DATEDIFF(month, 0,GETUTCDATE() ), 0)

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: SQL Query Translation - DateTrunc

PERFECT! Thanks!

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors