none
SCD2 - To add an EffectiveTo date parameter to close off past transaction RRS feed

  • Question

  • 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?


    Monday, February 13, 2017 1:54 AM

All replies

  • 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


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 14, 2017 7:33 AM
    Moderator