Aggregates with multi-series chart?

(2 posts) (2 voices)
  1. -nth-, Member

    I'm having a tough time figuring this one out. I need a multi-series chart with the series name being the client name, the categories being titles of 3 averages and the series values being the value of those 3 averages (averages are number of days calculated between several dates). I think I'm just missing something on how the data needs pivoted. I'm doing averages because a client may have multiple records and I need the average from all of them. Here's the code:


    IF object_id('sp_DBR_ClientDays', 'P') IS NOT NULL
    DROP PROCEDURE [sp_DBR_ClientDays]
    GO CREATE PROCEDURE sp_DBR_ClientDays @StartDate DATE,
    @EndDate DATE
    AS
    BEGIN
    SELECT 'dbr.chart',
    'MSColumn3D',
    'Client Average days to reach stage 3'; SELECT (SELECT 'Average Days to Stage3' UNION SELECT 'Average Days from Stage1 to Stage2' UNION SELECT 'Average Days from Stage2 to Stage3'),
    r.ClientName,
    (avg(datediff(d, r.Stage1Date, r.Stage3Date)) UNION avg(datediff(d, r.Stage1Date, r.Stage2Date)) UNION avg(datediff(d, r.Stage2Date, r.Stage3Date)))
    FROM mydbr_data AS r
    WHERE r.Stage1Date BETWEEN @StartDate AND @EndDAte
    GROUP BY r.ClientName
    END
    GO

    Thanks!
    -nth-

    Posted 8 years ago #
  2. myDBR Team, Key Master

    Multiseries charts will have an input of form category, series and value. Since in your case your different series are different query sets, the easiest way is to use union.

    select ClientName, 'Average Days from Stage1 to Stage2', avg(datediff(d, r.Stage1Date, r.Stage2Date))
    from mydbr_data r
    where r.Stage1Date between @StartDate and @EndDate
    group by ClientName
    union
    select ClientName, 'Average Days from Stage2 to Stage3', avg(datediff(d, r.Stage2Date, r.Stage3Date))
    from mydbr_data r
    where r.Stage1Date between @StartDate and @EndDate
    group by ClientName
    union
    select ClientName, 'Average Days from Stage1 to Stage3', avg(datediff(d, r.Stage1Date, r.Stage3Date))
    from mydbr_data r
    where r.Stage1Date between @StartDate and @EndDate
    group by ClientName

    If you want to optimize the query to a single query, you can use:

    select ClientName, q.category,
    case
    when q.id = 1 then avg(datediff(d, r.Stage1Date, r.Stage2Date))
    when q.id = 2 then avg(datediff(d, r.Stage2Date, r.Stage3Date))
    when q.id = 3 then avg(datediff(d, r.Stage1Date, r.Stage3Date))
    end
    from mydbr_data r,
    (
    select 1 as 'id', 'Average Days from Stage1 to Stage2' as 'category'
    union
    select 2, 'Average Days from Stage2 to Stage3'
    union
    select 3, 'Average Days from Stage1 to Stage3'
    ) q
    group by ClientName, q.category, q.id

    --
    myDBR Team

    Posted 8 years ago #

Reply

You must log in to post.