Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Question Unanswered: SCD Type 2 "Dimentional Table Load

    I am trying to update my Dimention table during the load process with Last_Trans_Flag set to 'A' -active or 'I' -inactive. The Last_Trans_Flag for the new record being inserted is set to 'A' and the previous records for the customer are set to 'I'.

    During the initial load of the SCD, the first occurrence of each customer will be set an 'I' flag and the most recent
    customer with max(effective_date) will be set to 'A' .

    I need the query to accomplish this. Thanks!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    There are a number of ways to improve overall performance, but a brute force first attempt could be:
    UPDATE Dimension
       SET Last_Trans_Flag = CASE
          WHEN effective_date = (SELECT Max(b.effective_date)
             FROM Dimension AS b
             WHERE  b.customer = Dimension.customer) THEN 'A' ELSE 'I' END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts