Output in Groupby and count using Row and Column Structure

Category: sql server transactsql

Question

du00805 on Sat, 05 May 2018 19:21:50


Hi,

I want to make my output of the query as below

 

Analyst Name

Total # of tickets resolved with Transferring

“TR” will Include

Tickets # resolved without transferring

“TR” entry not included

Analyst 1

50

16

Analyst 2

45

20

 

                                          

 To Achieve  the above output below is the information:


 





Total Tickets come from Call_req table which count (ref_num)


                                          






I am trying to create the query for your reference where the query and output result is below:

 

select

call_req.ref_num  as TicketNumber, concat (ca_contact.first_name, ' ' , ca_contact.last_name) as Assignee1, t.type as TYPES

 from

 act_log T

 inner join call_req on T.call_req_id = call_req.persid

inner join ca_contact on call_req.assignee= ca_contact.contact_uuid

 

where T.type <>'TR' and ref_num ='140'






Do let me know what changes I have to make in my query to get my result.

Do let me know if you understood about my requirement or you have any questions.

I can share my database too for the reference.

















Replies

Visakh16 on Sat, 05 May 2018 19:49:29


looks like this is what you're after

select Assignee1,
COUNT(call_req_Id) AS TotalTicketsIncludingTR,
COUNT(CASE WHEN TRIncluded = 1 THEN NULL ELSE Call_req_ID END) AS TotalTicketsResolvedWithoutTR
from
(
select

concat (ca_contact.first_name, ' ' , ca_contact.last_name) as Assignee1,
T.call_reqid,
CASE WHEN SUM(CASE WHEN T.Type = 'TR' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END AS TRIncluded
 from

 act_log T

 inner join call_req on T.call_req_id = call_req.persid

inner join ca_contact on call_req.assignee= ca_contact.contact_uuid

 

where ref_num ='140'
GROUP BY concat (ca_contact.first_name, ' ' , ca_contact.last_name),T.Call_reg_ID
)t
GROUP BY Assignee1

du00805 on Sun, 06 May 2018 10:28:40



Visakh16 on Sun, 06 May 2018 10:45:44



That depends on your front end language

Seeing the above I think what you need is like below

select Assignee1,
COUNT(call_req_Id) AS TotalTicketsIncludingTR,
COUNT(CASE WHEN TRIncluded = 1 THEN NULL ELSE Call_req_ID END) AS TotalTicketsResolvedWithoutTR
from
(
select

concat (ca_contact.first_name, ' ' , ca_contact.last_name) as Assignee1,
T.call_reqid,
CASE WHEN SUM(CASE WHEN T.Type = 'TR' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END AS TRIncluded
 from

 act_log T

 inner join call_req on T.call_req_id = call_req.persid

inner join ca_contact on call_req.assignee= ca_contact.contact_uuid

 

where ref_num ='140'
and (ca_contact.first_name, ' ' , ca_contact.last_name) = $P{Assigneee1}
and dateadd(ss,call_req.open_date,'19700101') between $P{StartDate} and $P{EndDate}
GROUP BY concat (ca_contact.first_name, ' ' , ca_contact.last_name),T.Call_reg_ID
)t
GROUP BY Assignee1

du00805 on Sat, 12 May 2018 20:34:42


Thanks

Visakh16 on Sun, 13 May 2018 05:38:54


Thanks

welcome

Feel free to mark the relevant post as answer if it gave you the solution