I have a table
Category which has a few missing records for a few months. The table looks like this
Category Month Amount Opt January 12.00 Opt February 0.00 Opt March -1042000.00 Opt April 0.00 Opt May 0.00 Opt June 0.00 Opt July -782663.00 Opt November 0.00 Opt December 0.00
This table has missing rows for a few months. I would like to query and show this data as well.
So I created a month table
MonthNameLong MonthNameShort January Jan February Feb March Mar April Apr May May June Jun July Jul August Aug September Sep October Oct November Nov December Dec
So that I can do a right join on this and get the missing details as well. The query I wrote is
select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount from Category s RIGHT JOIN Month m ON s.Month = m.MonthNameLong
And the result I get is
Category MonthNameLong Amount Opt January 12.00 Opt February 0.00 Opt March -1042000.00 Opt April 0.00 Opt May 0.00 Opt June 0.00 Opt July -782663.00 NULL August 0.00 NULL September 0.00 NULL October 0.00 Opt November 0.00 Opt December 0.00
But instead of NULL category is there a way I can get Opt as the category.
I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0
This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.