SQL force insert dummy data

Question

I have a CTE with the following statement to obtain the total break time of a user in a day.

CTEBreak as
(
    select max(name) as Name
          ,sum(DurationInHours) as HourlyBreak
          ,Work_Day
    from ctesemifinal
    where id in (4130)
    group by Work_Day
)

This returns results like:

However if they did not take a break for a particular day, it results into:

where some days are not included because he didn't take breaks. What I want is to force insert a '1' in the days that the user didn't take breaks. How do I accomplish this? Thank you!

Btw, the Work_Day column is a datename of an actual date (just in case it might come to play)

Answer 1

You need to provide SQL Server with all the data you want to include in your query, either from a table in your database, via calculation or by explicitly including it.

In your case, you can explicitly include it here by adding another cte that includes your days of the week and left joining from that to your CTEBreak:

with
...
,CTEBreak as (...)
,CTEWorkdays(wd) as (select 'Monday' union all
                     select 'Tuesday' union all
                     select 'Wednesday' union all
                     select 'Thursday' union all
                     select 'Friday'
                    )
select isnull(b.HourlyBreak,1) as HourlyBreak
      ,w.wd as Work_Day
from CTEWorkdays as w
    left join CTEBreak as b
        on w.wd = b.Work_Day

Answer 2

I want to note that you can often solve this sort of problem with conditional aggregation:

select max(case when id = 4130 then name end) as Name,
       max(case when id = 4130 then DurationInHours else 1 end) as HourlyBreak,
       Work_Day
from ctesemifinal
group by Work_Day;

This assumes that all work days are in the original data. This approach can be simpler than the left join under some circumstances.