When I run this query below,

SELECT clientid, 
       CASE WHEN D.ccode = '-1' Then 'Did Not Show' ELSE D.ccode End ccode,  
       ca, 
       ot, 
       bw, 
       cshT, 
       dc, 
       dte, 
       approv 
  FROM dbo.emC D 
 WHERE year(dte) = year(getdate())

I get the correct results.

It is correct result because ccode shows 'Did Not Show' when the value on the db is '-1'

However, when I do a UNION ALL so I can get total for each column, I get the results but then 'Did Not Show' is no longer visible when valye for ccode is '-1'.

There are over 1000 records with valuye of '-1'.

Can someone please help?

Here is the entire code with UNION.

SELECT clientid, 
CASE WHEN D.ccode = '-1' Then 'Did Not Show' ELSE D.ccode End ccode,  
ca, 
ot, 
bw, 
cshT, 
dc, 
dte, 
approv 
FROM dbo.emC D WhERE year(dte) = year(getdate())
UNION ALL   
SELECT 'Total',
 '', 
 SUM(D.ca), 
 SUM(D.ot), 
 SUM(D.bw), 
 SUM(D.cshT),
'',
'',
'' 
  FROM emC D 
 WHERE YEAR(dte)='2011'

I also tried using ROLLUP but the real issue here is that I can't get the 'Did Not Show' text to display when ccode value is -1

ClientID        CCODE           ot      ca      bw      cshT
019692  CF001           0.00    0.00    1.00    0.00   0.00   
019692  CH503           0.00    0.00    1.00    0.00   0.00
010487  AC407           0.00    0.00    1.00    0.00   0.00
028108  CH540           0.00    0.00    1.00    0.00   0.00
028108  GS925           0.00    0.00    1.00    0.00   0.00
001038  AC428           0.00    0.00    3.00    0.00   0.00
028561  Did Not Show        0.00    0.00    0.00    0.00   0.00
016884  Did Not Show        0.00    0.00    0.00    0.00   0.00
05184   CF001           0.00    0.00    4.50    0.00   0.00