July 1, 2013 at 1:32 am
Hello SQL GuRu's,
A few weeks ago I asked the following (http://www.sqlservercentral.com/Forums/Topic1459631-150-1.aspx?Update=1).
For this I had received a clear answer and a good syntax that works nicely. Only I wish now that he takes the total of the syntax (item_keya) and is already compare network name. From the number of devices that come there must be a number of per cent will be given of how many devices are not seen in the database.
Here again my syntax;
SELECT
snetworkname,
REPLACE(sNetworkName,'.noc','')
FROM WhatsUp.dbo.NetworkInterface
RIGHT OUTER JOIN WhatsUp.dbo.device
ON WhatsUp.dbo.NetworkInterface.nNetworkInterfaceID = whatsup.dbo.device.nDefaultNetworkInterfaceID
WHERE REPLACE(sNetworkName,'.noc','') NOT IN (SELECT
REPLACE(item_keya,'.noc','') AS snetwork
FROM dbo.item
WHERE item_keya IS NOT NULL
AND item_keya <> '')
It so my total (item_keya) and that he looks what devices not found in the database to give a percentage number.
July 1, 2013 at 11:41 am
Somebody a Answer
July 1, 2013 at 11:49 am
The problem here is that we don't know what you want. We have no idea what your tables look like or the data. It is unclear what you expect as output. I read through your other thread and to be honest I am shocked that Lowell was able to put what he did based on what you posted.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2013 at 12:03 pm
I have made ??a comparison of query and snetworkname item_keya. Below the syntax:
SELECT
snetworkname,
REPLACE(sNetworkName,'.noc','')
FROM WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
RIGHT OUTER JOIN WhatsUp_Assyst_replicatie3.dbo.device
ON WhatsUp_Assyst_replicatie3.dbo.NetworkInterface.nNetworkInterfaceID = WhatsUp_Assyst_replicatie3.dbo.device.nDefaultNetworkInterfaceID
WHERE REPLACE(sNetworkName,'.noc','') NOT IN (SELECT
REPLACE(item_keya,'.noc','') AS NUMBER
FROM dbo.item
WHERE item_keya IS NOT NULL
AND item_keya <> '')
The syntax is the following:
ASR-AMF-SW-012.NOC
asr-amf-sw-200.noc
asr-utr-sw-118.noc
asr-woe-px-110.noc
ess-es1nl032itasa01.noc
ess-es1nl300nsoss001
ess-es1nl317ns1s001.noc
ess-es1nl317ns37s001.noc
Now I just I want to get a percentage as a percentage of how many devices from the result, eg the result;
item_keya / snetworkname * 100 = much percentage is not present or something ...
In Item_keya are all devices of customers and snetworkname all devices that have. Item_keya in
July 1, 2013 at 12:23 pm
(count(item_keya) / count(snetworkname)) * 100.0
or something ...
It seems that you missed the point of my previous post since none of this looks like any of the information that was requested.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2013 at 1:48 pm
I was not the person who made this table (thats why i cant tell you so much about it :S srry;
When i run the syntax
SELECT ((count(item_keya) / count(snetworkname)) * (100.0))
from item, NetworkInterface
it gives me a column 100.0 that is false.
Item_keya and snetworkname are nvarchar. Then i was trying with this;
SELECT (count(item_keya) / count(snetworkname)) * CAST(AVG(100) as Numeric(10, 2))
from item, NetworkInterface
where item_keya IS NOT NULL
AND item_keya <> ''
Then i get the following error;
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
I just wanne get the procent from total(item_keya) / Snetworkname * 100
July 1, 2013 at 2:19 pm
try this to avoid integer division:
if sql divides two integers , it returns an integer datatype, which can get rounded or truncated.
SELECT ((count(item_keya) * 1.0 / count(snetworkname) * 1.0) * (100.0))
from item, NetworkInterface
Lowell
July 1, 2013 at 2:36 pm
Oh good grief...I put my implicit conversion outside the division. Thanks Lowell. My intention was something like this:
(count(item_keya) / count(snetworkname) * 1.0) * 100.0
I was not the person who made this table (thats why i cant tell you so much about it :S srry;
But you are the one supporting it. Surely you know how to find a create table script for an existing table? If not, we will be happy help you. This is something you need to know how to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2013 at 1:19 am
The Query of Lowell works. Only now i get a value of the total of the table item_keya / snetworkname * 100.
But i use this qeury;
SELECT
sNetworkName,
REPLACE(sNetworkName,'.noc','')
FROM WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
RIGHT OUTER JOIN WhatsUp_Assyst_replicatie44.dbo.device
ON WhatsUp_Assyst_replicatie44.dbo.NetworkInterface.nNetworkInterfaceID = WhatsUp_Assyst_replicatie44.dbo.device.nDefaultNetworkInterfaceID
WHERE REPLACE(sNetworkName,'.noc','') NOT IN (SELECT
REPLACE(item_keya,'.noc','') AS NUMMER
FROM dbo.item
WHERE item_keya IS NOT NULL
AND item_keya <> '')
I want to integrate the qeury what Lowell made. The problem is when i run the qeury above it gives the results colmn (NO name) and i heard i cant use a AS colmn. How can i calculate the qeury what lowell made and but it in ...
The result what i get when i run the qeury above;
Snetworkname
bhg-voip-cm.noc
bhg-voip-g450-1.noc
bhg-voip-iptelfs-03.noc
bhg-voip-s8300-1.noc
bhg-voip-utility-01.noc
bhg-voip-vsp-1-01.noc
(No column name)
bhg-voip-cm
bhg-voip-g450-1
bhg-voip-iptelfs-03
bhg-voip-s8300-1
bhg-voip-utility-01
bhg-voip-vsp-1-01
I have try this syntax;
SELECT
sNetworkName,
REPLACE(sNetworkName,'.noc','')
FROM WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
RIGHT OUTER JOIN WhatsUp_Assyst_replicatie44.dbo.device
ON WhatsUp_Assyst_replicatie44.dbo.NetworkInterface.nNetworkInterfaceID = WhatsUp_Assyst_replicatie44.dbo.device.nDefaultNetworkInterfaceID
WHERE REPLACE(sNetworkName,'.noc','') NOT IN (SELECT
REPLACE(item_keya,'.noc','') AS NUMMER
FROM dbo.item
WHERE item_keya IS NOT NULL
AND item_keya <> '')
AND snetworkname = (select ((count(item_keya) * 1.0 / count(snetworkname) * 1.0) * (100.0)) from item, NetworkInterface
where item_keya IS NOT NULL
AND item_keya <> '')
But i getting a error;
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
because the two tables are nvarchars
July 3, 2013 at 8:20 am
ANSWERS
July 3, 2013 at 9:02 am
karim.boulahfa (7/3/2013)
ANSWERS
Please follow the link in my signature to find how to post your question in order to attract relevant and prompt ANSWERS 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply