How to make my number shorter??

  • 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

    680426137668031892488496713728

    But i need to make a short number like 68.04

  • Divide by 100000000.0 and cast as decimal.

    John

  • 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)

  • 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

  • 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.00068042613766803189248849671372880,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 (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

  • The valua is FLOAT of the column

  • 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

  • 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 DpolltimestTypenSizenUsed_AvgnUsed_min nUsed_max nTimedelta nDatatype nStatisticalDiskIdentificationID

    188323672013-05-02 13:24:23.627Fixed Disk25801953280119614586881196145868811961458688600,00292968751254

    188323682013-05-02 13:24:23.643Fixed Disk89769836544369119641603691196416036911964160600,00292968751255

    188323692013-05-02 13:24:23.673Fixed Disk262824960107304961073049610730496600,00292968751256

    188323702013-05-02 13:24:23.690Fixed Disk25801986048797573127975731279757312600,00292968751257

    Its about nUsed_min/max and AVG

  • 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 DpolltimestTypenSizenUsed_AvgnUsed_min nUsed_max nTimedelta nDatatype nStatisticalDiskIdentificationID

    188323672013-05-02 13:24:23.627Fixed Disk25801953280119614586881196145868811961458688600,00292968751254

    188323682013-05-02 13:24:23.643Fixed Disk89769836544369119641603691196416036911964160600,00292968751255

    188323692013-05-02 13:24:23.673Fixed Disk262824960107304961073049610730496600,00292968751256

    188323702013-05-02 13:24:23.690Fixed Disk25801986048797573127975731279757312600,00292968751257

    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

  • 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 DpolltimestTypenSizenUsed_AvgnUsed_min nUsed_max nTimedelta nDatatype nStatisticalDiskIdentificationID

    188323672013-05-02 13:24:23.627Fixed Disk25801953280119614586881196145868811961458688600,00292968751254

    188323682013-05-02 13:24:23.643Fixed Disk89769836544369119641603691196416036911964160600,00292968751255

    188323692013-05-02 13:24:23.673Fixed Disk262824960107304961073049610730496600,00292968751256

    188323702013-05-02 13:24:23.690Fixed Disk25801986048797573127975731279757312600,00292968751257

    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.

  • Its bytes in colmn

    And the units you see in de qeury before that he find the units in device.deviceid and name.

  • 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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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

  • 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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply