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
