select statment please help 1

[Crystalboy1]

I want to do a select statement to fill a table . the field in the tables are as follows

CustomerID Amount band 1 Amount band 2 Amount band 3 Amount band 4 …..Amount band 12
the band selection criteria given below.

• Period Band 1 - Up to 1 year
• Period Band 2 - Over 1 but not over 2 years
• Period Band 3 - Over 2 but not over 5 years
• Period Band 4 - Over 5 but not over 10 years
• Period Band 5 - Over 10 but not over 15 years
• Period Band 6 - Over 15 but not over 20 years
• Period Band 7 - Over 20 but not over 25 years
• Period Band 8 - Over 25 but not over 30 years
• Period Band 9 - Over 30 but not over 35 years
• Period Band 10 - Over 35 but not over 40 years
• Period Band 11- Over 40 but not over 45 years
• Period Band 12 - Over 45 years


For ‘period band 1’ I will do like
Select cu.customerID, Sum(cu.amount) from customeramount cu
Where cu.agreementenddate between 31/03/2010 band 31/03/2011
Group by cu.custmoerid

For band 2
Select cu.customerID, Sum(cu.amount) from customeramount cu
Where cu.agreementenddate between 31/03/2011 band 31/03/2013
Group by cu.custmoerid

Same way for all bands

Now I want a single insert and select statement, I don’t want to do multiple update statements as it will make ti really slow.

 

[Dagon]

insert into table
select cu.customerID, 
       Sum(case when cu.agreementenddate between '31/03/2010' and '31/03/2011' then cu.amount else 0 end),
...
from customeramount  cu
Group by cu.custmoerid

Retired (not by choice) Oracle contractor.