Custom reports grief

njoylif

SQL questions for custom reports...The only lines I've added is in the WHERE section for <NodeID LIKE> part, which is a valid SQL query, but it causes the report to come back empty...any thoughts??? 

Also, I'm fairly new at SQL and the DateTime in the WHERE command is killing me.  If I remove that and put something in like <AND   (  Convert(DateTime, Floor(Cast(DateTime as Float)), 2) LIKE '%Jul%' )> with the <NodeID LIKE> part it works.  SEE BELOW and thanks. 

SELECT Convert(DateTime,Floor(Cast(DateTime as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
Interfaces.PhysicalAddress AS Physical_Address

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
( DateTime BETWEEN 37993 AND 38000.625 )
 AND 
(
  NOT (Interfaces.PhysicalAddress > '')
)
  AND
(
  Nodes.NodeID LIKE '44%' 
)

GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)),0),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon, Interfaces.PhysicalAddress

ORDER BY SummaryDate ASC, 4 ASC, 6 ASC

njoylif

I think I figured this out, at least in part...using getdate() and DateAdd....The WHERE clause with HostName was to limit results for server crunching conservation.  This report gives me the last 7 days of MAX and AVG for interfaces on the Nodes.  Needs much work, but got over the date Hurdle.

SELECT Convert(DateTime,Floor(Cast(DateTime as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
Nodes.HostName AS HostName,
Interfaces.InterfaceID AS InterfaceID,
Interfaces.Caption AS Caption,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
( DateTime BETWEEN (Dateadd(d,-7,getdate())) and getdate() )
 AND   ( Nodes.HostName LIKE 'R-XRT-[Aa-Dd]%'  )
 AND    (  NOT (Interfaces.PhysicalAddress > '')  )

GROUP BY
Nodes.HostName, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.Caption, Convert(DateTime, Floor(Cast(DateTime as Float)), 0)

ORDER BY SummaryDate ASC, 4 DESC, 5 DESC