How To...
-
--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; {excluding the dots}
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.....5Total
----------........-.....-.....-.....-........-----
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
------------------------------------------------ -
Try using WITH ROLLUP or WITH CUBE. That along with judicious use of the GROUPING function should produce exactly what you need.
A thorough reading of the BOL entries for the above should clear up any questions.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Thank you Gordon for your help
My code is now looking like this [I'm almost getting there] ::::
--Number of calls resolved by each staff member [for a specified date range]
--declare @startdate datetime,
--@enddate datetime
select
RM.fldEditedBy as 'User Login',
RM.fldPriorityCode as 'Priority',
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 with cube
union
select
RM.fldEditedBy as 'User Login',
RM.fldPriorityCode as 'Priority',
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 with cube
Results;
NULLNULL3094
NULL173
NULL22753
NULL3230
NULL415
NULL523
AMCCO039NULL12
AMCCO03919
AMCCO03923
COBRI003NULL37
COBRI00314
COBRI003214
COBRI003319
JDONN001NULL987
JDONN00112
JDONN0012954
JDONN001331
JFAIT001NULL414
JFAIT00119
JFAIT0012352
JFAIT001328
JFAIT00145
JFAIT001520
MCATN001NULL1010
MCATN001123
MCATN0012930
MCATN001353
MCATN00144
PBRAD001NULL157
PBRAD001123
PBRAD0012119
PBRAD001312
PBRAD00142
PBRAD00151
PHUDS001NULL54
PHUDS00113
PHUDS001219
PHUDS001330
PHUDS00152
PKENN004NULL340
PKENN0042331
PKENN00435
PKENN00444
PMISK001NULL83
PMISK001231
PMISK001352
Is there any way of naming the 'NULLs' at the following {to be 'Total' instead};
NULL3094
NULL12
NULL37
NULL987
NULL414
NULL1010
NULL157
NULL54
NULL340
NULL83
???
*[highlight]NULLNULL3094[/highlight]
NULL173
NULL22753
NULL3230
NULL415
NULL523
*can this be moved to here & how do I do that!?!
*[highlight]AMCCO039NULL12[/highlight]
AMCCO03919
AMCCO03923
*the same again for this
------------------------------------------------
http://floetichoney.spaces.live.com
------------------------------------------------ -
Check BOL for the GROUPING keyword for more details.
select
RM.fldEditedBy as 'User Login',
case Grouping(RM.fldPriorityCode)
when 0 then RM.fldPriorityCode
else 'Total' end as 'Priority',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Muchas Gracias again ~ Perfecto! *** hugs you ***
--declare @startdate datetime,
--@enddate datetime
select
RM.fldEditedBy as 'User Login',
case Grouping(RM.fldPriorityCode)
when 0 then RM.fldPriorityCode
else 'Total' end as 'Priority',
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 with cube
union
select
RM.fldEditedBy as 'User Login',
case Grouping(RM.fldPriorityCode)
when 0 then RM.fldPriorityCode
else 'Total' end as 'Priority',
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 with cube
Results:
NULL173
NULL22753
NULL3230
NULL415
NULL523
NULLTotal3094
AMCCO03919
AMCCO03923
AMCCO039Total12
COBRI00314
COBRI003214
COBRI003319
COBRI003Total37
JDONN00112
JDONN0012954
JDONN001331
JDONN001Total987
JFAIT00119
JFAIT0012352
JFAIT001328
JFAIT00145
JFAIT001520
JFAIT001Total414
MCATN001123
MCATN0012930
MCATN001353
MCATN00144
MCATN001Total1010
PBRAD001123
PBRAD0012119
PBRAD001312
PBRAD00142
PBRAD00151
PBRAD001Total157
PHUDS00113
PHUDS001219
PHUDS001330
PHUDS00152
PHUDS001Total54
PKENN0042331
PKENN00435
PKENN00444
PKENN004Total340
PMISK001231
PMISK001352
PMISK001Total83
------------------------------------------------
http://floetichoney.spaces.live.com
------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply