We help IT Professionals succeed at work.

Creating a trend report

439 Views
Last Modified: 2013-11-26
Hi There,
i have created a report which looks back on the last 12 months and give the business,with details showing the product type ,the customer type,how many active customers there were on that month the total Revenue. I am using Visual Studio( on SQL Server 2005)  SSRS  using a matrix to get this result and this is working fine.
However now i would like to add in a new row which will show how many customer stoped/cancelled on a perticular month and i onlly want to use one month column at the top of the report. This is where i am getting confused.

Here is example tables to help see how the code works
here are the tables i used:
/* Holds latest info on account*/
Create table account
(account_id int not null,
Account_status_date datetime,
Account_status_id smallint not null,
setup [decimal](10, 2) NOT NULL,
recurring [decimal](10, 2) NOT NULL,
Product_id [int] NOT NULL );

Insert into account values (123456,'2007-06-28 18:34:25.000',2,50.00,5.20,1001);
Insert into account values (256478,'2007-05-28 18:34:25.000',2,100.00,12.00,1002);
Insert into account values (256479,'2007-04-28 18:34:25.000',1,50.00,5.20,1002,);
Insert into account values (256488,'2007-05-28 18:34:25.000',1,100.00,12.00,1001);
Insert into account values (299488,'2007-06-28 18:34:25.000',2,50.00,5.20,1002);
Insert into account values (356488,'2007-07-28 18:34:25.000',2,100.00,12.00,1001);
Insert into account values (25688,'2007-06-28 18:34:25.000',1,100.00,12.00,1002);
Insert into account values (256100,'2007-06-28 18:34:25.000',1,50.00,5.20,1001);
Insert into account values (56488,'2007-06-28 18:34:25.000',1,50.00,5.50,1002);
Insert into account values (256433,'2007-07-28 18:34:25.000',2,100.00,12.00,1002);

/*stores all hisotry on account*/
Create table account_status_history(
account_status_history_id varchar2(10),
account_id int not null,
account_status_date datetime ,
account_status_id smallint not null);

Insert into account_status_history values (1,123456,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (2,123456,'2007-06-28 18:34:25.000',2);
Insert into account_status_history values (3,256478,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (4,256478,'2007-05-28 18:34:25.000',2);
Insert into account_status_history values (5,256479,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (6,256488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (7,299488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (8,299488,'2007-06-28 18:34:25.000',2);
Insert into account_status_history values (9,356488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (10,356488,'2007-07-28 18:34:25.000',2);
Insert into account_status_history values (11,25688,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (12,256100,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (13,56488,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (14,256433,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (15,256433,'2007-07-28 18:34:25.000',2);


/* Create Month Table */

SET @Date = @StartDate

CREATE TABLE Months (
Month datetime PRIMARY KEY CLUSTERED,
ThisYearBegin datetime,
NextYearBegin datetime,
NextMonthBegin datetime)

WHILE (@Date <= @EndDate)
BEGIN

      INSERT INTO Months (
      Month,
      ThisYearBegin,
      NextYearBegin,
      NextMonthBegin
      ) VALUES (
      @Date,
      CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(dayofyear, @Date)-1)*-1, @Date)))),
      DATEADD(year, 1, CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(dayofyear, @Date)-1)*-1, @Date))))),
      DATEADD(month, 1, CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(day, @Date)-1)*-1, @Date)))))
      )

      SET @Date = DATEADD(month, 1, @Date)

END

CREATE NONCLUSTERED INDEX IX_Months_ThisYearBegin ON Months (ThisYearBegin)
CREATE NONCLUSTERED INDEX IX_Months_NextYearBegin ON Months (NextYearBegin)
CREATE NONCLUSTERED INDEX IX_Months_NextMonthBegin ON Months (NextMonthBegin)

/*Create Product Table*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRODUCT](
      [product_id] [int] NOT NULL,
      [product] [varchar](75) NOT NULL,
      [product_group_id] [int] NOT NULL
      
 CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
(
      [product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Insert into Product Values (1001,'ProductA',7);
Insert into Product values (1002,'ProductB',8);



/*Create Ptoduct Group Table*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRODUCT_GROUP](
      [product_group_id] [smallint] NOT NULL,
      [product_group] [varchar](75) NOT NULL,
      
 CONSTRAINT [PK_PRODUCT_GROUP] PRIMARY KEY CLUSTERED
(
      [product_group_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Insert into Product_Group values (7,'ProductGroupA');
Insert into Product_group values(8,'ProductGroupB');

--New Product trend

SELECT
--subq.account_status_date as ExactActiveDate,
CASE
when m.month = '2009-12-01 00:00:00.000' then 'Dec09'
when m.month = '2009-11-01 00:00:00.000' then 'Nov09'
when m.month = '2009-10-01 00:00:00.000' then 'Oct09'
when m.month = '2009-09-01 00:00:00.000' then 'Sept09'
when m.month = '2009-08-01 00:00:00.000' then 'Aug09'
when m.month = '2009-07-01 00:00:00.000' then 'July09'
when m.month = '2009-06-01 00:00:00.000' then 'Jun09'
when m.month = '2009-05-01 00:00:00.000' then 'May09'
when m.month = '2009-04-01 00:00:00.000' then 'Apr09'
when m.month = '2009-03-01 00:00:00.000' then 'Mar09'
when m.month = '2009-02-01 00:00:00.000' then 'Feb09'
when m.month = '2009-01-01 00:00:00.000' then 'Jan09'

when m.month = '2008-12-01 00:00:00.000' then 'Dec08'
when m.month = '2008-11-01 00:00:00.000' then 'Nov08'
when m.month = '2008-10-01 00:00:00.000' then 'Oct08'
when m.month = '2008-09-01 00:00:00.000' then 'Sept08'
when m.month = '2008-08-01 00:00:00.000' then 'Aug08'
when m.month = '2008-07-01 00:00:00.000' then 'July08'
when m.month = '2008-06-01 00:00:00.000' then 'Jun08'
when m.month = '2008-05-01 00:00:00.000' then 'May08'
when m.month = '2008-04-01 00:00:00.000' then 'Apr08'
when m.month = '2008-03-01 00:00:00.000' then 'Mar08'
when m.month = '2008-02-01 00:00:00.000' then 'Feb08'
when m.month = '2008-01-01 00:00:00.000' then 'Jan08'

when m.month = '2007-12-01 00:00:00.000' then 'Dec07'
when m.month = '2007-11-01 00:00:00.000' then 'Nov07'
when m.month = '2007-10-01 00:00:00.000' then 'Oct07'
when m.month = '2007-09-01 00:00:00.000' then 'Sep07'
when m.month = '2007-08-01 00:00:00.000' then 'Aug07'
When m.month = '2007-07-01 00:00:00.000' then 'July07'
When m.month = '2007-06-01 00:00:00.000' then 'June07'
When m.month = '2007-05-01 00:00:00.000' then 'May07'
When m.month = '2007-04-01 00:00:00.000' then 'April07'
When m.month = '2007-03-01 00:00:00.000' then 'March07'
When m.month = '2007-02-01 00:00:00.000' then 'Feb07'
When m.month = '2007-01-01 00:00:00.000' then 'Jan07'

When m.month = '2006-12-01 00:00:00.000' then 'Dec06'
When m.month = '2006-11-01 00:00:00.000' then 'Nov06'
When m.month = '2006-10-01 00:00:00.000' then 'Oct06'
When m.month = '2006-09-01 00:00:00.000' then 'Sep06'
When m.month = '2006-08-01 00:00:00.000' then 'Aug06'
When m.month = '2006-07-01 00:00:00.000' then 'July06'
When m.month = '2006-06-01 00:00:00.000' then 'June06'
When m.month = '2006-05-01 00:00:00.000' then 'May06'
When m.month = '2006-04-01 00:00:00.000' then 'April06'
When m.month = '2006-03-01 00:00:00.000' then 'March06'
When m.month = '2006-02-01 00:00:00.000' then 'Feb06'
When m.month = '2006-01-01 00:00:00.000' then 'Jan06'  End  as ActivationDate,
--churn.churndate as ExactChurnDate,

CASE
when churn.month = '2009-12-01 00:00:00.000' then 'Dec09'
when churn.month = '2009-11-01 00:00:00.000' then 'Nov09'
when churn.month = '2009-10-01 00:00:00.000' then 'Oct09'
when churn.month = '2009-09-01 00:00:00.000' then 'Sept09'
when churn.month = '2009-08-01 00:00:00.000' then 'Aug09'
when churn.month = '2009-07-01 00:00:00.000' then 'July09'
when churn.month = '2009-06-01 00:00:00.000' then 'Jun09'
when churn.month = '2009-05-01 00:00:00.000' then 'May09'
when churn.month = '2009-04-01 00:00:00.000' then 'Apr09'
when churn.month = '2009-03-01 00:00:00.000' then 'Mar09'
when churn.month = '2009-02-01 00:00:00.000' then 'Feb09'
when churn.month = '2009-01-01 00:00:00.000' then 'Jan09'

when churn.month = '2008-12-01 00:00:00.000' then 'Dec08'
when churn.month = '2008-11-01 00:00:00.000' then 'Nov08'
when churn.month = '2008-10-01 00:00:00.000' then 'Oct08'
when churn.month = '2008-09-01 00:00:00.000' then 'Sept08'
when churn.month = '2008-08-01 00:00:00.000' then 'Aug08'
when churn.month = '2008-07-01 00:00:00.000' then 'July08'
when churn.month = '2008-06-01 00:00:00.000' then 'Jun08'
when churn.month = '2008-05-01 00:00:00.000' then 'May08'
when churn.month = '2008-04-01 00:00:00.000' then 'Apr08'
when churn.month = '2008-03-01 00:00:00.000' then 'Mar08'
when churn.month = '2008-02-01 00:00:00.000' then 'Feb08'
when churn.month = '2008-01-01 00:00:00.000' then 'Jan08'

when churn.month = '2007-12-01 00:00:00.000' then 'Dec07'
when churn.month = '2007-11-01 00:00:00.000' then 'Nov07'
when churn.month = '2007-10-01 00:00:00.000' then 'Oct07'
when churn.month = '2007-09-01 00:00:00.000' then 'Sep07'
when churn.month = '2007-08-01 00:00:00.000' then 'Aug07'
When churn.month = '2007-07-01 00:00:00.000' then 'July07'
When churn.month = '2007-06-01 00:00:00.000' then 'June07'
When churn.month = '2007-05-01 00:00:00.000' then 'May07'
When churn.month = '2007-04-01 00:00:00.000' then 'April07'
When churn.month = '2007-03-01 00:00:00.000' then 'March07'
When churn.month = '2007-02-01 00:00:00.000' then 'Feb07'
When churn.month = '2007-01-01 00:00:00.000' then 'Jan07'

When churn.month = '2006-12-01 00:00:00.000' then 'Dec06'
When churn.month = '2006-11-01 00:00:00.000' then 'Nov06'
When churn.month = '2006-10-01 00:00:00.000' then 'Oct06'
When churn.month = '2006-09-01 00:00:00.000' then 'Sep06'
When churn.month = '2006-08-01 00:00:00.000' then 'Aug06'
When churn.month = '2006-07-01 00:00:00.000' then 'July06'
When churn.month = '2006-06-01 00:00:00.000' then 'June06'
When churn.month = '2006-05-01 00:00:00.000' then 'May06'
When churn.month = '2006-04-01 00:00:00.000' then 'April06'
When churn.month = '2006-03-01 00:00:00.000' then 'March06'
When churn.month = '2006-02-01 00:00:00.000' then 'Feb06'
When churn.month = '2006-01-01 00:00:00.000' then 'Jan06' END ChurnDate,

subq.account_id as Active_Customer,
churn.acc as Churn_Customer,
a.account_status_id,
pg.product_group,

sum(a.recurring)as Total_Recurring_Charges,
sum(setup)as Total_Setup_Fee,
(sum(a.recurring)/count(*))as ARPN
from
 ACCOUNT a
INNER JOIN PRODUCT p on p.product_id = a.product_id
INNER JOIN PRODUCT_GROUP pg on pg.product_group_id = p.product_group_id

--      find the earliest date where the account went active         
      INNER JOIN       (SELECT     MIN(account_status_date) AS account_status_date, account_id
                              FROM          ACCOUNT_STATUS_HISTORY AS ACCOUNT_STATUS_HISTORY_1
                              WHERE      (account_status_id = 1)
                              GROUP BY account_id) AS subq ON subq.account_id = a.account_id

LEFT OUTER JOIN Months AS M  ON  subq.account_status_date BETWEEN M.[Month] AND M.NextMonthBegin



--Finding the churn
Left Join  (select a1.account_id as acc,--case when a1.account_status_id = 3 then 'Cancelled' end ,
                  a1.account_status_date as churndate,m1.month as month
                  from account a1
                        inner join (select max(account_status_history_id)maxid,account_id
                                          FROM account_status_history group by account_id)ahs on ahs.account_id = a1.account_id
                        Left join months m1 on a1.account_status_date between m1.month and m1.nextmonthbegin
                        where a1.account_status_id = 3)churn on churn.acc = a.account_id

Left join months as m2 on m.month = subq.month

Where M.[Month] BETWEEN DATEADD(month, -36, GETDATE()) AND GETDATE()
AND pg.product_group_id in (7,8)

Group by
 M.[Month],
c.customer_type_id,pg.product_group, subq.account_id ,
churn.acc,
--subq.account_status_date,
a.account_id, a.account_status_id,
--a.account_status_date,
--subq.account_status_date ,
m.month ,
churn.acc,
--churn.churndate ,
churn.month
ORDER BY M.[Month] ASC

The Results returned look perfect on paper, but when i want to graph these this is where the problem is
I can use a matrix to do the following

                                                  Months                            Jan                Feb                  March
Product Group| customerType| Active Count                   10                   20                   30
                                                 Sum Recurring Tot          100                 200                  300.00
                                                 Sum of Set up Fee         1000                2000                 3000

Now i want to be able to see the following with Churn included:

                                                   Months                            Jan                Feb                  March
Product Group| customerType| Active Count                   10                   20                   30
                                                 Churn Cust                      2                     3                     1
                                                 Sum Recurring Tot          100                 200                  300.00
                                                 Sum of Set up Fee         1000                2000                 3000

All advise will be appriciated.
Kind Regards,
Putoch

Comment
Watch Question

Can you provide an example of one of the records this is producing now?  I'm having trouble understanding the connection between your data and the matrix

Author

Commented:
Hi, sorry i was just giving an abstract example of what i would like the matrix to look.
Ok i am after running the create tables and sql (excuse the mistakes i orginally had there) i have taken the errors out:

/* Create Month Table */

SET @Date = @StartDate

CREATE TABLE Months (
Month datetime PRIMARY KEY CLUSTERED,
ThisYearBegin datetime,
NextYearBegin datetime,
NextMonthBegin datetime)

WHILE (@Date <= @EndDate)
BEGIN

      INSERT INTO Months (
      Month,
      ThisYearBegin,
      NextYearBegin,
      NextMonthBegin
      ) VALUES (
      @Date,
      CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(dayofyear, @Date)-1)*-1, @Date)))),
      DATEADD(year, 1, CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(dayofyear, @Date)-1)*-1, @Date))))),
      DATEADD(month, 1, CONVERT(datetime, FLOOR(CONVERT(real, DATEADD(day, (DATEPART(day, @Date)-1)*-1, @Date)))))
      )

      SET @Date = DATEADD(month, 1, @Date)

END

CREATE NONCLUSTERED INDEX IX_Months_ThisYearBegin ON Months (ThisYearBegin)
CREATE NONCLUSTERED INDEX IX_Months_NextYearBegin ON Months (NextYearBegin)
CREATE NONCLUSTERED INDEX IX_Months_NextMonthBegin ON Months (NextMonthBegin)

Create table account
(account_id int not null,
Account_status_date datetime,
Account_status_id smallint not null,
setup [decimal](10, 2) NOT NULL,
recurring [decimal](10, 2) NOT NULL,
Product_id [int] NOT NULL );

