SQL Clone
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 (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 258
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.
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 258
Its bytes in colmn

And the units you see in de qeury before that he find the units in device.deviceid and name.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40086 Visits: 32653
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 258
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16298 Visits: 19551
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
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 258
I dont get any valuas out the Query ??
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16298 Visits: 19551
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
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