[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating a trend report

Posted on 2007-10-10
6
Medium Priority
?
424 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

0
Comment
Question by:Putoch
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20052291
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
0
 

Author Comment

by:Putoch
ID: 20055544
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.
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20056441
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?


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Putoch
ID: 20056699
''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.
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 900 total points
ID: 20068818
Will adding a condition to your churn join help?
left outer join (selct...) churn on churn.acc = a.account_id
AND churn.month = m.month
0
 

Author Comment

by:Putoch
ID: 20272275
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question