Insert into account values (123456,'2007-06-28 18:34:25.000',2,50.00,5.20,1001);
Insert into account values (256478,'2007-05-28 18:34:25.000',2,100.00,12.00,1002);
Insert into account values (256479,'2007-04-28 18:34:25.000',1,50.00,5.20,1002);
Insert into account values (256488,'2007-05-28 18:34:25.000',1,100.00,12.00,1001);
Insert into account values (299488,'2007-06-28 18:34:25.000',2,50.00,5.20,1002);
Insert into account values (356488,'2007-07-28 18:34:25.000',2,100.00,12.00,1001);
Insert into account values (25688,'2007-06-28 18:34:25.000',1,100.00,12.00,1002);
Insert into account values (256100,'2007-06-28 18:34:25.000',1,50.00,5.20,1001);
Insert into account values (56488,'2007-06-28 18:34:25.000',1,50.00,5.50,1002);
Insert into account values (256433,'2007-07-28 18:34:25.000',2,100.00,12.00,1002);

/*stores all hisotry on account*/
Create table account_status_history(
account_status_history_id varchar(10),
account_id int not null,
account_status_date datetime ,
account_status_id smallint not null);

Insert into account_status_history values (1,123456,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (2,123456,'2007-06-28 18:34:25.000',2);
Insert into account_status_history values (3,256478,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (4,256478,'2007-05-28 18:34:25.000',2);
Insert into account_status_history values (5,256479,'2007-04-28 18:34:25.000',1);
Insert into account_status_history values (6,256488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (7,299488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (8,299488,'2007-06-28 18:34:25.000',2);
Insert into account_status_history values (9,356488,'2007-05-28 18:34:25.000',1);
Insert into account_status_history values (10,356488,'2007-07-28 18:34:25.000',2);
Insert into account_status_history values (11,25688,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (12,256100,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (13,56488,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (14,256433,'2007-06-28 18:34:25.000',1);
Insert into account_status_history values (15,256433,'2007-07-28 18:34:25.000',2);



/*Create Product Table*/
CREATE TABLE [dbo].[PRODUCT](
      [product_id] [int] NOT NULL,
      [product] [varchar](75) NOT NULL,
      [product_group_id] [int] NOT NULL
     
)

Insert into Product Values (1001,'ProductA',7);
Insert into Product values (1002,'ProductB',8);



/*Create Ptoduct Group Table*/

CREATE TABLE [dbo].[PRODUCT_GROUP](
      [product_group_id] [smallint] NOT NULL,
      [product_group] [varchar](75) NOT NULL
     
)

Insert into Product_Group values (7,'ProductGroupA');
Insert into Product_group values(8,'ProductGroupB');

--New Product trend

SELECT
--subq.account_status_date as ExactActiveDate,
CASE
when m.month = '2009-12-01 00:00:00.000' then 'Dec09'
when m.month = '2009-11-01 00:00:00.000' then 'Nov09'
when m.month = '2009-10-01 00:00:00.000' then 'Oct09'
when m.month = '2009-09-01 00:00:00.000' then 'Sept09'
when m.month = '2009-08-01 00:00:00.000' then 'Aug09'
when m.month = '2009-07-01 00:00:00.000' then 'July09'
when m.month = '2009-06-01 00:00:00.000' then 'Jun09'
when m.month = '2009-05-01 00:00:00.000' then 'May09'
when m.month = '2009-04-01 00:00:00.000' then 'Apr09'
when m.month = '2009-03-01 00:00:00.000' then 'Mar09'
when m.month = '2009-02-01 00:00:00.000' then 'Feb09'
when m.month = '2009-01-01 00:00:00.000' then 'Jan09'

when m.month = '2008-12-01 00:00:00.000' then 'Dec08'
when m.month = '2008-11-01 00:00:00.000' then 'Nov08'
when m.month = '2008-10-01 00:00:00.000' then 'Oct08'
when m.month = '2008-09-01 00:00:00.000' then 'Sept08'
when m.month = '2008-08-01 00:00:00.000' then 'Aug08'
when m.month = '2008-07-01 00:00:00.000' then 'July08'
when m.month = '2008-06-01 00:00:00.000' then 'Jun08'
when m.month = '2008-05-01 00:00:00.000' then 'May08'
when m.month = '2008-04-01 00:00:00.000' then 'Apr08'
when m.month = '2008-03-01 00:00:00.000' then 'Mar08'
when m.month = '2008-02-01 00:00:00.000' then 'Feb08'
when m.month = '2008-01-01 00:00:00.000' then 'Jan08'

when m.month = '2007-12-01 00:00:00.000' then 'Dec07'
when m.month = '2007-11-01 00:00:00.000' then 'Nov07'
when m.month = '2007-10-01 00:00:00.000' then 'Oct07'
when m.month = '2007-09-01 00:00:00.000' then 'Sep07'
when m.month = '2007-08-01 00:00:00.000' then 'Aug07'
When m.month = '2007-07-01 00:00:00.000' then 'July07'
When m.month = '2007-06-01 00:00:00.000' then 'June07'
When m.month = '2007-05-01 00:00:00.000' then 'May07'
When m.month = '2007-04-01 00:00:00.000' then 'April07'
When m.month = '2007-03-01 00:00:00.000' then 'March07'
When m.month = '2007-02-01 00:00:00.000' then 'Feb07'
When m.month = '2007-01-01 00:00:00.000' then 'Jan07'

When m.month = '2006-12-01 00:00:00.000' then 'Dec06'
When m.month = '2006-11-01 00:00:00.000' then 'Nov06'
When m.month = '2006-10-01 00:00:00.000' then 'Oct06'
When m.month = '2006-09-01 00:00:00.000' then 'Sep06'
When m.month = '2006-08-01 00:00:00.000' then 'Aug06'
When m.month = '2006-07-01 00:00:00.000' then 'July06'
When m.month = '2006-06-01 00:00:00.000' then 'June06'
When m.month = '2006-05-01 00:00:00.000' then 'May06'
When m.month = '2006-04-01 00:00:00.000' then 'April06'
When m.month = '2006-03-01 00:00:00.000' then 'March06'
When m.month = '2006-02-01 00:00:00.000' then 'Feb06'
When m.month = '2006-01-01 00:00:00.000' then 'Jan06'  End  as ActivationDate,
--churn.churndate as ExactChurnDate,

CASE
when churn.month = '2009-12-01 00:00:00.000' then 'Dec09'
when churn.month = '2009-11-01 00:00:00.000' then 'Nov09'
when churn.month = '2009-10-01 00:00:00.000' then 'Oct09'
when churn.month = '2009-09-01 00:00:00.000' then 'Sept09'
when churn.month = '2009-08-01 00:00:00.000' then 'Aug09'
when churn.month = '2009-07-01 00:00:00.000' then 'July09'
when churn.month = '2009-06-01 00:00:00.000' then 'Jun09'
when churn.month = '2009-05-01 00:00:00.000' then 'May09'
when churn.month = '2009-04-01 00:00:00.000' then 'Apr09'
when churn.month = '2009-03-01 00:00:00.000' then 'Mar09'
when churn.month = '2009-02-01 00:00:00.000' then 'Feb09'
when churn.month = '2009-01-01 00:00:00.000' then 'Jan09'

when churn.month = '2008-12-01 00:00:00.000' then 'Dec08'
when churn.month = '2008-11-01 00:00:00.000' then 'Nov08'
when churn.month = '2008-10-01 00:00:00.000' then 'Oct08'
when churn.month = '2008-09-01 00:00:00.000' then 'Sept08'
when churn.month = '2008-08-01 00:00:00.000' then 'Aug08'
when churn.month = '2008-07-01 00:00:00.000' then 'July08'
when churn.month = '2008-06-01 00:00:00.000' then 'Jun08'
when churn.month = '2008-05-01 00:00:00.000' then 'May08'
when churn.month = '2008-04-01 00:00:00.000' then 'Apr08'
when churn.month = '2008-03-01 00:00:00.000' then 'Mar08'
when churn.month = '2008-02-01 00:00:00.000' then 'Feb08'
when churn.month = '2008-01-01 00:00:00.000' then 'Jan08'

when churn.month = '2007-12-01 00:00:00.000' then 'Dec07'
when churn.month = '2007-11-01 00:00:00.000' then 'Nov07'
when churn.month = '2007-10-01 00:00:00.000' then 'Oct07'
when churn.month = '2007-09-01 00:00:00.000' then 'Sep07'
when churn.month = '2007-08-01 00:00:00.000' then 'Aug07'
When churn.month = '2007-07-01 00:00:00.000' then 'July07'
When churn.month = '2007-06-01 00:00:00.000' then 'June07'
When churn.month = '2007-05-01 00:00:00.000' then 'May07'
When churn.month = '2007-04-01 00:00:00.000' then 'April07'
When churn.month = '2007-03-01 00:00:00.000' then 'March07'
When churn.month = '2007-02-01 00:00:00.000' then 'Feb07'
When churn.month = '2007-01-01 00:00:00.000' then 'Jan07'

When churn.month = '2006-12-01 00:00:00.000' then 'Dec06'
When churn.month = '2006-11-01 00:00:00.000' then 'Nov06'
When churn.month = '2006-10-01 00:00:00.000' then 'Oct06'
When churn.month = '2006-09-01 00:00:00.000' then 'Sep06'
When churn.month = '2006-08-01 00:00:00.000' then 'Aug06'
When churn.month = '2006-07-01 00:00:00.000' then 'July06'
When churn.month = '2006-06-01 00:00:00.000' then 'June06'
When churn.month = '2006-05-01 00:00:00.000' then 'May06'
When churn.month = '2006-04-01 00:00:00.000' then 'April06'
When churn.month = '2006-03-01 00:00:00.000' then 'March06'
When churn.month = '2006-02-01 00:00:00.000' then 'Feb06'
When churn.month = '2006-01-01 00:00:00.000' then 'Jan06' END ChurnDate,

subq.account_id as Active_Customer,
churn.acc as Churn_Customer,
a.account_status_id,
pg.product_group,

sum(a.recurring)as Total_Recurring_Charges,
sum(setup)as Total_Setup_Fee,
(sum(a.recurring)/count(*))as ARPN
from
 ACCOUNT a
INNER JOIN PRODUCT p on p.product_id = a.product_id
INNER JOIN PRODUCT_GROUP pg on pg.product_group_id = p.product_group_id

--      find the earliest date where the account went active        
      INNER JOIN       (SELECT     MIN(account_status_date) AS account_status_date, account_id
                              FROM          ACCOUNT_STATUS_HISTORY AS ACCOUNT_STATUS_HISTORY_1
                              WHERE      (account_status_id = 1)
                              GROUP BY account_id) AS subq ON subq.account_id = a.account_id

LEFT OUTER JOIN Months AS M  ON  subq.account_status_date BETWEEN M.[Month] AND M.NextMonthBegin



--Finding the churn
Left Join  (select a1.account_id as acc,--case when a1.account_status_id = 3 then 'Cancelled' end ,
                  a1.account_status_date as churndate,m1.month as month
                  from account a1
                        inner join (select max(account_status_history_id)maxid,account_id
                                          FROM account_status_history group by account_id)ahs on ahs.account_id = a1.account_id
                        Left join months m1 on a1.account_status_date between m1.month and m1.nextmonthbegin
                        where a1.account_status_id = 3)churn on churn.acc = a.account_id


Where M.[Month] BETWEEN DATEADD(month, -36, GETDATE()) AND GETDATE()
AND pg.product_group_id in (7,8)

Group by
m.month,
churn.month,
subq.account_id ,
churn.acc,
a.account_status_id,
pg.product_group

ORDER BY M.[Month] ASC


So when i create the matrix it looks like this
In the Rows Section put ProductGroup
In the Colunms Section put the Activation Date
in the Data section: Active Customer Count,Sum Total Recurring ,Sum total Setup fee, Sum ARPN

The Data then looks like this:
                                               
                                                                                       April                             May07                   Jun07
ProductGroup A         Active Customer                          1                                      2                           1
                                  Total Recurring Charges             5.20                                 24                       5.20
                                  Total Set Fee                              50                                    200                        50
                                   ARPN                                      5.20                                     24                       5.20

ProductB                   Active Customer                      2                                         1                              3
                                 Total Recurring Charges        17.20                                  5.20                        29.50
                                 Total Setup Fee                     150.00                                 50                          250.00
                                 ARPN                                    17.20                                   5.20                         29.50


But this is the way i want the Data to look when i add in the Churn

                                                                                       April                May07        Jun07           July 07
ProductGroup A         Active Customer                          1                      2                1                    0
                                   Churn                                          0                      0                 1                    1
                                  Total Recurring Charges             5.20                 24               5.20               0
                                  Total Set Fee                              50                    200              50                  0
                                   ARPN                                      5.20                    24               5.20               0

ProductB                   Active Customer                      2                        1                   3                  0
                                  Churn                                      0                        1                   1                  1
                                Total Recurring Charges        17.20                 5.20                29.50           0
                                 Total Setup Fee                     150.00                50                  250.00         0
                                 ARPN                                    17.20                  5.20                29.50          0

So the problem i am having is trying to get the churn in teh Matrix and only use one Month(period) header . Does this makes sense.

Thanks Putoch.
What is the churn measure, sum, count, etc...?

Your result appears now as ActivationDate, ChurnDate, Active_Customer, Churn_Customer, account_status_id, product_group, Total_Recurring_Charges, Total_Setup_Fee, ARPN

It looks like you want to add your churn measure as one of your select fields so your result looks like:
ActivationDate, ChurnDate, Active_Customer, Churn_Customer, account_status_id, product_group, Total_Recurring_Charges, Total_Setup_Fee, ARPN, SomeChurnMeasure

Does that make sense?


Author

Commented:
''What is the churn measure, sum, count, etc...?''
This would be count


''It looks like you want to add your churn measure as one of your select fields so your result looks like:''
See i have added in the ChurnDate and churn count in the script to bring back the reults just on a text editor so i can see the raw data of how many people started and when and stoped and when .
The problem i am having is HOW to actually put this together in Visual Studio when using a matrix(or pivot) to bring back the results how i want (as explaned above).

I think i should only have one Month colunm in my script (where at teh moment i have 2 one for active and one for chrun) but i only want one if you get me.
For example
i want to be able to see jan feb march april may june ......
       Cust Starts  Count   1     2      3        1     4        5
      Cust Stops   Count   0     1       2       3      1        0  

I'm just not sure how to write this into my code that i have, i dont know how i can use one month colum to link with the Cust Starts and the Cust Stops.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok this is workin but if i don't use the condition AND churn.month = m.month as this will only bring back cancelled customers instead of both.

Thanks for you help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.