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

  • 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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply