sql - Conditional Logic within SUM -

sql - Conditional Logic within SUM -

i'm combining 2 tables through union query , performing sum , grouping operations on result. working expected, have unique requirement can't seem figure out how implement. aim write sql says "when dev_age column >= 12 set revenue value if column 12". provide code below know description can bit confusing:

revenue table:

acc_yr dev_age state revenue loss 2012 3 ma 4000 0 2012 6 ma 8000 0 2012 9 ma 12000 0 2012 12 ma 16000 0

loss table:

acc_yr dev_age state revenue loss 2012 3 ma 0 2000 2012 6 ma 0 7000 2012 9 ma 0 9000 2012 12 ma 0 10000 2012 15 ma 0 14000 2012 18 ma 0 14000 2012 21 ma 0 14000 2012 24 ma 0 15000 2012 27 ma 0 17000

table after union all, grouping by, sum:

acc_yr dev_age state revenue loss 2012 3 ma 4000 2000 2012 6 ma 8000 7000 2012 9 ma 12000 9000 2012 12 ma 16000 10000 2012 15 ma 0 14000 2012 18 ma 0 14000 2012 21 ma 0 14000 2012 24 ma 0 15000 2012 27 ma 0 17000

what want accomplish:

acc_yr dev_age state revenue loss 2012 3 ma 4000 2000 2012 6 ma 8000 7000 2012 9 ma 12000 9000 2012 12 ma 16000 10000 2012 15 ma 16000 14000 2012 18 ma 16000 14000 2012 21 ma 16000 14000 2012 24 ma 16000 15000 2012 27 ma 16000 17000

in other words, revenue stops developing @ dev_age of 12 (there no rows in revenue table beyond dev_age of 12), want every dev_age beyond 12 equal revenue @ 12 in final table.

here approach uses window functions calculate revenue age 12 , logic assign it:

select acc_yr, dev_age, state, (case when dev_age > 12 rev12 else revenue end) revenue, loss (select l.acc_yr, l.dev_age, l.state, r.revenue, l.loss, max(case when l.dev_age = 12 r.revenue end) on (partition l.acc_yr, l.state) rev12 loss l left bring together revenue r on l.acc_yr = r.acc_yr , l.dev_age = r.dev_age , l.state = dev.state ) lr;

sql sql-server