asked on

sql case and count

Hi. I am writing a query to use in a matrix rpt. I have three ticket types Internal, External, Other and escalate codes 1,2,3. I would like to count the number of tickets but if there are none i would still like the type to display 0. How can this be done? I used an iif statement in the format but something is still not displaying.

getting:
Code1 code3
internal 1 0
other 0 2

would like to see:
code1 code2 code3
internal 1 0 0
external 0 0 0
other 0 0 2

Bill Prew

Can you share your current query?

Also, would it be okay to add another table to accomplish this, or do you not have that option? If not then a UNION will be needed with hard coded data...

~bp

pgmtkl

ASKER

I dont have option to add another table, working from views. Below is my query.

SELECT TT.Inc.Ticket, TT.Inc.TicketType, TT.Inc.Escalate
CASE WHEN TT.Inc.TicketType = 'External' THEN 'External' WHEN TT.Inc.TicketType = 'Internal' THEN 'Internal' WHEN TT.Inc.TicketType = 'Other' THEN ‘Other’
ELSE ' ' END AS TType,
CASE WHEN TT TT.Inc.Escalate= '1' THEN '1' WHEN TT.Inc Escalate
= '2' THEN '2' WHEN TT.Inc.Escalate = '3' THEN '3' ELSE ' ' END AS EscCode,
CASE WHEN COUNT(TT.Inc.Ticket) IS NULL
THEN '' ELSE COUNT(TT.Inc.Ticket) END AS numIncidents
FROM TT.Inc
WHERE (TT.Inc.Open_Date >= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
CONVERT(CHAR(10), GETUTCDATE() - 1, 101) + ' 08:00:00'))) AND (TT.Inc.Open_Date <= CONVERT(BIGINT, DATEDIFF(S,
'19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CONVERT(CHAR(10), GETUTCDATE(), 101) + ' 07:59:59')))
GROUP BY TT.Inc.Ticket, TT.Inc.TicketType, TT.Inc.Escalate

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial