Spliting rows, creating snap table from scd type 2

Question

I have a SQL Server DB and I need to create snapshot table from sdc type2 table. I need a row for each item for end of every month it existed. For current month I need data as for DATEADD(day, -1, CAST(GETDATE() AS date))

I have data like below

ID	data1	data2	DateFrom	DateTo
1	AA	ABC	2022-11-01	2022-12-25
1	AA	XYZ	2022-12-26	9999-12-31
2	BB	BCD	2023-01-13	2023-02-14
2	BB	YTW	2023-02-15	2023-03-17
3	CC	CDE	2022-11-01	2022-12-30
3	CC	RTY	2022-12-31	2022-03-10
3	CC	WER	2022-03-11	2022-03-19
3	CC	QWE	2022-03-20	9999-12-31

Need to have it like this

ID	data1	data1	SnapshotDate
1	AA	ABC	2022-11-30
1	AA	XYZ	2022-12-31
1	AA	XYZ	2023-01-31
1	AA	XYZ	2023-02-28
1	AA	XYZ	2023-03-31
1	AA	XYZ	2023-04-11
2	BB	BCD	2023-01-31
2	BB	YTW	2023-02-28
3	CC	CDE	2022-11-30
3	CC	RTY	2022-12-31
3	CC	RTY	2023-01-31
3	CC	RTY	2023-02-28
3	CC	QWE	2023-03-31
3	CC	QWE	2023-04-11

Appreciate every advice you can give me.

Answer 1

As @thom-a has commented, a table of dates to join to is useful here.

In the below case I have generated a table variable and projected month end dates into it to fit your requirements - you might want to use a temporary table or actually build a calendar table as suggested.

I make no guarantees about the efficiency of using this approach "as is" for large data sets :-)

Note that your expected results table seems to be missing 12-months worth of results for the "CC/QWE" row - if my solution is correct.

Here I set up your source data (again in a table variable for convenience):

declare @source table (ID int, data1 varchar(10), data2 varchar(10), DateFrom date, DateTo date)
insert into @source values
  (1, 'AA', 'ABC', '2022-11-01', '2022-12-25')
, (1, 'AA', 'XYZ', '2022-12-26', '9999-12-31')
, (2, 'BB', 'BCD', '2023-01-13', '2023-02-14')
, (2, 'BB', 'YTW', '2023-02-15', '2023-03-17')
, (3, 'CC', 'CDE', '2022-11-01', '2022-12-30')
, (3, 'CC', 'RTY', '2022-12-31', '2022-03-10')
, (3, 'CC', 'WER', '2022-03-11', '2022-03-19')
, (3, 'CC', 'QWE', '2022-03-20', '9999-12-31')

We need a start and end date to bracket the projection of dates:

-- Get the earlest "FromDate" ...
declare @startDate Date = (select min(DateFrom) from @source)
-- ...and then the first day of that month.
set @startDate = DateAdd(day, 1 - datepart(day, @startDate), @startDate)

-- End date (according to the question) is "yesterday"
declare @endDate Date = dateadd(day, -1, getdate())

Create and populate a table of the dates we want in the results.

-- Declare a table to store the dates.
declare @monthEnds table (monthEnd Date)

-- @date will have an initial value of the first day of the earliest month
--   As we add months in the loop, we'll always have the first of the month in @date, 
--   so we can substract a day to get the end of the prior month.
declare @date Date = @startDate

-- loop through the months adding the EOM date to the table.
while (@date <= @endDate)
begin
    insert into @monthEnds values (dateadd(day, -1, @date))
    set @date = dateadd(month, 1, @date)
end
-- the final day in the expected results appears to be "today", but I'm going to assume it's meant to be the "yesterday" date you reference in your question..
insert into @monthEnds values (@endDate)

Pull the results:

-- Now it's an easy join to pull the results.
select s.ID, s.data1, s.data2, m.monthEnd SnapshotDate
from @source s
join @monthEnds m
  on m.monthEnd between s.DateFrom and s.DateTo