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 3:58 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 have the following SQL syntax:

select TOP 10 Device.nDeviceID,
Device.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min) AS Minimaal_gebruik,
MAX(nused_Max) AS Maximaal_gebruik,
SUM(nSize) AS Totaal_Schijfruimte,

(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Ingebruik


from dbo.StatisticalDisk

left outer join dbo.StatisticalDiskIdentification on StatisticalDiskIdentification.nStatisticalDiskIdentificationID = StatisticalDisk.nStatisticalDiskIdentificationID

left outer join dbo.PivotStatisticalMonitorTypeToDevice on PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID = StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID

left outer join Device on Device.nDeviceID = PivotStatisticalMonitorTypeToDevice.nDeviceID
INNER JOIN pivotdevicetogroup ON PivotDeviceToGroup.nDeviceID = Device.nDeviceID
INNER JOIN DeviceGroup ON PivotDeviceToGroup.nDeviceGroupID = DeviceGroup.nDeviceGroupID
INNER JOIN time on StatisticalDisk.dPollTime = time.PK_Date
where StatisticalDisk.dPollTime = dateadd(MM, 1, '2013')
AND DeviceGroup.sGroupName IN ('CUSTOMER')
group by Device.nDeviceID,
Device.sDisplayName,
devicegroup.sgroupname,
sDescription,
dPollTime,
nSize
having (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
order by dPollTime desc

I get the following results

6804261376 6803189248 8496713728

But i need to make a short number like 68.04






Post #1454901
Posted Tuesday, May 21, 2013 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:00 AM
Points: 5,408, Visits: 10,052
Divide by 100000000.0 and cast as decimal.

John
Post #1454904
Posted Tuesday, May 21, 2013 4:09 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
These colmn most be shorter:

MIN(nUsed_Min) AS Minimaal_gebruik,
MAX(nused_Max) AS Maximaal_gebruik,
SUM(nSize) AS Totaal_Schijfruimte,

Can you tell me what you mean with your anwser? (Make a SQL syntax plz)
Post #1454906
Posted Tuesday, May 21, 2013 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:40 AM
Points: 6,778, Visits: 13,971
What are typical values for SUM(nUsed_Min) and SUM(nSize)?

Your query is easier to read if table aliases are used:
SELECT TOP 10 
d.nDeviceID,
d.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min) AS Minimaal_gebruik,
MAX(nused_Max) AS Maximaal_gebruik,
SUM(nSize) AS Totaal_Schijfruimte,

(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Ingebruik

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 = Device.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
HAVING (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
ORDER BY dPollTime DESC





“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 #1454907
Posted Tuesday, May 21, 2013 4:36 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)
What are typical values for SUM(nUsed_Min) and SUM(nSize)?

Your query is easier to read if table aliases are used:
SELECT TOP 10 
d.nDeviceID,
d.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min) AS Min_use,
MAX(nused_Max) AS Max_use,
SUM(nSize) AS Total_disk,

(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS total_used

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 = Device.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
HAVING (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
ORDER BY dPollTime DESC





When i run then the query i get the following result:
ndeviceID sdisplayname sgroupname sdescription dpolltime max_use min_use total_disk total_used
552 LWP-MEIPS Mondriaan C:\ 2013-02-01 00:00:00.000 6804261376 6803189248 8496713728 80,06 847

I want Max_use and Min_use and Total disk with a shorter number.

The Sum of Min and nsize mean to make a percentage about the avg use of the total disk.



Post #1454915
Posted Tuesday, May 21, 2013 5:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:40 AM
Points: 6,778, Visits: 13,971
ChrisM@Work (5/21/2013)

What are typical values for SUM(nUsed_Min) and SUM(nSize)?
What are typical values for nUsed_Min and nSize?

You could operate on either the original values or the aggregated values by a constant, say 1000000 or 1/1000000. Which is best depends upon the number of rows aggregated and the original values.



“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 #1454926
Posted Tuesday, May 21, 2013 5:23 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
The valua is FLOAT of the column
Post #1454928
Posted Tuesday, May 21, 2013 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:40 AM
Points: 6,778, Visits: 13,971
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?


“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 #1454932
Posted Tuesday, May 21, 2013 5:44 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)
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
Post #1454942
Posted Tuesday, May 21, 2013 5:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:40 AM
Points: 6,778, Visits: 13,971
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.


“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 #1454945
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse