|
-- execute part by part in order
--Part 1
select
L01, L03, A05 , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTEXP-ROUND(TOTEXP/3,0) else TOTEXP end as TOTEXP , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTACT-ROUND(TOTACT/3,0) else TOTACT end as TOTACT
into #TOTACT_TOTEXP
from ( select
L01, L03, A05 , datediff(dd,A27,A31)+1 as TOTACT , datediff(dd,A27,A28)+1 as TOTEXP , A_ACTIVE, A17
from
LR_Aims )T
--Part 2
select
B.L01, B.L03 , case when B.L_ACTIVE=1 and (B.L_INYR_EXPECTED_GLH>=450 or B.L_INYR_ACTUAL_GLH>=450) then 1 when B.L_ACTIVE=1 and L_COMPLETE=N and (B.L_INYR_ACTUAL_GLH/datediff("ww",L_A27_1,L_A31_1)>16) then 2 when B.L_ACTIVE=1 and (B.L_INYR_EXPECTED_GLH/datediff("ww",L_A27_1,L_A28_1)>16) then 2 when B.L_ACTIVE=1 and (L_ACTPER1>150 or L_ACTPER2>150 or L_ACTPER3>150) then 2 when B.L_ACTIVE=1 and L_TEMPMODE=3 then 4 when B.L_ACTIVE=1 and L_TEMPMODE=2 then 5 when B.L_ACTIVE=1 and L_TEMPMODE=1 then 6 when B.L_ACTIVE=1 then 3 else -1 end as L_MODE_B
from ( select
L01, L03 , count(A05) as N , case when A_ACTIVE=1 then sum(ACTPER1) end as L_ACTPER1 , case when A_ACTIVE=1 then sum(ACTPER2) end as L_ACTPER2 , case when A_ACTIVE=1 then sum(ACTPER3) end as L_ACTPER3 , case when A_ACTIVE=1 then max(A27_1) end as L_A27_1 , case when A_ACTIVE=1 then max(A28_1) end as L_A28_1 , case when A_ACTIVE=1 then max(A31_1) end as L_A31_1 , case when A_ACTIVE=1 then sum(A_COMPLETE) end as L_COMPLETE , max(A_TEMPMODE) as L_TEMPMODE
from ( select
T.L01, T.L03, T.A05, A_ACTIVE, A27_1, A28_1, A31_1 , case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 august 2009' and '31 december 2009') then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 august 2009' and '31 december 2009') then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER1
, case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A31)<datediff(dd,'1 january 2010',A31)) then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A31)>datediff(dd,'1 january 2010',A31)) then (datediff(dd,'1 january 2010',A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A28)<datediff(dd,'1 january 2010',A28)) then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A28)>datediff(dd,'1 january 2010',A28)) then (datediff(dd,'1 january 2010',A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER2
, case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A31)<datediff(dd,'1 april 2010',A31)) then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A31)>datediff(dd,'1 april 2010',A31)) then (datediff(dd,'1 april 2010',A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A28)<datediff(dd,'1 april 2010',A28)) then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A28)>datediff(dd,'1 april 2010',A28)) then (datediff(dd,'1 april 2010',A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER3
, case when A_ACTIVE=1 and A18=2 then 3 when A_ACTIVE=1 and A18=3 then 2 when A_ACTIVE=1 and A17=2 then 1 else 0 end as A_TEMPMODE
, case when A34=2 then 1 else 0 end as A_COMPLETE
from ( select
L01, L03, A05, A_ACTIVE, A17, A18, A31, A27, A28, A_INYR_EXPECTED_GLH, A_INYR_ACTUAL_GLH, A34 , case when A31>'31 july 2010' then '31 july 2010' else A31 end as A31_1 , case when A28>'31 july 2010' then '31 july 2010' else A28 end as A28_1 , case when A27<'01 august 2009' then '01 august 2009' else A27 end as A27_1
from
LR_Aims )T join #TOTACT_TOTEXP a
on a.L01=T.L01 and a.L03=T.L03 and a.A05=T.A05
)T2
where A_ACTIVE=1
group by
L01, L03, A_ACTIVE
)T3
right outer join LR_Learner B
on T3.L01=B.L01 and T3.L03=B.L03 |