Hi All,
I need some help on the following SQL Query below which I have been given by the DB guys, I am using the add command to build the table in CR11. It isnt what I want to see in my crystal report as its summarized.What I would like to see is the last three months data on a rolling basis or parameter driven but not summarized and including the DATETIME field.Thanks in advance for any help given and please let me know if you need anymore information.
DECLARE @StartDate DateTimeDECLARE @EndDate DateTimeSET @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.Caption AS NodeName,Nodes.VendorIcon AS Vendor_Icon, Interfaces.Caption AS Interface_Caption,Interfaces.InterfaceIcon AS Interface_Icon,Maxbps_In95,Maxbps_Out95,Maxbps_95FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_IN ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_OUT ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_MAX ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)--------------------- ORDER BY NodeName, Interface_Caption
I need some help on the following SQL Query below which I have been given by the DB guys, I am using the add command to build the table in CR11. It isnt what I want to see in my crystal report as its summarized.What I would like to see is the last three months data on a rolling basis or parameter driven but not summarized and including the DATETIME field.Thanks in advance for any help given and please let me know if you need anymore information.
DECLARE @StartDate DateTimeDECLARE @EndDate DateTimeSET @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.Caption AS NodeName,Nodes.VendorIcon AS Vendor_Icon, Interfaces.Caption AS Interface_Caption,Interfaces.InterfaceIcon AS Interface_Icon,Maxbps_In95,Maxbps_Out95,Maxbps_95FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_IN ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_OUT ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)---------------------INNER JOIN ( SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95 FROM ( SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate ) AS AA) as RESULT_MAX ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)--------------------- ORDER BY NodeName, Interface_Caption
