SQL code to insert missing records

[Anonymous]

Hi,

I have a table that looks somthing like below:

And want to insert a new row (yellow below) if a certain record is not repeated in the next period/date. In this example have the account number changed during the year and would need a null value for the period where the change happened in order to calculate a MTD value (source data in YTD values).

My table consist of more than 20m records and I would prefer to do this with SQL statement to run the command on the on-prem server instead of in Power Query.

Any help/guidance would be much appreciated!

Thanks

Alexander

[Anonymous]

I would prefer to only have that record created in 2022-08-01 in the above example. However, it would work in my use case if the record was repeated following months, although it would add unneccessary data.

[Daryl-Lynch-Bzy]

@Anonymous Hi Alex, I was wondering, do you want 2022-09-01 and 2022-10-01 to also show that "3110 | X4P" are 0, or to show only for the 2022-08-01?

Unfortunately, the request to do this in SQL is beyond the scope of this Power BI Power Query forum.  How you achieve this in SQL will depend on the how that environment is configured.  You will need to speak to the DBA or Developers.

Many thanks

Daryl