Jump to content

How To...

Floetic

By Floetic
in Microsoft SQL - MSSQL

 Share

Recommended Posts

Floetic Newbie

Floetic

Posted
Posted 

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

 

 

Link to comment
Share on other sites
Floetic Newbie

Floetic

Posted
  • Author
Posted

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

Link to comment
Share on other sites
Floetic Newbie

Floetic

Posted
  • Author
Posted 

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

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.