Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  T-SQL Query Creating a Trend Report

Author  Topic  putoch Starting Member 1 Post Posted - 2008-03-12 : 10:49:08 Hi there, I am trying to create a trend analysis report. i want to be able to see the count of customers who started each month and to see within the following months how many of these customers stayed active. i have attached code examples of the type of data i am dealing with. the result i am looking for is like follows: i am only going to use one account to make it simpler Jan Feb March April Jan 1 1 0 1 So this guy went active in Jan , was still active in Feb, went to cancelled for example in March, and then back to active in Aptil. My problem is trying to get this at themoment i am only getting this result: Jan Feb March April Jan 1 1 0 0 Feb 0 0 0 0 March 0 0 0 0 April 0 0 0 0 Can anyone give me some sugesstions on what i need to add or adjust in my code please. here is some example data of wha ti am looking for: /* Holds latest info on account Account status id 1 = Active 2 = Cancelled*/ Create table account (account_id int not null, Account_status_date datetime, Account_status_id smallint not null); Insert into account values (356488,'2007-07-28 18:34:25.000',2); /*stores all hisotry on account*/ Create table account_status_history( account_id int not null, account_status_date datetime , account_status_id smallint not null); Insert into account_status_history values (356488,'2007-01-28 18:34:25.000',1); Insert into account_status_history values (356488,'2007-03-28 18:34:25.000',2); Insert into account_status_history values (356488,'2007-04-28 18:34:25.000',1); Insert into account_status_history values (356488,'2007-07-28 18:34:25.000',2); Insert into account_status_history values (123456,'2007-02-28 18:34:25.000',1) Insert into account_status_history values (123456,'2007-06-28 18:34:25.000',2) Insert into account_status_history values (123456,'2008-03-01 18:34:25.000',1) /* Create Month Table */ DECLARE @StartDate datetime DECLARE @EndDate datetime DECLARE @Date datetime SET @StartDate = '1/1/1900' SET @EndDate = '1/1/2100' 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) SELECT StartMonth.Month AS FirstOrderMonth, OrderMonth.Month AS [Month], (SELECT Count(*) FROM (SELECT (FirstOrder1.Account_Status_Date) AS FirstOrderDate, FirstOrder1.Account_ID FROM Account_Status_History FirstOrder1 WHERE FirstOrder1.Account_Status_ID = 1 and FirstOrder1.account_id = 356488 -- GROUP BY FirstOrder1.Account_ID ) FirstOrder WHERE FirstOrder.FirstOrderDate BETWEEN StartMonth.Month AND StartMonth.NextMonthBegin AND Account_ID NOT IN (SELECT Account_ID FROM Account_Status_History AccClosed WHERE AccClosed.Account_Status_ID = 2 AND AccClosed.Account_Status_Date BETWEEN StartMonth.Month AND OrderMonth.NextMonthBegin) ) AS RetainedCustomers FROM Months StartMonth FULL OUTER JOIN Months OrderMonth ON OrderMonth.Month>=StartMonth.Month where StartMonth.Month BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE() and OrderMonth.Month BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE() Kind Regards, Putoch.