英文:
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 = @startDatewhile (@date <= @endDate)begininsert into @monthEnds values (dateadd(day, -1, @date))set @date = dateadd(month, 1, @date)endinsert into @monthEnds values (@endDate)
提取结果：
select s.ID, s.data1, s.data2, m.monthEnd SnapshotDatefrom @source sjoin @monthEnds mon 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)begininsert 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 SnapshotDatefrom @source sjoin @monthEnds mon m.monthEnd between s.DateFrom and s.DateTo
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论