Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to make my number shorter??


How to make my number shorter??

Author
Message
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7472 Visits: 15142
Divide by 100000000.0 and cast as decimal.

John
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8997 Visits: 19028
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
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8997 Visits: 19028
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
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
The valua is FLOAT of the column
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8997 Visits: 19028
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
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8997 Visits: 19028
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
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