Fill missing months on a date query

Question

I am working on Microsoft SQL Server 2017. I'm trying to fill the missing months in a data query.

The query is:

SELECT Concat(Datename(mm, temp1.InvoiceDate), '-', Datepart(yy, Getdate())) AS 'Date' , temp1.[Partner], Count(temp1.issues) AS 'Total' 
FROM   temp1 
GROUP  BY temp1.InvoiceDate, temp1.[partner] 
ORDER  BY Datepart(mm, temp1.InvoiceDate), temp1.[partner];

Don't mind about the getdate, since I'll always be working on the actual year data.

And it shows me that result:

Date        Partner             Total
April-2020  Enterprise1, S.A.   1
May-2020    Enterprise2, S.A.   1
July-2020   Enterprise2, S.A.   2

Which is correct, but I need to have the missing months too until the actual one (the getdate actual month), where there are no data.

Something like:

Date           Partner           Total
January-2020   N/A                0
February-2020  N/A                0
March-2020     N/A                0
April-2020     Enterprise1, S.A.  1
May-2020       Enterprise2, S.A.  1
June-2020      N/A                0
July-2020      Enterprise2, S.A.  2
August-2020    N/A                0
September-2020 N/A                0

Also, I don't want the months from the actual one showing on.

PS: I can't use T-SQL code (declare, etc.) in my development environment.

Thanks in advance.

Answer 1

I would use a recursive CTE to produce the dates for the year:

with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, t1.partner, count(t1.issues)
from dates d left join
     temp1 t1 
     ON t1.InvoiceDate >= d.yyyymm AND
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, t1.partner
order by d.yyyymm, t1.partner;

Note some changes:

• This returns the date. It does not format it as you have. You can format it however you like.
• It returns 0 rather than "N/A". 0 seems more appropriate.

The above will actually only work for one partner (so it works for the example in the question). I suspect you want all the months for all partners. If so, use a cross join to generate the rows and then the left join and group by:

with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, p.partner, count(t1.issues)
from dates d cross join
     (select distinct partner from temp1) p left join
     temp1 t1 
     on t1.partner = p.partner and
        t1.InvoiceDate >= d.yyyymm and
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, p.partner
order by d.yyyymm, p.partner;

Answer 2

You will find in a database only data that you have put in it. I such a case it is recommanded to add some datetime table such as a calendar table (one row per day) and some other tables like month, days, weeks...

When this will be create in your database, the query will be simple and efficient, rather than using recursive queries of table valued function, all things that are consuming a heavy time and process.

For you case, this can be :

CREATE TABLE T_MONTH 
(
MONTH_NUM   TINYINT PRIMARY KEY,
MONTH_NAME  VARCHAR(16) NOT NULL UNIQUE
);
DECLARE @I INT = 1, @D DATE = '2000-01-01'
WHILE @I <= 12
BEGIN
   INSERT INTO T_MONTH
   SELECT @I, DATENAME(month, DATEADD(month, @I - 1, @D));
   SET @I = @I + 1;
END

And the query something like :

SELECT Concat(MONTH_NAME, '-', Datepart(yy, Getdate())) AS 'Date' , 
       COALESCE(temp1.[Partner], 'N/A'), 
       COALESCE(Count(temp1.issues), 0) AS 'Total' 
FROM   temp1 
       RIGHT OUTER JOIN T_MONTH ON MONTH_NAME = Datename(mm, temp1.InvoiceDate)
GROUP  BY MONTH_NAME, temp1.[partner] 
ORDER  BY MONTH_NAME, temp1.[partner];

Answer 3

An alternative to a recursive CTE could be a query with a numbers (sometimes aka 'tally') table. There are only 12 months so it could just be enumerated in the code and then no recursion is needed. Also, to input years I added a variable that takes a comma separated list. The example in this case has 2 years, 2020 and 2021

Something like this

declare
  @report_yrs  varchar(100)='2020,2021';

with ent_cte(InvoiceDate, [Partner], [issues]) as
    (select '2020-03-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-04-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-05-01', 'Enterprise1, S.A.', 'x'
     union all
     select '2020-12-01', 'Enterprise1, S.A.', 'x')
SELECT Concat(Datename(mm, dfp.dfp), '-', Datepart(yy, dfp.dfp)) AS [Date],
       isnull(t.[Partner], 'N/A') [Partner], Count(t.issues) AS [Total] 
FROM string_split(@report_yrs, ',') sp
     cross join
     (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(mo)
     cross apply
     (select datefromparts(cast(sp.[value] as int), v.mo, 1) dfp) dfp
     left join 
     ent_cte t on dfp.dfp=t.InvoiceDate
GROUP  BY dfp.dfp, t.[partner] 
ORDER  BY dfp.dfp, t.[partner];

Output

Date            Partner Total
January-2020    N/A     0
February-2020   N/A     0
March-2020      Enterprise1, S.A.   1
April-2020      Enterprise1, S.A.   1
May-2020        Enterprise1, S.A.   1
June-2020       N/A     0
July-2020       N/A     0
August-2020     N/A     0
September-2020  N/A     0
October-2020    N/A     0
November-2020   N/A     0
December-2020   Enterprise1, S.A.   1
January-2021    N/A     0
February-2021   N/A     0
March-2021      N/A     0
April-2021      N/A     0
May-2021        N/A     0
June-2021       N/A     0
July-2021       N/A     0
August-2021     N/A     0
September-2021  N/A     0
October-2021    N/A     0
November-2021   N/A     0
December-2021   N/A     0