Return a row for each time increment if it does not exist

I have a query that returns the amount of jobs received per 15 minutes increments of everyday for a year. The problem is that it only returns data for each of the 15 minute increments if a job was received in that timeframe. The succinct version of the query is:

SELECT 
DATEPART(MONTH, Datim) AS Month#,
DATEPART(DAY, Datim) AS Day#,
(DATEPART(HOUR, Datim) + 1) AS Hour#,
((DATEPART(MINUTE, Datim) / 15) + 1) AS Interval#,
COUNT(JobNumber) AS TotalJobs

FROM Table

WHERE (Datim >= '2020-01-01' AND  Datim <= '2021-01-01')

GROUP BY
DATEPART(MONTH, Datim),
DATEPART(DAY, Datim),
(DATEPART(HOUR, Datim) + 1),
((DATEPART(MINUTE, Datim) / 15) + 1)

ORDER BY
DATEPART(MONTH, Datim),
(DATEPART(DAY, Datim) + 0),
(DATEPART(HOUR, Datim) + 1),
((DATEPART(MINUTE, Datim) / 15) + 1)

This is currently the output that is getting returned:

I would like the data output to fill in a 0 for the TotalJobs field for each of the 15 minute increments as well as hours if they do not exist. The final output would look like this:

Any ideas on what might be the best way to achieve this?

Possible solution with a tally table.

Sample data

Move your current query to a subquery or common table expression (CTE) so you can do a left join with it. Here, I just recreated the result rows from your current query.

create table AvailableData
(
  Month int,
  Day int,
  Hour int,
  Interval int,
  TotalJobs int
);

insert into AvailableData (Month, Day, Hour, Interval, TotalJobs) values
(1, 1, 1, 3, 123),
(1, 1, 2, 4, 456),
(1, 1, 4, 1, 789);

Solution

Gives you the first two months of 2021. Update the CTE's Tally and Intervals to get more incremental numbers and more months.

with Tally (n) as
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM       (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n) -- 10^1 = 10
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- 10^2 = 100
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- 10^3 = 1000
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- 10^4 = 10000
),
Intervals as
(
  select top (2*31*24*4) -- increase as required, max determined by Tally, example for 2 months
       --t.n,
       --dateadd(day, (t.n-1)/(24*4), '2021-01-01')                   as [Date],
         datepart(month, dateadd(day,  (t.n-1)/(24*4), '2021-01-01')) as [Month],
         datepart(day,   dateadd(day,  (t.n-1)/(24*4), '2021-01-01')) as [Day],
         (((t.n-1) % (24*4)))/4 +1                                    as [Hour],
         ( (t.n-1) %     4 )    +1                                    as [Interval]
  from Tally t
)
select i.Month,
       i.Day,
       i.Hour,
       i.Interval,
       coalesce(ad.TotalJobs, 0) as TotalJobs
from Intervals i
left join AvailableData ad
  on  ad.Month = i.Month
  and ad.Day = i.Day
  and ad.Hour = i.Hour
  and ad.Interval = i.Interval;

Fiddle with some intermediate results.

Full solution

Quick merge and edit for the full year of 2021. Not validated.

with Tally (n) as
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (      VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n) -- 10^1 = 10
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- 10^2 = 100
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- 10^3 = 1000
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- 10^4 = 10000
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- 10^5 = 100000
),
Intervals as
(
  select top (365*24*4) -- full year of 2021
         datepart(month, dateadd(day,  (t.n-1)/(24*4), '2021-01-01')) as [Month],
         datepart(day,   dateadd(day,  (t.n-1)/(24*4), '2021-01-01')) as [Day],
         (((t.n-1) % (24*4)))/4 +1                                    as [Hour],
         ( (t.n-1) %     4 )    +1                                    as [Interval]
  from Tally t
),
AvailableData as
(
  SELECT 
  DATEPART(MONTH, Datim) AS [Month],
  DATEPART(DAY, Datim) AS [Day],
  (DATEPART(HOUR, Datim) + 1) AS [Hour],
  ((DATEPART(MINUTE, Datim) / 15) + 1) AS [Interval],
  COUNT(JobNumber) AS TotalJobs
  
  FROM Table
  
  WHERE (Datim >= '2020-01-01' AND  Datim <= '2021-01-01')
  
  GROUP BY
  DATEPART(MONTH, Datim),
  DATEPART(DAY, Datim),
  (DATEPART(HOUR, Datim) + 1),
  ((DATEPART(MINUTE, Datim) / 15) + 1)
)
select i.Month,
       i.Day,
       i.Hour,
       i.Interval,
       coalesce(ad.TotalJobs, 0) as TotalJobs
from Intervals i
left join AvailableData ad
  on  ad.Month = i.Month
  and ad.Day = i.Day
  and ad.Hour = i.Hour
  and ad.Interval = i.Interval
order by i.Month,
         i.Day,
         i.Hour,
         i.Interval;