none
Update a table as of Date

    Domanda

  •   I am having version history table with two columns for Start and End  Date

    I need to perform an update on this table and need to perform the update as of certain date ( Do not table the version table to get the current system date )

    Example 

    I want to perform an update as of 1 month ago , So the rows should have End date = ( 1 month ago - 1 Day ) and the new row to have start date equal ( 1 Month Ago )

    I need to perform this because I rec. a patches of Incremental update on daily bases , But I execute it One per week


    khaled Mohamed

    martedì 17 aprile 2018 12:44

Tutte le risposte

  • you can use MERGE statement for this

    see

    https://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-slowly-changing-dimensions/

    In your case only difference would be to set EndDate as your date value which is stored in a variable and Startdate as variable + 1 day

    i.e like

    declare @dt date = 'your date value'
    
    
    insert into dbo.tblDimSCDType2Example
    ( 
      Key,
      ...
      EndDate
    )
    select    
    KeyColumn,
    .. other columns
    EffectiveDate,
    EndDate
    from
    (
      
      MERGE into YourTargetTable AS target
    
      USING 
      (
        
        SELECT 
        Key,
        ...,@dt
        from dbo.Sourcetable
      ) AS source 
      ( 
        Key,
        ...,EndDate
      ) ON --We are matching on the SourceSystemID in the target table and the source table.
      (
        target.Key= source.Key
      )
     
      WHEN MATCHED and conditions...
                                    
      THEN 
      UPDATE SET 
        EndDate=s.EndDate, 
        ...
    
      WHEN NOT MATCHED THEN  
      INSERT 
      (
        Key, 
        ...
      )
      VALUES 
      (
        source.Key, 
        ..,
        DATEADD(dd,1,source.EndDate)
      )
      OUTPUT $action, 
        source.Key, 
        source.Attribute1,
        ...,source.EndDate
    ) -- the end of the merge statement
    --The changes output below are the records that have changed and will need
    --to be inserted into the slowly changing dimension.
    as changes 
    (
      action, 
      Key, 
      Attribute1,
      ...,EndDate
    )
    where action='UPDATE';


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    martedì 17 aprile 2018 13:17
  • Hi,

    I do like to suggest that Start Date should be earlier than End Date. Try:

    Create Table #TempTable(
    StartDate datetime,
    EndDate datetime
    )
    insert into #temptable values(dateadd(d,-1,dateadd(m,-1,getdate())),dateadd(m,-1,getdate()))
    
    select * from #temptable
    


    Many Thanks & Best Regards, Hua Min

    martedì 17 aprile 2018 13:46
  • Hi,

    I do like to suggest that Start Date should be earlier than End Date. Try:

    Create Table #TempTable(
    StartDate datetime,
    EndDate datetime
    )
    insert into #temptable values(dateadd(d,-1,dateadd(m,-1,getdate())),dateadd(m,-1,getdate()))
    
    select * from #temptable


    Many Thanks & Best Regards, Hua Min

    Nope

    This is SCD2 where new entry will have Startdate as next day compared to the EndDate of the previous entry


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    martedì 17 aprile 2018 13:51