0

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.