More Traffic Lights

In previous posts (http://www.joatit.com/wordpress/?p=96) and (http://www.joatit.com/wordpress/?p=69) I showed how to colour code the quick views in a traffic light style using the following query:

(CASE WHEN [CLK_STOPTIME:] IS NOT NULL THEN 'On Hold' WHEN GETDATE() > [RECOMMENDEDFIX_DATE:] AND GETDATE() < [DUE_DATE:] THEN 'Warning' WHEN GETDATE() > [DUE_DATE:] THEN 'Critical' ELSE 'Normal' END)

Recently I was talking to one of our users who, when using Service Desk Express 9.8, rather liked the ability to view closed incidents in the quick views. She commented however, that when you viewed closed incidents with the above query everything went red eventually. It would be much better if you could see if the call was inside or outside SLA as defined by Close Date vs. Due Date.

So without further ado; creating a calculated field with the query below will not only achieve the same results as the previous posts but additionally will show if the incident was closed in time (Normal) or not (Critical) when looking at closed incidents:

(CASE WHEN BASE.[CLK_STOPTIME:] IS NOT NULL AND BASE.[STATUS] = 'O' THEN 'On Hold' WHEN GETDATE() > BASE.[RECOMMENDEDFIX_DATE:] AND GETDATE() < BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Warning' WHEN GETDATE() > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Critical' WHEN BASE.[CLOSED ON] > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'C' THEN 'Critical' ELSE 'Normal' END)

Hope it helps. As always, please keep the feedback coming. It makes sure that what I am posting is of relevance and interest to the people reading.