[SQL] Calculate difference in times using CASE statements

I've been handed a table to report on where, instead of grouping data values in columns, they are all stored in a single column with a corresponding "question" in the next column. This is the first time I've had to report on a table structured like this.

| QUESTIONID | QUESTION | ANSWER |
|------------|-------------|--------|
| 589 | Start Time | 9:40 |
| 590 | Finish Time | 9:42 |
| 28 | Hydrants | 2 |
| 36 | Project Name| |


I am needing to calculate the difference between the two times listed. I've been trying a bunch of variances using CASE, DATEDIFF, CONVERT and other functions such as below, but I've yet to be able to get a value other than NULL.

DATEDIFF(MINUTE,CASE WHEN QUESTIONID = 590 THEN ANSWER END,CASE WHEN QUESTIONID = 589 THEN ANSWER END) AS TimeDiff


I am stumped on how to get the desired result of 2.

Continue reading...