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 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? Quote Link to comment https://forums.phpfreaks.com/topic/50911-how-to/ Share on other sites More sharing options...
Floetic Posted May 11, 2007 Author Share Posted May 11, 2007 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; NULL NULL 3094 NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 AMCCO039 NULL 12 AMCCO039 1 9 AMCCO039 2 3 COBRI003 NULL 37 COBRI003 1 4 COBRI003 2 14 COBRI003 3 19 JDONN001 NULL 987 JDONN001 1 2 JDONN001 2 954 JDONN001 3 31 JFAIT001 NULL 414 JFAIT001 1 9 JFAIT001 2 352 JFAIT001 3 28 JFAIT001 4 5 JFAIT001 5 20 MCATN001 NULL 1010 MCATN001 1 23 MCATN001 2 930 MCATN001 3 53 MCATN001 4 4 PBRAD001 NULL 157 PBRAD001 1 23 PBRAD001 2 119 PBRAD001 3 12 PBRAD001 4 2 PBRAD001 5 1 PHUDS001 NULL 54 PHUDS001 1 3 PHUDS001 2 19 PHUDS001 3 30 PHUDS001 5 2 PKENN004 NULL 340 PKENN004 2 331 PKENN004 3 5 PKENN004 4 4 PMISK001 NULL 83 PMISK001 2 31 PMISK001 3 52 Is there any way of naming the 'NULLs' at the following {to be 'Total' instead}; NULL 3094 NULL 12 NULL 37 NULL 987 NULL 414 NULL 1010 NULL 157 NULL 54 NULL 340 NULL 83 ??? *[highlight]NULL NULL 3094[/highlight] NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 *can this be moved to here & how do I do that!?! *[highlight]AMCCO039 NULL 12[/highlight] AMCCO039 1 9 AMCCO039 2 3 *the same again for this Quote Link to comment https://forums.phpfreaks.com/topic/50911-how-to/#findComment-250558 Share on other sites More sharing options...
Floetic Posted May 11, 2007 Author Share Posted May 11, 2007 --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; NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 NULL Total 3094 AMCCO039 1 9 AMCCO039 2 3 AMCCO039 Total 12 COBRI003 1 4 COBRI003 2 14 COBRI003 3 19 COBRI003 Total 37 JDONN001 1 2 JDONN001 2 954 JDONN001 3 31 JDONN001 Total 987 JFAIT001 1 9 JFAIT001 2 352 JFAIT001 3 28 JFAIT001 4 5 JFAIT001 5 20 JFAIT001 Total 414 MCATN001 1 23 MCATN001 2 930 MCATN001 3 53 MCATN001 4 4 MCATN001 Total 1010 PBRAD001 1 23 PBRAD001 2 119 PBRAD001 3 12 PBRAD001 4 2 PBRAD001 5 1 PBRAD001 Total 157 PHUDS001 1 3 PHUDS001 2 19 PHUDS001 3 30 PHUDS001 5 2 PHUDS001 Total 54 PKENN004 2 331 PKENN004 3 5 PKENN004 4 4 PKENN004 Total 340 PMISK001 2 31 PMISK001 3 52 PMISK001 Total 83 Quote Link to comment https://forums.phpfreaks.com/topic/50911-how-to/#findComment-250595 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.