Jump to content

How To...

Floetic

By Floetic
in Microsoft SQL - MSSQL

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
https://forums.phpfreaks.com/topic/50911-how-to/
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
https://forums.phpfreaks.com/topic/50911-how-to/#findComment-250558
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
https://forums.phpfreaks.com/topic/50911-how-to/#findComment-250595
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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.