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, 3 months 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, 3 months 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))
      ...

One more question though. When we assign primary key to a column it creates clustered index by default on that column but I know that in future I will be...

Referring to staging tables as temporary tables is confusing!

For each of the staging tables, create a stored procedure. This stored proc should take care of 'changing the things you want'...

Are you inserting from the temporary tables to the same 'permanent' table in each case? Or are there different targets?

100,000,0 is not a number – please clarify.

I'd suggest doing the...

I'd be interested to hear your reasoning.