SCCM 2007 - Formatting SQL Statement Numbers

We want to format initial output of a number in MB's.  It is Physical Memory, first acquired in MB's, then divided by 1024, and the last three numbers removed:

4096 to 4
12287 to 12
150349 to 150

Here is the statement we are using:

SELECT DISTINCT 
	SYS.Name0 AS [Computer Name],

	[Environment] = CASE
		WHEN (SYS.Name0 LIKE '%DV%')
		THEN 'DEV'
		WHEN (SYS.Name0 LIKE '%UA%')
		THEN 'UAT'
		ELSE 'PROD'
	END,

	SYS.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],

	SUBSTRING(MEM.Name0, 1, CHARINDEX('|', MEM.Name0) - 1) AS [Operating System],

	OS.CSDVersion0 AS [Service Pack],
	DATEDIFF (day, OS.LastBootUpTime0, WS.LastHWScan) AS [Uptime (in Days)],
	CONVERT(varchar(20), OS.LastBootupTime0, 100) AS [Last Reboot],
	CONVERT(varchar(20), WS.LastHWScan, 100) AS [Last Hardware Inventory],

	[Serial Number] = CASE
		WHEN (ENCL.SerialNumber0 = 'None')
		THEN 'VM'
		ELSE ENCL.SerialNumber0
	END,

	[Manufacturer] = CASE
		WHEN (COMP.Manufacturer0 = 'Dell Inc.')
		THEN 'Dell'
		WHEN (COMP.Manufacturer0 = 'VMWare, Inc.')
		THEN 'WMWare'
		ELSE COMP.Manufacturer0
	END,

	COMP.Model0 AS [Model],
	MEM.TotalPhysicalMemory0 / 1024 As [Physical Memory (GB)],

	LDSK_C.Size0 / 1024 AS [C: Size (GB)],
	LDSK_C.FreeSpace0 / 1024 AS [C: FreeSpace (GB)],
	(LDSK_C.Size0 / 1024) - (LDSK_C.FreeSpace0 / 1024) AS [C: Used (GB)],

	LDSK_E.Size0 / 1024 AS [E: Size (GB)],
	LDSK_E.FreeSpace0 / 1024 AS [E: FreeSpace (GB)],
	(LDSK_E.Size0 / 1024) - (LDSK_E.FreeSpace0 / 1024) AS [E: Used (GB)]

FROM v_R_System AS SYS

INNER JOIN v_GS_OPERATING_SYSTEM OS ON SYS.ResourceID = OS.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE ENCL ON SYS.ResourceID = ENCL.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM COMP ON SYS.ResourceID = COMP.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_C ON SYS.ResourceID = LDSK_C.ResourceID
	AND LDSK_C.DriveType0 = '3'
	AND LDSK_C.Name0 LIKE 'C%'
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_E ON SYS.ResourceID = LDSK_E.ResourceID
	AND LDSK_E.DriveType0 = '3'
	AND LDSK_E.Name0 LIKE 'E%'

WHERE SYS.canonicalName0 LIKE 'mydomain.com/Servers%' OR SYS.canonicalName0 LIKE 
	'mydomain.com/Domain Controllers%' 

ORDER BY SYS.Name0

Open in new window

LVL 3
isd503Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not real sure what you're asking, as the #'s above reflect divide by 1000, not divide by 1024.

The quick and dirty way would be to divide by that number, cast as an int to return only the whole number portion.
SELECT CAST(150349/1000 as int) 
SELECT CAST(150349/1024 as int) 

Open in new window

0
isd503Author Commented:
Here is the current rquest in the statement:  

MEM.TotalPhysicalMemory0 / 1024 As [Physical Memory (GB)],

I am looking for a way to divide by 1024 and return only the left most number, if there are four numbers, left most two numbers if there are five and left most three numbers if there are six.

I just need to hack off numbers to the right after dividing by 1024 to get the whole number I am looking for.

The suggestion above did not run in the statement.

I just need to remove the right most three numbers in each output.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am looking for a way to divide by 1024 and return only the left most number
>I just need to remove the right most three numbers in each output.
These two statements are mutually exclusive, as 150349 / 1024 = 146 and change, while the left three numbers of 150349 is 150.  So which do you want, 146 or 150?

>I just need to remove the right most three numbers in each output.
Declare @your_number int = 150349
SELECT LEFT(CAST(@your_number as varchar(max)), LEN(CAST(@your_number as varchar(max))) - 3)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

isd503Author Commented:
I need to divide the initial number in the report by 1024, and then chop off three numbers on the right.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Still not clear.

150349 / 1024 = 146.82519531250000000000.
Define 'chop off three numbers on the right' of 146.82519531250000000000.
0
David ToddSenior DBACommented:
Hi,

Either use round( 150349 / 1024 , 0 ) or cast( 150349 / 1024.0  as int )

There are a few other ways.

But really, this question has been answered twice by Jim above. Have you tried his code?

Regards
  David
0
isd503Author Commented:
Let me explain further.  I am very new to SQL statements, so I am looking for the code that will fit into the statement and work.  I may or may not be able to take a hint from someone and make it work.

The SCCM report collects the amount of memory in a server in megabytes.  I am trying to find a way to divide that column by 1024, and round down to the nearest whole number.  if the output is four numbers (after being divided by 1024), then only the left most number is needed.  If the output is five numbers, I need the left most two numbers.  If it is six numbers, I need the left most three.

The raw output is on the far left:

6278168 / 1024 = 6131 (need to round to 6)
100650072 / 1024 = 98291 (need to round to 98)

If the output ends up being a six digit number after being divided by 1024, I need the left most three numbers.

Does this make more sense?  I need a way to do this in a SQL statement.

I do not think you can do division inside the ROUND or CAST functions.
0
David ToddSenior DBACommented:
Hi,

Please run this code against tempdb from SSMS
select round( 6278168 / 1024.0, 0 )

What is the result?

Regards
  David
0
isd503Author Commented:
The result is:  6131.000000
0
isd503Author Commented:
I worked through it and found the solution:

	[Physical Memory (GB)] = CASE
		WHEN MEM.TotalPhysicalMemory0/1024 LIKE '[0-9][0-9][0-9][0-9]'
		THEN LEFT(MEM.TotalPhysicalMemory0/1024,1)
		WHEN MEM.TotalPhysicalMemory0/1024 LIKE '[0-9][0-9][0-9][0-9][0-9]'
		THEN LEFT(MEM.TotalPhysicalMemory0/1024,2)
		WHEN MEM.TotalPhysicalMemory0/1024 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
		THEN LEFT(MEM.TotalPhysicalMemory0/1024,3)
		ELSE MEM.TotalPhysicalMemory0/1024
	END,

Open in new window

0
isd503Author Commented:
Thanks for your help, it gave me ideas I could follow up on.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.