SQL

[Floetic]

--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-5'
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-5'
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:

 

 

Priority    Closed Calls

              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.

 

 

 

 

 

 

 

[Floetic]

 

 

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

 

 

I hope that someone else can find this helpful too