Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

compare SQL syntax but need the total and %??? Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 1:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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.
Post #1468957
Posted Monday, July 1, 2013 11:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
Somebody a Answer
Post #1469170
Posted Monday, July 1, 2013 11:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
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 Moden's 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)
Post #1469175
Posted Monday, July 1, 2013 12:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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
Post #1469179
Posted Monday, July 1, 2013 12:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
(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 Moden's 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)
Post #1469185
Posted Monday, July 1, 2013 1:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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
Post #1469208
Posted Monday, July 1, 2013 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469216
Posted Monday, July 1, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
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 Moden's 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)
Post #1469219
Posted Tuesday, July 2, 2013 1:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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
Post #1469322
Posted Wednesday, July 3, 2013 8:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
ANSWERS
Post #1470079
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse