SQL Query
-
--Number of calls closed in 0-5 days; 6-10; 11+ [for a specified date range]
declare @startdate datetime,
@finishdate datetime
select RM.fldPriorityCode as 'Priority',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5Days'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5
--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10
--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5Days'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5
--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10
--and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11order by RM.fldPriorityCode asc
Results;
Priority Closed Calls 0-5 Days
-------- -------------------
1..........................14
2..........................1868
3..........................59
4..........................149
Total.....................2090
Priority Closed Calls 6-10 Days
-------- --------------------
1..........................4
2..........................342
3..........................23
4..........................77
5..........................1
Total.....................447
Priority Closed Calls 11+ Days
-------- --------------------
1..........................3
2..........................516
3..........................60
4..........................225
5..........................3
Total.....................807
I'm looking to display my results like this: {excluding the dots}
--------------Closed Calls
Priority-----0-5 Days-------6-10 Days-------11+ Days
-------- ---------------------------------------------
1...................14...............4...............3
2...................1868............342...........516
3...................59...............23.............60
4...................149.............77.............225
5...................0................1...............3
Total..............2090...........447............807
How would I go about doing this?
Any help would be gratefully appreciated.
------------------------------------------------
http://floetichoney.spaces.live.com
------------------------------------------------ -
Try this:
declare @startdate datetime,
@finishdate datetime
select
RM.fldPriorityCode as 'Priority',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5
then 1
else 0
end) as 'Closed Calls 0-5 Days',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10
then 1
else 0
end) as 'Closed Calls 6-10 Days',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11
then 1
else 0
end) as 'Closed Calls 11+ Days'
from
tblRequestMaster RM
where
RM.fldPriorityCode between 1 and 5
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',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5
then 1
else 0
end) as 'Closed Calls 0-5 Days',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10
then 1
else 0
end) as 'Closed Calls 6-10 Days',
sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11
then 1)
else 0
end) as 'Closed Calls 11+ Days'
from
tblRequestMaster RM
where
RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '08-05-2007'
and RM.fldRequestFlag like 'D'
and RM.fldRequestStatus = 'Y'
order by
RM.fldPriorityCode asc
-
Thank you very much Lynn that worked perfectly
------------------------------------------------
http://floetichoney.spaces.live.com
------------------------------------------------
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply