question

Nick 1 avatar image
Nick 1 asked

Hi guys can you plz help me with this query.

Here are my two queries. and i wanna get three columns out of these two queries 1. modified_by_worker_full_name 2. '# Closed Incidents on First Contact' 3. 'Total Tickets closed'

I have done almost everything and used help from people but they couldnt figure this out.

SELECT modified_by_worker_full_name, COUNT(*)'# Closed Incidents on First Contact' FROM HD_workitem_detail_view WHERE workitem_status_lookup_id = '125' --PreClosed status post Dec 11, 2009 and modified_by_worker_name IN ('orichar', 'kprivot', 'cstewa2', 'cwilso4', 'bkey001', 'emachad', 'jsmith6', 'bmillig','eddie')

 AND workitem_number IN                     
              (                    
              SELECT workitem_number                    
              FROM HD_workitem_current_view                      
            WHERE  (workitem_version = 1                     
               or (workitem_version = 2 and workitem_modified_by_worker_id = 5))                    
               --and workitem_modified_by_worker_id = 5-- helpdesk user                    
                    AND workitem_modified_on BETWEEN '1/1/2010' AND '2/1/2010'                    
               )                    

GROUP BY modified_by_worker_full_name ORDER BY modified_by_worker_full_name


SELECT modified_by_worker_full_name, COUNT(*)'Total Tickets closed' FROM HD_workitem_detail_view
WHERE workitem_status_lookup_id = '125' --PreClosed status post Dec 11, 2009 AND modified_by_worker_name IN ('orichar', 'kprivot', 'cstewa2', 'cwilso4', 'bkey001', 'emachad', 'bmillig') AND (workitem_type_lookup_value Like 'incide%' or workitem_type_lookup_value LIKE 'Service%' ) AND workitem_number IN ( SELECT workitem_number FROM HD_workitem_current_view
WHERE workitem_status_lookup_id = '600' --Closed Status AND workitem_modified_by_worker_id = 5 -- helpdesk user AND workitem_modified_on BETWEEN '1/1/2010' AND '2/1/2010' )

GROUP BY modified_by_worker_full_name ORDER BY modified_by_worker_full_name

sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kday avatar image
Kday answered

I think as subquery would help. Something like...

SELECT modified_by_worker_full_name, SUM(ClosedStatusItems) as 'Closed Items', SUM(TotalTIckets) as 'Total Tickets Closed' from ( SELECT modified_by_worker_full_name, ClosedStatusItems = case when workitem_status_lookup_id = '600' then 1 else 0 end, 1 as TotalTickets FROM HD_workitem_detail_view WHERE workitem_status_lookup_id = '125') as mySubQuery GROUP BY modified_by_worker_full_name ORDER BY modified_by_worker_full_name

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.