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

Reply

You must log in to post.