Need help with report of Incidents edited within last 5 days.

This issue has been solved. See solution.
JMoreno28's picture

Need help with report of current users Incidents edited within last 5 days please. Any feedback is appreciated.

SELECT       
 hd1.[assigned_to_worker_name] as 'Worker_Name',         
  hd1.[assigned_to_worker_email] as 'Worker_Email',     
 sum(case when hd1.[workitem_status_lookup_id] = '300' then 1 else 0 end) as 'Open_Work_Requests',            
 sum(case when hd1.[workitem_status_lookup_id] = '100' then 1 else 0 end) as 'Planned_Work_Items',     
 sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate()) = 0 then 1 else 0 end) as 'Items_Closed_Today',      
 sum(case when hd1.[workitem_detail_view] (hd1.[workitem_version] >1 getdate())   <= 5 then 1 else 0 end) as 'Items_Edited_This_Week',   
  sum(case when hd1.[workitem_priority_lookup_id] = '100' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'ASAP_Priority',       
  sum(case when hd1.[workitem_priority_lookup_id] = '200' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'High_Priority',         
  sum(case when hd1.[workitem_priority_lookup_id] = '300' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Medium_Priority',     
  sum(case when hd1.[workitem_priority_lookup_id] = '400' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Low_Priority',       
  sum(case when hd1.[workitem_priority_lookup_id] = '500' and hd1.[workitem_status_lookup_id] !='500' then 1 else 0 end) as 'Planned_Priority'   
                                
FROM         
 dbo.HD_workitem_current_view hd1         
WHERE         
 hd1.[assigned_to_worker_status] = 'a'      
GROUP BY         
 hd1.[assigned_to_worker_name], hd1.[assigned_to_worker_email]      
ORDER BY         
 hd1.[assigned_to_worker_name]      

JMoreno28's picture

This is what I came up

Solution

This is what I came up with:
SELECT           
 hd1.[assigned_to_worker_name] as 'Worker_Name',             
  hd1.[assigned_to_worker_email] as 'Worker_Email',         
 sum(case when hd1.[workitem_status_lookup_id] = '300' then 1 else 0 end) as 'Open_Work_Requests',                
 sum(case when hd1.[workitem_status_lookup_id] = '100' then 1 else 0 end) as 'Planned_Work_Items',         
 sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate()) = 0 then 1 else 0 end) as 'Items_Closed_Today',         
 sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate())   <= 5 then 1 else 0 end) as 'Items_Closed_This_Week',    
sum(case when hd1.[workitem_version] > '0' and datediff(d, hd1.[workitem_modified_on], getdate())   <= 5 then 1 else 0 end) as 'Items_Edited_This_Week',          
  sum(case when hd1.[workitem_priority_lookup_id] = '100' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'ASAP_Priority',           
  sum(case when hd1.[workitem_priority_lookup_id] = '200' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'High_Priority',             
  sum(case when hd1.[workitem_priority_lookup_id] = '300' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Medium_Priority',         
  sum(case when hd1.[workitem_priority_lookup_id] = '400' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Low_Priority',           
  sum(case when hd1.[workitem_priority_lookup_id] = '500' and hd1.[workitem_status_lookup_id] !='500' then 1 else 0 end) as 'Planned_Priority'       
                                    
FROM             
 dbo.HD_workitem_current_view hd1             
WHERE             
 hd1.[assigned_to_worker_status] = 'a'          
GROUP BY             
 hd1.[assigned_to_worker_name], hd1.[assigned_to_worker_email]          
ORDER BY             
 hd1.[assigned_to_worker_name]