I am new to SQL and I come across a problem that it is giving me some headaches. I hope some of you can point me to the right direction.
I have a table with a lot of information with different rows referring to the same object but with different values for different features. To explain better her is an example:
Index Value
1001 Interface1
1002 Interface2
1003 Interface3
1001 Up
1002 Up
1003 Down
1001 1
1002 10
1003 20
The first column is just an ID and the second a parameter of an interface. First three rows is just the name for each interface, the next three rows will represent the status of each interface and the last three rows represents the number of customers connected to that interface.
What I am trying to do is a query that gives me all the interfaces that are down and have more than 0 customers, which means that if an interface goes down but it doesn't have customers I don't care but if the interface is down with at least one customer that should show on the query
What I am struggling to achieve is a way of combining all this data into one virtual table just for the purpose of a query and identify the condition I mentioned before.
So virtually the table should show something like this
Index Value Value Value
1001 Interface1 Up 1
1002 Interface2 Up 10
1003 Interface3 Down 20
And after the proper condition applied I should be able to see just this:
1003 Interface3 Down 20
I have been playing around with 'Join' but for some reason I am not getting this right. I have something like this:
SELECT name.Index, name.Value, state.Value, clients.Value FROM table1 AS name
INNER JOIN table1 AS state ON (name.Index = state.Index)
INNER JOIN table1 AS clients ON (state.Index = clients.Index)
WHERE (state.Value = 'Down' AND clients.value > 0);
So I am trying to join columns from the same table but with different rows which doesn't seem to work. Can anyone give me an idea how to acomplish this?
I created an identity column in the temp table, assuming that these records are inserted in the proper order and that there are only 3 records per 'index'.
Create table #test(id int identity(1,1),
[index] int,
[value] varchar(20))
insert into #test values
(1001,'Interface1'),
(1002,'Interface2'),
(1003,'Interface3'),
(1001,'Up'),
(1002,'Up'),
(1003,'Down'),
(1001,'1'),
(1002,'10'),
(1003,'20')
select [index], [value], [Status], CustomersConnected
from (
Select row_number() over (partition by [index] order by id) as id,
[index], [value],
lead([value],1,0) over(partition by [index] order by [index], id) as [Status],
lead([value],2,0) over(partition by [index] order by [index], id) as CustomersConnected
from #test) x
where x.id = 1
Thank you for your quick answer. I was trying to do this without creating any new table, just by using the existing one. The reason for this is that I am trying to apply a query into SolarWinds which doesn't allow me to create anything new, it just allows the query. I thought that join would not create anything new, just a virtual table to query which is only live during that specific query but I am not sure if this is the case.
There is no way to guarantee which row is identified for each value with only the columns shown. Given an index of 1001 - how do you know which of the 3 rows is the name or status or client count?
You need some way to identify each value type - then you can build a query that will return your expected results. For example:
Select
From dbo.YourTable nm
Inner Join dbo.YourTable st On st.index = nm.index
And st.Type = 'State'
Inner Join dbo.YourTable cl On cl.index = nm.index
And cl.Type = 'Client'
Where nm.Type = 'Name' -- limit to interface name row
And st.Value = 'Down'
And cl.Value > 0;
If you don't have anything - it may be possible to do something like:
With sortedRows
As (
Select *
, value_type = Case When try_cast(yt.Value As int) > 0 Then 'Client'
When yt.Value In ('Up', 'Down') Then 'State'
Else 'Name'
End
)
Select *
From sortedRows nm
Inner Join sortedRows st On st.Index = nm.Index
And st.value_type = 'State'
Inner Join sortedRows cl On cl.Index = nm.Index
And cl.value_type = 'Client'
Where nm.value_type = 'Name' -- limit to interface name
And st.Value = 'Down'
And cl.Value > 0;
The assumption here is that anything that can be converted/cast to an integer is your client count - anything with a value of 'Up' or 'Down' is your state - and everything else will be the interface name. You need to validate those rules though and modify the ordering so that your value_type is calculated appropriately.
Or let's just make what order they are in irrelevant
SELECT
MAX(CASE WHEN Value_Type = 'N' THEN Value END) AS Name_Value,
MAX(CASE WHEN Value_Type = 'S' THEN Value END) AS State_Value,
MAX(CASE WHEN Value_Type = 'C' THEN Value END) AS Client_Value
FROM #test
CROSS APPLY (
SELECT CASE WHEN Value NOT LIKE '%[^0-9]%' THEN 'C'
WHEN Value IN ('Down', 'Up') THEN 'S'
ELSE 'N' END AS Value_Type
) AS ca1
GROUP BY [Index]
HAVING MIN(CASE WHEN Value_Type = 'S' THEN Value END) = 'Down'
