Spliting rows, creating snap table from scd type 2

huangapple go评论58阅读模式
英文:

Spliting rows, creating snap table from scd type 2

问题

我有一个SQL Server数据库,我需要从sdc类型2表创建快照表。
我需要为每个项目的每个月的结束创建一行。对于当前月份,我需要数据如下:DATEADD(day, -1, CAST(GETDATE() AS date))

我有以下数据:

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

需要转化为以下格式:

ID data1 data2 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

希望这对你有帮助。

英文:

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.

答案1

得分: 0

根据@thom-a的评论,这里使用的日期表格很有用。

在下面的情况下,我生成了一个表变量,并将月底日期投影到其中,以符合您的要求 - 您可能希望使用一个临时表或实际上建立一个建议的日历表。

我不保证对于大型数据集使用这种方法“原样”是有效的:-)

注意,您的预期结果表似乎缺少“CC/QWE”行的12个月的结果 - 如果我的解决方案正确的话。

这里我设置了您的源数据(再次使用表变量方便起见):

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')

我们需要起始日期和结束日期来限定日期的投影:

declare @startDate Date = (select min(DateFrom) from @source)
set @startDate = DateAdd(day, 1 - datepart(day, @startDate), @startDate)

declare @endDate Date = dateadd(day, -1, getdate())

创建并填充我们想要在结果中的日期表:

declare @monthEnds table (monthEnd Date)

declare @date Date = @startDate

while (@date <= @endDate)
begin
	insert into @monthEnds values (dateadd(day, -1, @date))
	set @date = dateadd(month, 1, @date)
end

insert into @monthEnds values (@endDate)

提取结果:

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
英文:

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 Spliting rows, creating snap table from scd type 2

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, &#39;AA&#39;, &#39;ABC&#39;, &#39;2022-11-01&#39;, &#39;2022-12-25&#39;)
, (1, &#39;AA&#39;, &#39;XYZ&#39;, &#39;2022-12-26&#39;, &#39;9999-12-31&#39;)
, (2, &#39;BB&#39;, &#39;BCD&#39;, &#39;2023-01-13&#39;, &#39;2023-02-14&#39;)
, (2, &#39;BB&#39;, &#39;YTW&#39;, &#39;2023-02-15&#39;, &#39;2023-03-17&#39;)
, (3, &#39;CC&#39;, &#39;CDE&#39;, &#39;2022-11-01&#39;, &#39;2022-12-30&#39;)
, (3, &#39;CC&#39;, &#39;RTY&#39;, &#39;2022-12-31&#39;, &#39;2022-03-10&#39;)
, (3, &#39;CC&#39;, &#39;WER&#39;, &#39;2022-03-11&#39;, &#39;2022-03-19&#39;)
, (3, &#39;CC&#39;, &#39;QWE&#39;, &#39;2022-03-20&#39;, &#39;9999-12-31&#39;)

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

-- Get the earlest &quot;FromDate&quot; ...
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 &quot;yesterday&quot;
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&#39;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 &lt;= @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 &quot;today&quot;, but I&#39;m going to assume it&#39;s meant to be the &quot;yesterday&quot; date you reference in your question..
insert into @monthEnds values (@endDate)

Pull the results:

-- Now it&#39;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

huangapple
  • 本文由 发表于 2023年4月11日 17:08:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75984178.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定