I’m currently working on a kind of warehouse for our SQL inventory, where I’m ripping out data from MAPT (More on that to follow), as I’m creating a table i found that I’m using the CASE statement in multiple ways so thought i would share a few of them.

 

Basic CASE

 

Simple CASE statement where i wanted to Change the output of a column from text to numeric

 

CASE d .[OperatingSystemServicePack]
WHEN '' THEN 0
WHEN 'Service Pack 1' THEN 1
WHEN 'Service Pack 2' THEN 2
WHEN 'Service Pack 3' THEN 3
END AS 'OSServicePack'

 

Doing a expression to work out the CASE

In this example the i.Version column shows the numeric value for the SQL Server so would look like ‘11.0.2100.60’

 

CASE LEFT( i.[Version] , CHARINDEX ('.', i. [Version]) - 1)
WHEN 8 THEN 'SQL Server 2000'
WHEN 9 THEN 'SQL Server 2005'
WHEN 10 THEN 'SQL Server 2008'
WHEN 11 THEN 'SQL Server 2008 r2'
WHEN 12 THEN 'SQL Server 2012'
WHEN 13 THEN 'SQL Server 2014'
END AS 'SQL'

 

Doing an expression in the WHEN / ELSE

 

CASE
WHEN CHARINDEX( ';', nac.[IpAddress] ) = 0 THEN nac.[IpAddress]
ELSE LEFT( nac.[IpAddress] , CHARINDEX (';', nac. [IpAddress]) - 1)
END AS 'IP Address'

Doing a Subquery in a WHEN

 

This wasn’t really needed but i wanted to add an example of using a subquery based on the CASE statement, in this example, the Sqlservicetype shows which feature is installed on the server, so i wanted to find out how many database files are on the C drive if the engine is installed.

 

CASE i .Sqlservicetype
WHEN 1
THEN ( SELECT COUNT (d. [filename])
FROM [uk] .[SqlServer_Inventory]. [DataBaseFileGroup] AS d
WHERE i .DeviceNumber = d .DeviceNumber
AND LEFT( d.Filename , 1) = 'C'
)
ELSE ''
END
Advertisements