May 21, 2013 at 3:58 am
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
May 21, 2013 at 4:03 am
Divide by 100000000.0 and cast as decimal.
John
May 21, 2013 at 4:09 am
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)
May 21, 2013 at 4:11 am
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
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
May 21, 2013 at 4:36 am
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 10d.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.
May 21, 2013 at 5:17 am
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.
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
May 21, 2013 at 5:23 am
The valua is FLOAT of the column
May 21, 2013 at 5:26 am
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?
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
May 21, 2013 at 5:44 am
ChrisM@Work (5/21/2013)
karim.boulahfa (5/21/2013)
The valua is FLOAT of the columnFLOAT 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
May 21, 2013 at 5:50 am
karim.boulahfa (5/21/2013)
ChrisM@Work (5/21/2013)
karim.boulahfa (5/21/2013)
The valua is FLOAT of the columnFLOAT 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.
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
May 21, 2013 at 7:22 am
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 columnFLOAT 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.
May 21, 2013 at 7:24 am
Its bytes in colmn
And the units you see in de qeury before that he find the units in device.deviceid and name.
May 21, 2013 at 7:56 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2013 at 8:26 am
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
May 21, 2013 at 9:07 am
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
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 16 total)
You must be logged in to reply to this topic. Login to reply