That made it more complicated than I had expected and I had to adopt a different method, unfortunately containing cascading CTEs. Does this get you close?

WITH BaseData1
AS...

Which column, or columns, of data should be considered when generating the Sales = 0 rows?

Well done on providing the sample data, though please use one or more code blocks next time.  In future, please use temp tables (avoids others having to create and drop...

OK, I see the problem. The query logic is such that the generation of a notional Sales = 0 row depends on column 'Data', not on EmpId.

In the case where...

Are you saying that the line in blue is incorrect?

Yes there is.

SQLPackage /Action:Publish, with appropriate parameters.

Set the CreateNewDatabase flag to false.

That looks trivial and is something which I suggest you attempt yourself.

Just add the columns to the BaseData CTE and then to the SELECT ... FROM BaseData.

Please read this article. The FORMAT() function performs very badly.

• This reply was modified 3 years ago by Phil Parkin. Reason: Fix typo

Like this?

SELECT t.VID
      ,t.OrdDate
      ,t.Prg1
      ,NewCol = (CASE
    ...

Your query does not parse (it has two WHERE clauses). But no matter, for (current) YTD, use something like

WHERE SomeDate >= DATEFROMPARTS(YEAR(GETDATE()),1,1) AND SomeDate <= GETDATE()

We use a third party tool 'ControlM' to create jobs, execute them since we have hundreds of jobs which run everyday so the ask is to run the job...

The refinements continue!

WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
          ,Week = rt.yearwk
...

Using aliases for table names is also a good idea. Shortens the query and improves readability.

Any one of those left joins could be increasing the number of rows returned beyond...

• This reply was modified 3 years ago by Phil Parkin.

Is it so difficult to use code blocks?

SELECT dbo.TrainingHeader.TrainingID
      ,dbo.TrainingHeader.TrainingNumber
      ,dbo.TrainingHeader.TrainingTypeID
      ,dbo.TrainingHeader.TrainingParticipantType
...

Try this (as my query gets uglier and uglier!):

WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
      ...