MySQL Forums
Forum List  »  Microsoft SQL Server

How To Format A Query Output
Posted by: Soul Tech
Date: May 11, 2007 04:34AM

--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.

Options: ReplyQuote


Subject
Written By
Posted
How To Format A Query Output
May 11, 2007 04:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.