英文:
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
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论