By Example : CASE Statement
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.
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