SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()

SELECT	
/*Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
*/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
		
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 /*inner join InterfaceTraffic on trafficstat.InterfaceID = InterfaceTraffic.InterfaceID
*/
WHERE 
   (Interfaces.Caption LIKE '%TO LA%') OR 
   (Interfaces.Caption LIKE '%TKM%') OR 
   (Interfaces.Caption LIKE '%TLKM%') OR 
   (Interfaces.Caption LIKE '%XL%') OR 
   (Interfaces.Caption LIKE '%TELKOM%') OR 
   (Interfaces.Caption LIKE '%MPLS%')

/*GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0), interfaces.InterfaceID, nodes.NodeID, nodes.Caption, nodes.VendorIcon, interfaces.caption, Interfaces.InterfaceIcon, trafficstat.Maxbps_95, trafficstat.Maxbps_In95, TrafficStat.Maxbps_Out95
*/
ORDER BY NodeName, Interface_Caption