--Gives overall total and a breakdown of the number of calls resolved for each priority by I.T (excluding 3rd parties)
declare @startdate datetime,
@enddate datetime
select
RM.fldPriorityCode as 'Priority',
--RM.fldEditedBy as 'User Login',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldTrade = 'IT'
--and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
--RM.fldEditedBy as 'User Login',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldTrade = 'IT'
--and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
order by RM.fldPriorityCode
Results;
Priority Calls Resolved
-------- --------------
1 73
2 2753
3 222
4 15
5 23
Total 3086
------------------------------------------------------------
--Number of calls resolved by each staff member [for a specified date range]
declare @startdate datetime,
@enddate datetime
select
RM.fldPriorityCode as 'Priority',
RM.fldEditedBy as 'User Login',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldTrade = 'IT'
--and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
group by RM.fldPriorityCode,RM.fldEditedBy
union
select
'Total' as 'Priority',
RM.fldEditedBy as 'User Login',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldTrade = 'IT'
--and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
group by RM.fldPriorityCode,RM.fldEditedBy
Results;
Priority User Login Calls Resolved
-------- -------------------- --------------
1 AMCCO039 9
1 COBRI003 4
1 JDONN001 2
1 JFAIT001 9
1 MCATN001 23
1 PBRAD001 23
1 PHUDS001 3
2 AMCCO039 3
2 COBRI003 14
2 JDONN001 954
2 JFAIT001 352
2 MCATN001 930
2 PBRAD001 119
2 PHUDS001 19
2 PKENN004 331
2 PMISK001 31
3 COBRI003 19
3 JDONN001 31
3 JFAIT001 28
3 MCATN001 45
3 PBRAD001 12
3 PHUDS001 30
3 PKENN004 5
3 PMISK001 52
4 JFAIT001 5
4 MCATN001 4
4 PBRAD001 2
4 PKENN004 4
5 JFAIT001 20
5 PBRAD001 1
5 PHUDS001 2
Total AMCCO039 3
Total AMCCO039 9
Total COBRI003 4
Total COBRI003 14
Total COBRI003 19
Total JDONN001 2
Total JDONN001 31
Total JDONN001 954
Total JFAIT001 5
Total JFAIT001 9
Total JFAIT001 20
Total JFAIT001 28
Total JFAIT001 352
Total MCATN001 4
Total MCATN001 23
Total MCATN001 45
Total MCATN001 930
Total PBRAD001 1
Total PBRAD001 2
Total PBRAD001 12
Total PBRAD001 23
Total PBRAD001 119
Total PHUDS001 2
Total PHUDS001 3
Total PHUDS001 19
Total PHUDS001 30
Total PKENN004 4
Total PKENN004 5
Total PKENN004 331
Total PMISK001 31
Total PMISK001 52
Desired display of results;
User Login Priority Calls Resolved
---------- -------- -------------
AMCCO039 1 9
2 3
3 0
4 0
5 0
Total 12
COBRI003 1 4
2 14
3 19
4 0
5 0
Total 37
...
OR
User Login 1 2 3 4 5 Total
---------- - - - - - -----
AMCCO039 9 3 0 0 0 12
COBRI003 4 14 19 0 0 37
...
Any guidance that anyone could offer to me in how I would go about displaying my results in either of the following ways shown above?
---------------------------------------
http://floetichoney.spaces.live.com/
---------------------------------------
Edited 1 time(s). Last edit at 05/11/2007 06:23AM by Soul Tech.