May 10, 2007 at 8:12 am
--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) >=11
group 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) >=11
order 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
------------------------------------------------
May 10, 2007 at 9:00 am
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
May 10, 2007 at 9:28 am
Thank you very much Lynn that worked perfectly
------------------------------------------------
http://floetichoney.spaces.live.com
------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy