|
-- execute part 1 1st then part 2.
--part 1
select
L01, L03, A05, A15, A26, A04 , case when (A04<>35 and (A27<'1 september 2009' and (A31>='1 august 2009' or A31 is NULL))) then 1 else 0 end as A_IL_P1_qry , case when A04<>35 and A27<'1 october 2009' and (A31>='1 september 2009' or A31 is NULL) then 1 else 0 end as A_IL_P2_qry , case when A04<>35 and A27<'1 november 2009' and (A31>='1 october 2009' or A31 is NULL) then 1 else 0 end as A_IL_P3_qry , case when A04<>35 and A27<'1 december 2009' and (A31>='1 november 2009' or A31 is NULL) then 1 else 0 end as A_IL_P4_qry , case when A04<>35 and A27<'1 january 2010' and (A31>='1 december 2009' or A31 is NULL) then 1 else 0 end as A_IL_P5_qry , case when A04<>35 and A27<'1 february 2010' and (A31>='1 january 2010' or A31 is NULL) then 1 else 0 end as A_IL_P6_qry , case when A04<>35 and A27<'1 march 2010' and (A31>='1 february 2010' or A31 is NULL) then 1 else 0 end as A_IL_P7_qry , case when A04<>35 and A27<'1 april 2010' and (A31>='1 march 2010' or A31 is NULL) then 1 else 0 end as A_IL_P8_qry , case when A04<>35 and A27<'1 may 2010' and (A31>='1 april 2010' or A31 is NULL) then 1 else 0 end as A_IL_P9_qry , case when A04<>35 and A27<'1 june 2010' and (A31>='1 may 2010' or A31 is NULL) then 1 else 0 end as A_IL_P10_qry , case when A04<>35 and A27<'1 july 2010' and (A31>='1 june 2010' or A31 is NULL) then 1 else 0 end as A_IL_P11_qry , case when A04<>35 and A27<'1 august 2010' and (A31>='1 july 2010' or A31 is NULL) then 1 else 0 end as A_IL_P12_qry
into #aimlevel
from
ILR0910_E_AIMS
--part 2
select
B.L01, B.L03, A05 , case when A04=35 then P_IL_P1 else A_IL_P1_qry end as A_IL_P1 , case when A04=35 then P_IL_P2 else A_IL_P2_qry end as A_IL_P2 , case when A04=35 then P_IL_P3 else A_IL_P3_qry end as A_IL_P3 , case when A04=35 then P_IL_P4 else A_IL_P4_qry end as A_IL_P4 , case when A04=35 then P_IL_P5 else A_IL_P5_qry end as A_IL_P5 , case when A04=35 then P_IL_P6 else A_IL_P6_qry end as A_IL_P6 , case when A04=35 then P_IL_P7 else A_IL_P7_qry end as A_IL_P7 , case when A04=35 then P_IL_P8 else A_IL_P8_qry end as A_IL_P8 , case when A04=35 then P_IL_P9 else A_IL_P9_qry end as A_IL_P9 , case when A04=35 then P_IL_P10 else A_IL_P10_qry end as A_IL_P10 , case when A04=35 then P_IL_P11 else A_IL_P11_qry end as A_IL_P11 , case when A04=35 then P_IL_P12 else A_IL_P12_qry end as A_IL_P12
from ( select
L01, L03, A15, A26 , max(A_IL_P1_qry) as P_IL_P1 , max(A_IL_P2_qry) as P_IL_P2 , max(A_IL_P3_qry) as P_IL_P3 , max(A_IL_P4_qry) as P_IL_P4 , max(A_IL_P5_qry) as P_IL_P5 , max(A_IL_P6_qry) as P_IL_P6 , max(A_IL_P7_qry) as P_IL_P7 , max(A_IL_P8_qry) as P_IL_P8 , max(A_IL_P9_qry) as P_IL_P9 , max(A_IL_P10_qry) as P_IL_P10 , max(A_IL_P11_qry) as P_IL_P11 , max(A_IL_P12_qry) as P_IL_P12
from ( select
L01, L03, A05, A15, A26, A04 , case when (A04<>35 and (A27<'1 september 2009' and (A31>='1 august 2009' or A31 is NULL))) then 1 else 0 end as A_IL_P1_qry , case when A04<>35 and A27<'1 october 2009' and (A31>='1 september 2009' or A31 is NULL) then 1 else 0 end as A_IL_P2_qry , case when A04<>35 and A27<'1 november 2009' and (A31>='1 october 2009' or A31 is NULL) then 1 else 0 end as A_IL_P3_qry , case when A04<>35 and A27<'1 december 2009' and (A31>='1 november 2009' or A31 is NULL) then 1 else 0 end as A_IL_P4_qry , case when A04<>35 and A27<'1 january 2010' and (A31>='1 december 2009' or A31 is NULL) then 1 else 0 end as A_IL_P5_qry , case when A04<>35 and A27<'1 february 2010' and (A31>='1 january 2010' or A31 is NULL) then 1 else 0 end as A_IL_P6_qry , case when A04<>35 and A27<'1 march 2010' and (A31>='1 february 2010' or A31 is NULL) then 1 else 0 end as A_IL_P7_qry , case when A04<>35 and A27<'1 april 2010' and (A31>='1 march 2010' or A31 is NULL) then 1 else 0 end as A_IL_P8_qry , case when A04<>35 and A27<'1 may 2010' and (A31>='1 april 2010' or A31 is NULL) then 1 else 0 end as A_IL_P9_qry , case when A04<>35 and A27<'1 june 2010' and (A31>='1 may 2010' or A31 is NULL) then 1 else 0 end as A_IL_P10_qry , case when A04<>35 and A27<'1 july 2010' and (A31>='1 june 2010' or A31 is NULL) then 1 else 0 end as A_IL_P11_qry , case when A04<>35 and A27<'1 august 2010' and (A31>='1 july 2010' or A31 is NULL) then 1 else 0 end as A_IL_P12_qry
from
ILR0910_E_AIMS )T
group by
L01, L03, A15, A26 )T2 join #aimlevel B
on T2.L01=B.L01 and T2.L03=B.L03
order by
L01, L03, A05 |