Sfrgttk

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.

I have updated your fiddle with:

• add one more category for to see the query is correct when there is more than 1 category


• alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.

Edited fiddle.

The final query is

With 

Categories AS (

  SELECT DISTINCT Category

  FROM Category

  ),

MonthCategories AS (

  SELECT m.MonthNameLong, cat.Category

  FROM Month m, Categories cat

  )



select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount

FROM MonthCategories mc

LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong

                      AND cat.Category = mc.Category;

Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.

PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.