SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compare SQL syntax but need the total and %???


compare SQL syntax but need the total and %???

Author
Message
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
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.
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
Somebody a Answer
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62325 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62325 Visits: 17954

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

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
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
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71396 Visits: 40930
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62325 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
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
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 275
ANSWERS
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search