SQL Recursive Select

Rickzzz
Rickzzz used Ask the Experts™
on
I have a table with with 3 columns I need to key on to build triangle data.

EffectiveMonth = 01/2017, 02/2017, 03/2017... to current month
TransactionEffectiveMonth = 01/2017, 02/2017, 03/2017... to current month
TotalPremium = 20.00

I need to sum each effective month as of each transaction effective month.

So, I need the sum of TotalPremium for each EffectiveMonth, as of each TransactionEffectiveMonth so that the data returned is represented as shown in the attached image.

EffectiveMonth only needs to be evaluted as of 2 years out. Hopefully the attached image defines that for you as well.

Thanks in advance!!
Triangle Data
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
this is feasible without a recursive query

select effectiveMonth,transactionEffectiveMonth,sum(TotalPremium) from table group by effectiveMonth,transactionEffectiveMonth, order by effectiveMonth,transactionEffectiveMonth

if your existing transactions are stored with complete dates

select concat(month(effectiveDate,'/',year(effectiveDate))) as effectiveMonth ...

Author

Commented:
Man did I overthink that... thanks!
Good to know and happy coding. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial