SQL Server: combine only dates with no reference

Question

I have a data such as

Table 1: (after converting data into the format I need with the below query later in this question after the left join)

Initially has details of tickets such as date, ticket number, ticket type.

Monthyear   Premiumold   Silverold
-----------------------------------
Jan 2019      233           156
Feb 2019      344           258
Mar 2019      222           298

Table 2: which I predicted and pushed from a different source in the same format

Monthyear   Premium   silver
----------------------------
Apr 2019     284       312
May 2019     267       344 
Jun 2019     223       356
Jul 2019     244       367
Aug 2019     234       373

I want to get this data to be in a format such as:

Monthyear   Premiumold   Silverold      Premium   silver
---------------------------------------------------------
    Jan2019      233           156       NULL      NULL     
    Feb 2019     344           258       NULL      NULL
    Mar 2019     222           298       NULL      NULL
    Apr 2019    NULL           NULL      284       312
    May 2019    NULL           NULL      267       344 
    Jun 2019    NULL           NULL      223       356
    Jul 2019    NULL           NULL      244       367
    Aug 2019    NULL           NULL      234       373

which basically puts the months together and leaves out NULL in wherever data isn't present for.

I have tried:

select * 
from 
    ((select Monthyear, Premium, Silver 
      from [dbo].[Predicted]) c
      left join 
         (select 
              case when (tickettype = 'Premium') 
                   then count(number) 
              end as Premiumold,
              case when (tickettype = 'Silver') 
                   then count(number) 
              end as Silverold,
              concat(convert(char(3), a.date, 0), ' ', year(a.date)) as Monthyear 
          from 
              openquery(SNOW, 'select number,date, ticket_type from ticketdata 
where date between ''2019-01-01 00:00:00'' and ''2019-02-28 23:59:59''')a
group by concat(convert(char(3), a.sys_created_on, 0),' ',year(a.date)),tickettype) as b
on c.Monthyear = b.Monthyear)

This obviously isn't returning what I want.

Please help me with this.

Thanks!

Answer 1

Try this.

select ISNULL(a.monthyear,b.monthyear),a.Premiumold,silverold,Premium,silver from Table1 a 
full join Table2 b on a.monthyear=b.MonthYear