sql - sqlserver 2005 Splitting a date range into consecutive years -

i trying take 2 dates in record, , split them defined years bands. these bands/buckets year. i've been @ while don't seem able starting point in year two!

some test data:

if object_id('tempdb..##policyuwyear') not null          begin             drop table ##policyuwyear         end      create table ##policyuwyear         (          policynumber [nvarchar](50)           ,policystartdate datetime         ,policyenddate datetime         )     on  [primary]  insert ##policyuwyear values ('rmo33032207' ,  '2014-03-04 00:00:00.000','2017-03-04 00:00:00.000'  )     insert ##policyuwyear values ('rmo33000999' ,  '2013-10-04 00:00:00.000','2016-10-04 00:00:00.000'  )      insert ##policyuwyear values ('amo05000888' ,  '2014-09-04 00:00:00.000','2014-12-04 00:00:00.000'  )      insert ##policyuwyear values ('qqo05000333' ,  '2014-10-04 00:00:00.000','2015-10-04 00:00:00.000'  )  

this creates table looks this:

what want splitting defined buckets.. below (not complete using test data):

this confused few things.. biggest 1 each underwriting year starts on october 10th , ends of september 30th - meaning record can start in august 2014 still part of 2013 underwriting year.

for example '2013 2014 underwritingyear start' 2013-10-01 , 2013 2014 underwritingyear end 2014-09-30.

records can of various lengths... 3 or 5 years.. can shorter! basically, it's finance, customers have odd , ideas...

any gratefully received - started thinking quite straightforward, , found wasn't. having said might be!

the logic here cumbersome, can it. requires lot of conditional aggregation. think following encapsulates logic:

select policynumber,        max(case when policyenddate <= '2013-09-30' null                 when policystartdate <= '2014-09-30'                 dateadd(year,                              datediff(month, policystartdate, '2014-09-30') / 12,                              policystartdate                             )            end) uw_start_2013,        max(case when policystartdate > '2014-09-30' null                 when policyenddate > '2013-09-30' , policyenddate <= '2014-09-30'                 policyenddate                 when policyenddate > '2014-09-30'                 dateadd(year,                              1 + (datediff(month, policystartdate, '2014-09-30') / 12),                              policystartdate                             )            end) uw_end_2013,        max(case when policyenddate <= '2013-09-30' null                 when policystartdate <= '2015-09-30'                 dateadd(year,                              datediff(month, policystartdate, '2015-09-30') / 12,                              policystartdate                             )            end) uw_start_2014,        max(case when policystartdate > '2015-09-30' null                 when policyenddate > '2014-09-30' , policyenddate <= '2015-09-30'                 policyenddate                 when policyenddate > '2015-09-30'                 dateadd(year,                              1 + (datediff(month, policystartdate, '2015-09-30') / 12),                              policystartdate                             )            end) uw_end_2014 policyuwyear group policynumber; 

here sql fiddle.