SCD2 - 添加EffectiveTo日期参数以关闭过去的事务 [英] SCD2 - To add an EffectiveTo date parameter to close off past transaction

问题描述

我试图在MS ACCESS表上创建某种有效性（日期范围）字段。它需要按ID，主题和级别分组才能得出每条记录的有效期。如果没有相应的测试来提高成绩，那么迄今为止有效的
将设置为31/12/9999。

我现在有下表：

 <代码样式="边距：0px;填充：0px;边框：0px;字体大小：13px;字体系列：Consolas，Menlo，Monaco，'Lucida控制台'，'解放单声道'，'DejaVu Sans Mono'，'Bitstream Vera Sans Mono'，'Courier New'，monospace，sans-serif; white-space：inherit">  ID   |    主题    |    等级    |     FromGrade     |     ToGrade     |     TestDate   
   101     |    数学    |     5     |   C   +     |   D   |     31   /   11   /   2016   
   101     |    数学    |     4     |   D   |   A   |     01   /   12   /   2016   
   101     |    数学    |     5     |   D   |   B   +     |     12   /   12   /   2016   
   101     |    数学    |     5     |   B   +     |   A   |     25   /   12   /   2016   
   102     |    英语    |     4     |   B   |   B   |     20   /   12   /   2016   
   102     |    英语    |     4     |   B   |   C   |     28   /   12   /   2016   

最终结果表我应该得到以下内容：

   ID   |    主题    |    等级    |     FromGrade     |     ToGrade     |     TestDate     |     EffectiveTo   
   101     |    数学    |     5     |   C   +     |   D   |     31   /   11   /   2016     |     11   /   12   /   2016   
   101     |    数学    |     4     |   D   |   A   |     01   /   12   /   2016     |     31   /   12   /   9999   
   101     |    数学    |     5     |   D   |   B   +     |     12   /   12   /   2016     |     24   /   12   /   2016   
   101     |    数学    |     5     |   B   +     |   A   |     25   /   12   /   2016     |     31   /   12   /   9999   
   102     |    英语    |     4     |   B   |   B   |     20   /   12   /   2016     |     27   /   12   /   2016   
   102     |    英语    |     4     |   B   |   C   |     28   /   12   /   2016     |     31   /   12   /   9999   

要在SQL中执行此操作，我可以轻松使用LEAD OVER函数：

   SELECT   [  ID  ]   
  ，    [ 主题 ]   
  ，    [ 等级 ]   
  ，    [  FromGrade  ]   
  ，    [  ToGrade  ]   
  ，    [  TestDate  ]   
  ，    [  EffectiveTo  ]     =   LEAD  （[  TestDate  ]，    1  ）  OVER  （  PARTITION BY   [  ID  ]，    [  Subject  ]，    [  Level  ]   ORDER BY   [  TestDate  ]    ）  
  进入    StudentTable2   
 FROM   [  dbo  ]。[  StudentTable1  ]   
 ORDER BY   [  ID  ]，    [ 主题 ]，    [ 等级 ]，    [  TestDate  ]   

然后跟着一个

  更新  [  dbo  ]。  StudentTable2set     [  EffectiveTo  ]     =   DATEADD  （ " DAY"  ，     -    1  ，    [  EffectiveTo  ]）   其中    EffectiveTo        不    null   

但是，这不适用于Ms / VBA Ms脚本，有没有其他方法可以达到相同的结果？

解决方案

您好，

是否是TestDate日期字段或字符串/文本字段？我认为没有2016/11/31。

Access中没有LEAD OVER功能，所以我们可能需要 来获取结果功能的逻辑。根据
LEAD（Transact-SQL），LEAD是用于在不使用SQL Server 2016中的自联接的情况下从同一结果集中的后续行访问数据.LEAD提供对当前行之后的给定
物理偏移量的行的访问。 SELECT语句中的分析函数，用于将当前行中的值与后续行中的值进行比较。

为了得到预期结果，我认为首先我们需要o获得如下表所示的结果：使用OrderID来标识应插入哪个日期作为新字段。

Sub         拉特           ID              Date   OrderID OrderID2

Math        4           101         2016-01-12  1  NULL

数学        5           101         2016-11-30  2  3

数学        5           101         2016-12-12  3  4

数学        5           101         2016-12-25  4  NULL

英文     4           102         2016-12-20  5  6

English     4           102         2016-12-28  6  NULL

OrderID / OrderID2引用行号

如果OrderID2为null，则为新字段EffectiveTo应为默认值。

如果OrderID2不为null，则新字段EffectiveTo应为OrderID2 = OrderID的日期。

获取结果如上，我们可以插入或更新字段EffectiveTo

问候，

Celeste

I am trying to create some sort of Validity (Date Range) fields on my MS ACCESS table. It will need to be group by ID, Subject and Level to derive the effective period of each record. If there is no corresponding test taken to improve the grade, the effective to date will be set to 31/12/9999.

I have the following table now:

ID  | Subject | Level | FromGrade | ToGrade | TestDate
101 | Math    |  5    | C+        | D       | 31/11/2016
101 | Math    |  4    | D         | A       | 01/12/2016
101 | Math    |  5    | D         | B+      | 12/12/2016
101 | Math    |  5    | B+        | A       | 25/12/2016
102 | English |  4    | B         | B       | 20/12/2016
102 | English |  4    | B         | C       | 28/12/2016

The end resulting table i should be getting the following:

ID  | Subject | Level | FromGrade | ToGrade | TestDate   | EffectiveTo
101 | Math    |  5    | C+        | D       | 31/11/2016 | 11/12/2016
101 | Math    |  4    | D         | A       | 01/12/2016 | 31/12/9999
101 | Math    |  5    | D         | B+      | 12/12/2016 | 24/12/2016
101 | Math    |  5    | B+        | A       | 25/12/2016 | 31/12/9999
102 | English |  4    | B         | B       | 20/12/2016 | 27/12/2016
102 | English |  4    | B         | C       | 28/12/2016 | 31/12/9999

To do this in SQL, i could easily use the LEAD OVER function:

SELECT [ID]
, [Subject]
, [Level]
, [FromGrade]
, [ToGrade]
, [TestDate]
, [EffectiveTo] = LEAD([TestDate], 1) OVER ( PARTITION BY [ID], [Subject], [Level]  ORDER BY [TestDate] )
into StudentTable2
FROM [dbo].[StudentTable1]
ORDER BY [ID], [Subject], [Level], [TestDate]

and then follow by a

update [dbo].StudentTable2set [EffectiveTo] = DATEADD("DAY", -1, [EffectiveTo]) where EffectiveTo is not null

However, this is not working on Ms Access/VBA script, is there any other way i can achieve the same results?

解决方案

Hi，

Is TestDate Date field or string/text field? I think there is no 2016/11/31.

There is no LEAD OVER function in Access, so we might need to get the result following the logic of the function. According to LEAD (Transact-SQL), LEAD is used to access data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

To get the expected result, I think firstly we need to get result like the following table: use OrderID to identify which date should be inserted as the new field.

Sub         lvl          ID             Date  OrderID OrderID2
Math       4          101        2016-01-12 1 NULL
Math       5          101        2016-11-30 2 3
Math       5          101        2016-12-12 3 4
Math       5          101        2016-12-25 4 NULL
English    4          102        2016-12-20 5 6
English    4          102        2016-12-28 6 NULL

OrderID/OrderID2 refers to the row number

If OrderID2 is null, then the new field EffectiveTo should be default value.

If OrderID2 is not null, the new field EffectiveTo should be the Date where OrderID2=OrderID.

After getting the result above, we could then insert or update the field EffectiveTo

Regards,

Celeste

这篇关于SCD2 - 添加EffectiveTo日期参数以关闭过去的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！