Need help with report of Incidents edited within last 5 days.
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]
This is what I came up
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]
Would you like to reply?
Login or Register to post your comment.