T-SQL Query

--current status on account
Create table account
(account_id int,
date_entered datetime, 
account_Status_id int, 
account_status_date datetime)
 
insert into account values (3905,'2004-09-22 00:00:00.000',1,'2007-05-11 14:06:37.000')
insert into account values (127304,'2006-04-29 12:48:37.000',3,'2007-10-03 15:28:57.000')
 
 
--historic status on account
create table account_status_history(
account_status_history_id int,
account_id int, 
account_status_id int, 
account_status_date datetime)
 
insert into account_status_history values (337699,3905,1,'2007-05-11 14:06:37.000')
insert into account_status_history values (228446,3905,2,'2007-01-04 10:43:37.000')
insert into account_status_history values (100,3905,1,'2006-04-29 12:48:37.000')
 
 
insert into account_status_history values(435449,127304,3,'2007-10-03 15:28:57.000')
insert into account_status_history values (1837,127304,1,'2006-04-29 12:48:37.000')
 
 
 
select 
 
--a.account_id,
--Active_Months.months_active,
--Prior06Act.MONTHS_ACTIVE1,
a.account_id,Active_Months.Months_Active
,Prior06Act.Months_active1
 
from 
account a 
--If they were active after the 29-04-06 then they will be accounted for here 
LEFT JOIN (select a.account_id , a.account_status_date,-- n.account_status_id, 
			n.account_status_date as end_date,
				sum(datediff(m,a.Account_status_Date,isnull(n.Account_Status_Date,getdate()))) as MONTHS_ACTIVE
				  from account_status_history a
				  left join account_status_history n
					on n.account_id = a.account_id
				   and n.account_status_history_id = (select top 1 i.account_status_history_id from account_status_history i where i.account_id = a.account_id and i.account_status_history_id > a.account_status_history_id order by i.account_status_date asc )
				 where a.account_status_id = 1
				group by a.account_id , a.account_status_date,  n.account_status_date 
						) Active_Months  on   Active_Months.account_id = a.account_id
 
 
--If they were active before the 29-04-06 then they will be accounted for here
LEFT JOIN (select a.account_id, a.date_entered, b.account_status_date,
		sum(datediff(m,a.date_entered,isnull(b.Account_Status_Date,getdate()))) as MONTHS_ACTIVE1
		From account a 
		left join account_Status_history b  on b.account_id = a.account_id
		and b.account_status_history_id = (select top 1 g.account_status_history_id from account_Status_history g where g.account_id  = a.account_id and g.account_status_date > a.date_entered order by g.account_status_date asc)
		where a.account_status_id = 3 
		group by a.account_id,a.date_entered, b.account_status_date
	 )Prior06Act on Prior06Act.account_id = a.account_id

                                  

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: