Simple question - help me modify this SQL code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple question - help me modify this SQL code

Author

Topic

klc2009
Starting Member

3 Posts

Posted - 2009-03-02 : 12:12:31

This will be very easy for someone to answer, I'm certain, but reading SQL code makes me dizzy....

The following code selects all of the interfaces in our database and returns several pieces of information for each one and throws them all into one report. I need to modify this code so that it only returns these values for one or a small number of interfaces that I will choose by InterfaceId. I'm guessing I need a WHERE clause in there somewhere, but I have not been able to figure out where to put it.

I would also like to add a field to my report that is called Interfaces.InBandwidth in the db table. I'm not sure where to put that either.

Here's the code.....

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.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(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

Thank you!
klc2009

SQLforGirls
Starting Member

48 Posts

Posted - 2009-03-02 : 12:31:29

You haven't included the entire SELECT statement here. Perhaps you know that.... The structure of the SELECT goes like this:

SELECT
[column names]
FROM
[table1]
INNER JOIN
[table2] on [table1.column=table2.column]
INNER JOIN
[table3] on [table1.column=table3.column]
WHERE
[column=constant, column=column, etc.]

The tables that are being joined in this query are: Interfaces, RESULT_IN (derived table, meaning it is defined by the first SELECT from InterfaceTraffic), RESULT_OUT (derived table defined by the second SELECT from InterfaceTraffic)

Your second question first: In order to add a column from the Interfaces table to your output, you would simply add it to the column list in the first section of your SELECT. It doesn't matter where in the SELECT list you add it, but here is one possibility:

SELECT Interfaces.InterfaceId,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Interfaces.InBandwidth AS Interface_InBandwidth, --

NOW, in order to filter your results on 1 or more InterfaceIds, you first need to identify the location of your WHERE clause (not pictured here). The script you posted ends with INNER JOIN, which means you have 1 or more additional tables that are being referenced in your complete script. You need to find the end of the last table that is being joined. At that point you may or may not have a WHERE clause already in existence. If so, you would add to the end of your WHERE clause:

[WHERE .....}
AND Interfaces.InterfaceId = 123

----or------

[WHERE .....]
AND Interfaces.InterfaceID in (123, 234, 456)

I am assuming your InterfaceID column is an Integer datatype. If not, if the InterfaceID is varchar (alphanumeric) then you need to put each of your values into single quotes.

Finally, if you have any concern about WHERE the WHERE clause is, please try again at posting the entire SELECT statement and we can help locate it for you.

:)

Hope that helps.

klc2009
Starting Member

3 Posts

Posted - 2009-03-02 : 12:51:45

Sorry, I missed the last part during copy/paste. Yes, I need to know where to put the WHERE and the syntax for one InterfaceId as well as the syntax for several InterfaceIds. Thank you so much for your quick response!

Here it is in it's entirety:

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.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(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

Thank you!
klc2009

klc2009
Starting Member

3 Posts

Posted - 2009-03-02 : 13:05:36

Nevermind my last post, I was able to figure it out from your reply. Thank you very much!

Thank you!
klc2009

SQLforGirls
Starting Member

48 Posts

Posted - 2009-03-02 : 15:23:59

Glad to hear it's making sense.

Just to be clear based on your complete query, the WHERE clause would go directly before the ORDER BY.

And since you don't have an existing WHERE, you would just add it like this:

WHERE Interfaces.InterfaceId = 123
ORDER BY NodeName, Interface_Caption

or

WHERE Interfaces.InterfaceId in (123, 234, 456)
ORDER BY NodeName, Interface_Caption

or (if InterfaceId is a varchar datatype):

WHERE Interfaces.InterfaceId in ('123', 'ABC', 'pdq')
ORDER BY....

Have fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources