Hall of Fame

1. angelIII983,351
2. aneeshatti...741,674
3. matthews...571,487
4. rrjegan17406,769
5. capricorn1406,214
6. mwvisa1393,008
7. ralmada334,041
8. Sharath_...326,005
9. BrandonG...272,108
10. reb73229,974
11. chapmandew221,436
12. peter57r201,096
13. mark_wills177,032
14. boag2000157,180
15. pcelba154,279
16. sdstuber140,116
17. TextReport134,666
18. acperkins132,452
19. DanielWilson125,383
20. DatabaseMX121,464
21. pivar117,472
22. mrjoltcola116,984
23. LSMCons...114,570
24. RiteshShah114,551
25. lwadwell114,353

1. angelIII3,010,791
2. capricorn11,252,886
3. matthews...1,236,223
4. aneeshatti...1,153,277
5. mwvisa1828,426
6. chapmandew710,108
7. mark_wills694,816
8. BrandonG...557,946
9. peter57r494,820
10. ee_rlee460,453
11. rrjegan17406,769
12. sdstuber398,017
13. DatabaseMX391,015
14. Sharath_...365,805
15. dqmq346,705
16. ralmada336,041
17. GRayL330,160
18. DanielWilson323,456
19. boag2000318,493
20. harfang298,054
21. LSMCons...279,439
22. jimhorn238,445
23. reb73229,974
24. acperkins227,845
25. imitchie220,374

	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.8

Visual Studio SSRS and T-SQL Question

Asked by Putoch in SQL Query Syntax, MS SQL Server, Visual Studio

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:

/*For example say this person went active in Jan(which you can see with the month axix on the left) and stayed active in Feb, then
went to Cancelled in MArch and then Active again in April*/

Jan Feb March April
Jan 1 1 0 1

/*My problem is trying to get the above layout, at the moment 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 suggestions on what i need to add or adjust in my code please.
i also have code that someone suggested but it is not suitable (for the amount of data being proceed in
this report it takes hours for the report to run and runs out of time)

*/

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:

           
           
             /* Here is some data i have created 3 different accounts*/
/* 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);
Insert into account values (123456,'2008-03-01 18:34:25.000',1);
Insert into account values (548977,'2008-03-01 18:34:25.000',1);
 
/*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)
 
Insert into account_status_history values (548977,'2007-02-28 18:34:25.000',1)
Insert into account_status_history values (548977,'2008-01-28 18:34:25.000',2)
Insert into account_status_history values (548977,'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)
 
 
 
 /* this is my First try where the restuls are coming back wrong I tend to put these results into a maxtix 
using SSRS in Visual Studio*/
 
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()
 
 
/*this is the one that i was helped with.
The issue i have with this query is that in theory it works fine (for showing 2007)
with this example, as there are only 3 accounts in this example, but if i use this and include the years 2004,05,06,07,08
and then my db has 200k accounts in it this query can not handle this. */
SELECT RIGHT(CONVERT(varchar(11), FirstOrderMonth,113),8) AS FirstOrderMonth
       ,SUM(CASE WHEN month([Month])= 1 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Jan2007
       ,SUM(CASE WHEN month([Month])= 2 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Feb2007
       ,SUM(CASE WHEN month([Month])= 3 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Mar2007
       ,SUM(CASE WHEN month([Month])= 4 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Apr2007
       ,SUM(CASE WHEN month([Month])= 5 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS May2007
       ,SUM(CASE WHEN month([Month])= 6 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Jun2007
       ,SUM(CASE WHEN month([Month])= 7 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Jul2007
       ,SUM(CASE WHEN month([Month])= 8 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Aug2007
       ,SUM(CASE WHEN month([Month])= 9 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Sep2007
       ,SUM(CASE WHEN month([Month])= 10 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Oct2007
       ,SUM(CASE WHEN month([Month])= 11 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Nov2007
       ,SUM(CASE WHEN month([Month])= 12 AND YEAR([Month])=2007 THEN 1 ELSE 0 END) AS Dec2007
 
 
 
 
 
FROM
(
SELECT M.Month, FirstOrderMonth
FROM months1 M,
(
SELECT [Month] AS FirstOrderMonth, account_id
FROM months1 INNER JOIN 
     (SELECT account_id,MIN(account_status_date) AS MinDate
      FROM account_status_history 
      GROUP BY account_id) T ON (MinDate >= [Month] AND MinDate < NextMonthBegin)) A
WHERE account_id IN
(SELECT account_id
FROM account_status_history  H
WHERE account_status_date = (SELECT MAX(account_status_date) FROM account_status_history WHERE account_id=H.account_id AND account_status_date<M.NextMonthBegin)
AND account_status_id=1)
) T
GROUP BY FirstOrderMonth

           
         

Visual Studio SSRS and T-SQL Question

Asked by Putoch in SQL Query Syntax, MS SQL Server, Visual Studio

About this solution