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.
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