The best way to describe this practice is really that you’re putting data into your results from within the query itself.
It has a multitude of uses – you can use it to split results into more results, combine results into fewer results, or just randomly pull data out of nowhere that never existed in the first place. It’s also helpful if you want text to be a number, or a number value to be text.
Let’s start with a simple explanation of the “searched CASE” statement. It’s basically like an IF statement in Excel, etc:
CASE WHEN [something] [meets criteria] THEN [result] ELSE [result] END
I’ll put this in stupid simple terms, if you’ll bear with me. If you had a menu with sandwiches on it, and you hated everything with peanut butter, you could create a CASE statement like this:
WHEN sandwich_name like ‘%peanut%’ THEN ‘Gross’ ELSE ‘Delicious’ END as [How Does It Taste]
But if you also had a sandwich you felt less strongly about, you could do this:
WHEN sandwich_name like ‘%peanut%’ THEN ‘Gross’
WHEN sandwich_name like ‘%tuna%’ THEN ‘Tolerable’
ELSE ‘Delicious’ END as [How Does It Taste]
One thing to note is that the THEN can either be a supplied string, like ‘Gross’ or ‘Delicious’, or it can be a number, or it can be a result from a column, like so:
CASE WHEN sandwich_availablity like ‘%Available%’ THEN sandwich_name ELSE NULL END as [Sandwich Name]
I have used this for everything from forcing a pivot table into a specific order to managing bad site template changes for Issues and Risks that resulted in mismatched data for certain values.
In our case, an example is Issues Status on different sites, which look something like this across the board:
1 – Active
SQL doesn’t want those values to be friends, and while you could group them in your pivot table, what happens if some monkey goes and adds 1. Active!!! or something?
You could use your CASE statement to roll them all together, like so:
WHEN MSP_WssIssue_OlapView.Status like ‘%Active%’ THEN ‘Active’
WHEN MSP_WssIssue_OlapView.Status like ‘%Closed% THEN ‘Closed’
WHEN MSP_WssIssue_OlapView.Status like ‘%Resolved% THEN ‘Resolved’
END as [Status]
Using this method, you could convert, say, for all you Project Server users out there, the Issues or Risks value which is numerical (such as exposure) into a range of High, Medium and Low for reporting purposes, or vice versa.
CASE WHEN MSP_WssRisk_OlapView.Exposure >25 THEN ‘High’ (etc, etc)…
The last use I want to point out is more of the “There was no data there, and then there was” variety.
In my post Don’t Make Me Draw This Capacity Line With a Crayon!, I mentioned that you need to use a value which is the same for all resources to union into the Project Name. On further thought, you actually don’t have to have a real value there at all! Instead, you could pull something like this:
CASE WHEN MSP_EpmResource_UserView.ResourceName IS NOT NULL THEN ‘Capacity’ ELSE ‘NULL’ END as [ProjectName]
And voila! Now you’re drawing that pretty line without having to create a superfluous field in Project Server.
So ends my tutorial on pulling stuff out of your SQL behind. If you have any more questions of how to hocus pocus your queries, let me know. I’m always up for a challenge!