Floetic Posted May 11, 2007 Share Posted May 11, 2007 --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 datetimeselectRM.fldPriorityCode as 'Priority',--RM.fldEditedBy as 'User Login',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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.fldPriorityCodeunion select'Total' as 'Priority',--RM.fldEditedBy as 'User Login',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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.fldPriorityCodeResults;Priority Calls Resolved -------- -------------- 1 732 27533 2224 155 23Total 3086--------------------------------------------------------------Number of calls resolved by each staff member [for a specified date range]declare @startdate datetime,@enddate datetimeselectRM.fldPriorityCode as 'Priority',RM.fldEditedBy as 'User Login',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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.fldEditedByunion select'Total' as 'Priority',RM.fldEditedBy as 'User Login',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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.fldEditedByResults;Priority User Login Calls Resolved -------- -------------------- -------------- 1 AMCCO039 91 COBRI003 41 JDONN001 21 JFAIT001 91 MCATN001 231 PBRAD001 231 PHUDS001 32 AMCCO039 32 COBRI003 142 JDONN001 9542 JFAIT001 3522 MCATN001 9302 PBRAD001 1192 PHUDS001 192 PKENN004 3312 PMISK001 313 COBRI003 193 JDONN001 313 JFAIT001 283 MCATN001 453 PBRAD001 123 PHUDS001 303 PKENN004 53 PMISK001 524 JFAIT001 54 MCATN001 44 PBRAD001 24 PKENN004 45 JFAIT001 205 PBRAD001 15 PHUDS001 2Total AMCCO039 3Total AMCCO039 9Total COBRI003 4Total COBRI003 14Total COBRI003 19Total JDONN001 2Total JDONN001 31Total JDONN001 954Total JFAIT001 5Total JFAIT001 9Total JFAIT001 20Total JFAIT001 28Total JFAIT001 352Total MCATN001 4Total MCATN001 23Total MCATN001 45Total MCATN001 930Total PBRAD001 1Total PBRAD001 2Total PBRAD001 12Total PBRAD001 23Total PBRAD001 119Total PHUDS001 2Total PHUDS001 3Total PHUDS001 19Total PHUDS001 30Total PKENN004 4Total PKENN004 5Total PKENN004 331Total PMISK001 31Total PMISK001 52Desired display of results; {excluding the dots}User Login Priority Calls Resolved---------- -------- -------------AMCCO039 1................9 2................3 3................0 4................0 5................0 Total..............12COBRI003 1................4 2................14 3................19 4................0 5................0 Total..............37...ORUser Login 1 2 3 4 5 Total---------- - - - - - -----AMCCO039 9.....3.....0.....0.....0.....12COBRI003 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? Link to comment Share on other sites More sharing options...
Floetic Posted May 11, 2007 Author Share Posted May 11, 2007 --declare @startdate datetime,--@enddate datetimeselectRM.fldEditedBy as 'User Login',case Grouping(RM.fldPriorityCode)when 0 then RM.fldPriorityCodeelse 'Total' end as 'Priority',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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 cubeunion selectRM.fldEditedBy as 'User Login',case Grouping(RM.fldPriorityCode)when 0 then RM.fldPriorityCodeelse 'Total' end as 'Priority',count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved'from tblRequestMaster RMwhere RM.fldPriorityCode between 1 and 5and 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 cubeResults;NULL 1 73NULL 2 2753NULL 3 230NULL 4 15NULL 5 23NULL Total 3094AMCCO039 1 9AMCCO039 2 3AMCCO039 Total 12COBRI003 1 4COBRI003 2 14COBRI003 3 19COBRI003 Total 37JDONN001 1 2JDONN001 2 954JDONN001 3 31JDONN001 Total 987JFAIT001 1 9JFAIT001 2 352JFAIT001 3 28JFAIT001 4 5JFAIT001 5 20JFAIT001 Total 414MCATN001 1 23MCATN001 2 930MCATN001 3 53MCATN001 4 4MCATN001 Total 1010PBRAD001 1 23PBRAD001 2 119PBRAD001 3 12PBRAD001 4 2PBRAD001 5 1PBRAD001 Total 157PHUDS001 1 3PHUDS001 2 19PHUDS001 3 30PHUDS001 5 2PHUDS001 Total 54PKENN004 2 331PKENN004 3 5PKENN004 4 4PKENN004 Total 340PMISK001 2 31PMISK001 3 52PMISK001 Total 83 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.