Displaying annual data from current year and previous year side by side

Question

SO I created a temp table that would dump monthly data per carrier and policy number. What I want to output is for these two table to output the data side by side so I can have SSRS aggregate the data from current minus the previous year value. Here is my example code:

select carrier, 
       PolicyNumber,
       sum(AnnualPremium) CurrentYearAnnualPremium
from #totalinforcepremium
group by carrier, 
         PolicyNumber
order by PolicyNumber

select carrier, 
       PolicyNumber,
       sum(AnnualPremium) PreviousYearAnnualPremium
from #TotalInforcePremiumPreviousYear
group by carrier, 
         PolicyNumber
order by PolicyNumber

Here is the results from both of these tables:

This is my desired output:

I want it to output this way so I can easily do an aggregate between the two years in SSRS. I actually don't need to return the carrier column as a result since the policynumber includes the carrier name. I am thinking I need to do an outer apply? I am new to outer apply and I am not really sure how to go about it. Thanks!

Answer 1

I would suggest full outer join:

with y as (
      select carrier, PolicyNumber, sum(AnnualPremium) CurrentYearAnnualPremium
      from #totalinforcepremium
      group by carrier, PolicyNumber
     ),
     yp as (
      select carrier, PolicyNumber, sum(AnnualPremium) as CurrentYearAnnualPremium
      from #TotalInforcePremiumPreviousYear
      group by carrier, PolicyNumber
     )
select coalesce(y.carrier, yp.carrier) as carrier,
       coalesce(y.policynumber, yp.policynumber) as policynumber,
       y.CurrentYearAnnualPremium, yp.CurrentYearAnnualPremium
from y full outer join
     yp
     on yp.carrier = y.carrier and yp.policynumber = y.policynumber
order by PolicyNumber;

I should note that storing different years in separate tables in a bad idea. You should just store all years in a single table. Then the query would be a (simple) conditional aggregation query.

In fact, given that these are temporary tables, the original data would probably result in a simpler query -- and a faster one as well.