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

How to make my number shorter?? Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
ChrisM@Work (5/21/2013)
karim.boulahfa (5/21/2013)
ChrisM@Work (5/21/2013)
karim.boulahfa (5/21/2013)
The valua is FLOAT of the column


FLOAT is the data type.
Can you show some sample data for the columns I listed?


select *
from StatisticalDisk

Thats are the rseult of the colmns valua's

nStatisticalDiskID Dpolltime stType nSize nUsed_Avg nUsed_min nUsed_max nTimedelta nDatatype nStatisticalDiskIdentificationID


18832367 2013-05-02 13:24:23.627 Fixed Disk 25801953280 11961458688 11961458688 11961458688 600,0029296875 1 254
18832368 2013-05-02 13:24:23.643 Fixed Disk 89769836544 36911964160 36911964160 36911964160 600,0029296875 1 255
18832369 2013-05-02 13:24:23.673 Fixed Disk 262824960 10730496 10730496 10730496 600,0029296875 1 256
18832370 2013-05-02 13:24:23.690 Fixed Disk 25801986048 79757312 79757312 79757312 600,0029296875 1 257


Its about nUsed_min/max and AVG


Thanks. What are the units? Bytes? You need to know this for your output column headers.


It gives the Bytes as Output.
Post #1454998
Posted Tuesday, May 21, 2013 7:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
Its bytes in colmn

And the units you see in de qeury before that he find the units in device.deviceid and name.
Post #1454999
Posted Tuesday, May 21, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
Well, OK then. If it's bytes, what do you want to display? Kilobytes is derived by dividing the number of bytes by 1024 because that's how many bytes makes up a kilobyte. If you want megabytes then you divide by 1024 again. Same again for gigabytes.

Just apply the business rules and logic to get the number to what you want for display purposes. Or, better still, leave it as is and let the front-end handle formatting.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1455027
Posted Tuesday, May 21, 2013 8:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
Grant Fritchey (5/21/2013)
Well, OK then. If it's bytes, what do you want to display? Kilobytes is derived by dividing the number of bytes by 1024 because that's how many bytes makes up a kilobyte. If you want megabytes then you divide by 1024 again. Same again for gigabytes.

Just apply the business rules and logic to get the number to what you want for display purposes. Or, better still, leave it as is and let the front-end handle formatting.


I want to see MB
Post #1455042
Posted Tuesday, May 21, 2013 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
This is What'sUp Gold data. What version of SQL Server is hosting it?

The left joins in the query will be converted into inner joins by the child table joins:

INNER JOIN pivotdevicetogroup pdg
ON pdg.nDeviceID = d.nDeviceID
INNER JOIN DeviceGroup dg
ON pdg.nDeviceGroupID = dg.nDeviceGroupID

You don't want nsize in the GROUP BY, if you want to SUM it in the output.

Use column aliases throughout so folks can see what tables your columns belong to.


Try something like this:
SELECT 
nDeviceID,
sDisplayName,
sgroupname,
sDescription,
dPollTime,
Minimaal_gebruik,
Maximaal_gebruik,
Totaal_Schijfruimte,
(SUM_nUsed_Min / Totaal_Schijfruimte) * 100.0 AS Ingebruik
FROM (
SELECT TOP 10
d.nDeviceID,
d.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min)/1048576 AS Minimaal_gebruik,
MAX(nused_Max)/1048576 AS Maximaal_gebruik,
CAST(SUM(nSize)/1048576 AS DECIMAL(10,2)) AS Totaal_Schijfruimte,
SUM(nUsed_Min/1048576) AS SUM_nUsed_Min
FROM dbo.StatisticalDisk sd

LEFT JOIN dbo.StatisticalDiskIdentification sdi
ON sdi.nStatisticalDiskIdentificationID = sd.nStatisticalDiskIdentificationID
LEFT JOIN dbo.PivotStatisticalMonitorTypeToDevice pm
ON pm.nPivotStatisticalMonitorTypeToDeviceID = sdi.nPivotStatisticalMonitorTypeToDeviceID
LEFT JOIN Device d
ON d.nDeviceID = pm.nDeviceID

INNER JOIN pivotdevicetogroup pdg
ON pdg.nDeviceID = d.nDeviceID
INNER JOIN DeviceGroup dg
ON pdg.nDeviceGroupID = dg.nDeviceGroupID

INNER JOIN [time]
ON sd.dPollTime = [time].PK_Date

WHERE sd.dPollTime = dateadd(MM, 1, '2013')
AND dg.sGroupName IN ('CUSTOMER')

GROUP BY
d.nDeviceID,
d.sDisplayName,
dg.sgroupname,
sDescription,
dPollTime--,
--nSize
ORDER BY dPollTime DESC
) d
WHERE (SUM_nUsed_Min / Totaal_Schijfruimte) * 100.0 >= 80




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1455085
Posted Wednesday, May 22, 2013 1:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
I dont get any valuas out the Query ??
Post #1455326
Posted Wednesday, May 22, 2013 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
karim.boulahfa (5/22/2013)
I dont get any valuas out the Query ??


It's very difficult to rework someone else's code without data to run the code against. That's why I wrote "Something like this", because I can't run the code to verify it. You will have to experiment with it to find out why no results are returned. Try running the inner query, try commenting out any filters. This shouldn't cost you more than a few seconds of your time.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1455331
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse