none
Capturing history of data.

    Question

  • Hello All,

    I have a question about capturing history of table,Please feel free ask questions if you dont understand the case.i wrote a query,it wont be efficent,cause tables have large amount of data.Please think about query cost when you write script.so the goal is finding history of id_emp and key_transaction relation.employee has set of key_transacttions and i want to capture this history.Please see the scripts.i have an idea but query cost is high.i would like to know more efficient queries.

    declare @Sales table ( id_emp int, key_transaction int)
    declare @Sales_history table ( id_emp int, key_transaction int,version_key int)
    
    
    insert into @Sales ( id_emp,key_transaction ) --- first insert 
    select 1,1 
    union all
    select 1,2
    union all 
    select 1,3 
    union all
    select 2,1 
    union all
    select 1,4
    union all 
    select 2,5 
    
    
    --- desired output of sales history 
    
    insert into @Sales_history ( id_emp,key_transaction,version_key) --- first insert 
    select 1,1,1 
    union all
    select 1,2,1
    union all 
    select 1,3,1 
    union all
    select 2,1,1 
    union all
    select 1,4,1
    union all 
    select 2,5,1
    
    select * from @Sales_history --- desired output of sales history 
    
    
    insert into @Sales ( id_emp,key_transaction ) --- second insert ( adding new transaction to id_emp = 1 so history of id_emp = 1 is changed so new version should be push) 
    select 1,7 
    
    insert into @Sales_history ( id_emp,key_transaction,version_key) --- first insert 
    select 1,1,2 
    union all
    select 1,2,2
    union all 
    select 1,3,2 
    union all
    select 1,7,2 
    
    select * from @Sales_history --- desired output of sales history 
    
    
    delete from @Sales where key_transaction = 3  and id_emp = 1 --- deleting records (data deleted from sales table for id_emp = 1 so history is changed so new version should be push) 
    
    
    
    insert into @Sales_history ( id_emp,key_transaction,version_key) --- first insert 
    select 1,1,3 
    union all
    select 1,2,3
    union all
    select 1,7,3 
    
    
    select * from @Sales_history 
    
    
    

    Monday, October 28, 2013 6:35 PM

Answers

  • It is my solution.but it does not perform well.

    ;with salesCTE as (
    select *,ROW_NUMBER() over ( partition by id_emp order by key_transaction ) rn 
      from Sales  
    )
    
    , Sales_HistoryCTE as (
    select *,ROW_NUMBER() over ( partition by id_emp order by key_transaction ) rn 
      from Sales_History sh1
     where sh1.version_key = ( select max(sh2.version_key) from Sales_History sh2 where sh1.id_emp = sh2.id_emp ) 
    )
    
    
    insert into Sales_History ( id_emp,key_transaction,version_key)
    
    
    select id_emp,key_transaction,(select isnull(max(version_key) + 1,1) from Sales_History sh where s.id_emp = sh.id_emp ) 
      from Sales s
     where id_emp in ( 
    				select s.id_emp
    				  from salesCTE s
    				 where not exists ( select null from Sales_HistoryCTE sh where s.id_emp = sh.id_emp and s.rn = sh.rn and s.key_transaction = sh.key_transaction  ) ) 
    
    
    
    union 
    
    select id_emp,key_transaction,(select isnull(max(version_key) + 1,1) from Sales_History sh where s.id_emp = sh.id_emp ) 
      from Sales s
     where id_emp in ( 
    				select s.id_emp
    				  from Sales_HistoryCTE sh
    				 where not exists ( select null from SalesCTE s where s.id_emp = sh.id_emp and s.rn = sh.rn and s.key_transaction = sh.key_transaction  ) ) 
    
    
    
    



    • Edited by altuko Wednesday, October 30, 2013 11:40 PM
    • Marked as answer by altuko Thursday, October 31, 2013 7:56 AM
    • Unmarked as answer by altuko Thursday, October 31, 2013 7:57 AM
    • Marked as answer by altuko Thursday, October 31, 2013 11:19 PM
    Wednesday, October 30, 2013 10:27 PM

All replies

  • So my question is: What is your problem?
    Monday, October 28, 2013 6:55 PM
  • If you see the scripts,i want to insert history of sales table to Sales_history.i put the desired output queries.

    Monday, October 28, 2013 7:02 PM
  • Hello Stefan,

    If you dont understand my question,i will try to explain more. basicly, the goal is quite simple that will have history of sales table which i already mentioned sales_history as this name.as you see employees have sales transactions,what i want to know the next run of sales processing if anyone of employees   have different sales_transactions.so basicly,let's say employee a has 1,2 transactions,and after next run of sales processing now let's say  employee a has 1,2,3 transactions.So it means something changes,now he has more sales.i want to add his history to sales history table like below.

    emp_id,key_transaction,version

    1,1,1

    1,2,1

    1,1,2

    1,2,2

    1,3,2

    And also if some transactions can be removed,and i want to push the history of that case.i hope it is more clean now.

    Monday, October 28, 2013 7:32 PM
  • any idea ?
    Monday, October 28, 2013 8:24 PM
  • I hope,someone can help me to write efficient query for this case.
    Monday, October 28, 2013 11:33 PM
  • Hello altuko,

    To me, your flow is little lengthy and you may face some performance issue for each updation, you need all other data to be inserted into your history table. It would be good, if you insert only the affected row to your history table and when you need for the presentation, you tweak to get only those required.

    You may try the below:

    declare @Sales table ( id_emp int, key_transaction int)
    declare @Sales_history table ( seq int identity(1,1), id_emp int, key_transaction int, Action char(1))
    
    
    insert into @Sales ( id_emp,key_transaction ) 
    OUTPUT inserted.id_emp,inserted.key_transaction,'I'
    INTO @Sales_history(id_emp,key_transaction,Action)
    Select * 
    From 
    (
    select 1 id_emp,1 key_transaction
    union all
    select 1,2
    union all 
    select 1,3 
    union all
    select 2,1 
    union all
    select 1,4
    union all 
    select 2,5 ) A
    
    Select * From @Sales
    Select * From @Sales_history
    Where seq in (
    Select MAX(Seq) From @Sales_history
    Group by id_emp,key_transaction
    having Min(Action) <> 'D') --Assume, only D - Delete, I - Insert, U - Update
    
    delete A OUTPUT DELETED.id_emp,
           Deleted.key_transaction,'D'
        INTO @Sales_history
        from @Sales A where key_transaction = 3  and id_emp = 1 
    
    Select * From @Sales
    
    Select * From @Sales_history
    Where seq in (
    Select MAX(Seq) From @Sales_history
    Group by id_emp,key_transaction
    having Min(Action) <> 'D') --Assume, only D - Delete, I - Insert, U - Update


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Durval Ramos Tuesday, October 29, 2013 10:16 AM
    • Unproposed as answer by altuko Tuesday, October 29, 2013 8:53 PM
    Tuesday, October 29, 2013 5:33 AM
  • E.g.
    USE tempdb;
    
    CREATE TABLE Sales
        (
          id_emp INT ,
          key_transaction INT ,
          payload INT
        );
    
    CREATE TABLE Sales_history
        (
          id_emp INT ,
          key_transaction INT ,
          payload INT ,
          version_key TIMESTAMP ,
          history_datetime DATETIME DEFAULT ( GETDATE() ) ,
          history_type CHAR(1)
        );
    GO
    
    CREATE TRIGGER tr_Sales_I ON Sales
        AFTER INSERT
    AS
        INSERT  INTO Sales_History
                ( id_emp ,
                  key_transaction ,
                  payload ,
                  history_type
                )
                SELECT  id_emp ,
                        key_transaction ,
                        payload ,
                        'I'
                FROM    INSERTED;
    GO
    
    CREATE TRIGGER tr_Sales_U ON Sales
        AFTER UPDATE
    AS
        INSERT  INTO Sales_History
                ( id_emp ,
                  key_transaction ,
                  payload ,
                  history_type
                )
                SELECT  id_emp ,
                        key_transaction ,
                        payload ,
                        'U'
                FROM    INSERTED;
    GO
    
    CREATE TRIGGER tr_Sales_D ON Sales
        AFTER DELETE
    AS
        INSERT  INTO Sales_History
                ( id_emp ,
                  key_transaction ,
                  payload ,
                  history_type
                )
                SELECT  id_emp ,
                        key_transaction ,
                        payload ,
                        'D'
                FROM    DELETED;
    GO
    
    
    --- first insert 
    INSERT  INTO Sales
            ( id_emp, key_transaction, payload )
    VALUES  ( 1, 1, 10 ),
            ( 1, 2, 20 ),
            ( 1, 3, 30 ),
            ( 2, 1, 10 ),
            ( 1, 4, 40 ),
            ( 2, 5, 50 );
    
    SELECT  *
    FROM    Sales;
    SELECT  *
    FROM    Sales_History
    ORDER BY version_key;
    
    WAITFOR DELAY '00:00:05';
    
    --- second insert ( adding new transaction to id_emp = 1 so history of id_emp = 1 is changed so new version should be push) 
    INSERT  INTO Sales
            ( id_emp, key_transaction, payload )
    VALUES  ( 1, 7, 70 );
    
    SELECT  *
    FROM    Sales;
    SELECT  *
    FROM    Sales_History
    ORDER BY version_key;
    
    WAITFOR DELAY '00:00:05';
    
    --- updating
    UPDATE  Sales
    SET     payload += 100
    WHERE   key_transaction = 1;
    
    SELECT  *
    FROM    Sales;
    SELECT  *
    FROM    Sales_History
    ORDER BY version_key;
    
    WAITFOR DELAY '00:00:05';
    
    --- deleting records (data deleted from sales table for id_emp = 1 so history is changed so new version should be push) 
    DELETE  FROM Sales
    WHERE   key_transaction = 3
            AND id_emp = 1;
    
    SELECT  *
    FROM    Sales;
    SELECT  *
    FROM    Sales_history;
    

    Tuesday, October 29, 2013 9:26 AM
  • Hello Latheesh and Stefan,

    Thank you for your answers but what i want to do is a little different.I dont know whether i could explain or not.i put more explanation to the my code.

    declare @Sales table ( id_emp int, key_transaction int)
    declare @Sales_history table ( id_emp int, key_transaction int,version_key int)
    
    
    insert into @Sales ( id_emp,key_transaction ) --- let's say some transactions come and i do insert.i just put manual insert statement.no need query for below..
    											  --- so there are already some queries for this.
    select 1,1 
    union all
    select 1,2
    union all 
    select 1,3 
    union all
    select 2,1 
    union all
    select 1,4
    union all 
    select 2,5 
    
    
    
    
    insert into @Sales_history ( id_emp,key_transaction,version_key)  
    --- so need to insert script to @sales_history table and desired should be like below.i inserted it manually.but there should be query here.
    
    select 1,1,1 
    union all
    select 1,2,1
    union all 
    select 1,3,1 
    union all
    select 2,1,1 
    union all
    select 1,4,1
    union all 
    select 2,5,1
    
    select * from @Sales_history --- desired output of sales history 
    
    
    insert into @Sales ( id_emp,key_transaction ) --- second insert ( adding new transaction to id_emp = 1 so history of id_emp = 1 is changed so new version should be push) 
    select 1,7 
    
    insert into @Sales_history ( id_emp,key_transaction,version_key) -- so as you see above new transactions come to sales table,need to create new history for id_emp = 1 which is called version 2
    select 1,1,2 
    union all
    select 1,2,2
    union all 
    select 1,3,2 
    union all
    select 1,7,2 
    
    select * from @Sales_history --- desired output of sales history 
    
    
    delete from @Sales where key_transaction = 3  and id_emp = 1 --- deleting records (data deleted from sales table for id_emp = 1 so history is changed so new version should be push) 
    
    
    
    insert into @Sales_history ( id_emp,key_transaction,version_key) --- and here deleted something above,so need to new history
    select 1,1,3 
    union all
    select 1,2,3
    union all
    select 1,7,3 
    
    
    select * from @Sales_history---so the final sales history table should be like that.as you see in query result there are there version of id_emp = 1,
    
    --- So it is not about keeping update,delete,or inserting history,it should keep history of employee's transaction history.
    

    Tuesday, October 29, 2013 8:49 PM
  • Hi altuko,

    You can try the following codes:

    USE tempdb;
    go
    
    
    create table Sales ( id_emp int, key_transaction int)
    create table Sales_history ( id_emp int, key_transaction int,version_key int)
    
    
    insert into Sales ( id_emp,key_transaction )
    
    select 1,1 
    union all
    select 1,2
    union all 
    select 1,3 
    union all
    select 2,1 
    union all
    select 1,4
    union all 
    select 2,5 
    
    insert into Sales_history ( id_emp,key_transaction,version_key) 
    
    select 1,1,1 
    union all
    select 1,2,1
    union all 
    select 1,3,1 
    union all
    select 2,1,1 
    union all
    select 1,4,1
    union all 
    select 2,5,1
    
    select * from Sales_history 
    
    create trigger Update_History
    on Sales
    after insert, update, delete
    as
    begin	
    	
    		declare @InsertRows int;
    	declare @DeleteRows int;
    	select @InsertRows=count(*) from inserted
    	select @DeleteRows=count(*) from deleted
     
    	if @InsertRows>0 
    	begin
    		if @DeleteRows<=0
    		begin
    			declare @id_emp int;
    			declare @key_transaction int;
    			declare @version_key int;
    			select @id_emp=id_emp,@key_transaction=key_transaction from inserted
    			update Sales_history
    			set version_key = version_key+1
    			where id_emp = @id_emp
    
    			select @version_key = version_key from Sales_history
    			where id_emp = @id_emp
    
    			insert into Sales_history(id_emp,key_transaction,version_key) values(@id_emp,@key_transaction,@version_key);
    		end
    	end
    	else
    	begin
    		declare @id_emp2 int;
    		declare @key_transaction2 int;
    		select @id_emp2=id_emp,@key_transaction2=key_transaction from deleted
    
    		delete from Sales_history
    		where id_emp = @id_emp2
    		and key_transaction = @key_transaction2
    
    		update Sales_history
    		set version_key = version_key+1
    		where id_emp = @id_emp2
    	end
    end
    
    
    insert into Sales ( id_emp,key_transaction ) 
    select 1,7 
    
    select * from Sales_history
    
    delete from Sales where key_transaction = 3  and id_emp = 1 
    
    select * from Sales_history
    
    drop table Sales
    drop table Sales_history
    
    Best Regards,
    Allen Li

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, October 30, 2013 6:46 AM
  • Hello Allen Li,

    Thank you for your answer,but trigger is not good idea,cause there is truncate load for Sales table,so Daily insert to sales table will be around 2 million.i dont know how is the performance if i put trigger to Sales table.

    Wednesday, October 30, 2013 10:05 AM
  • let me explain process again.

    ----

    1-truncate table Sales

    2-load sales table with new data

    3-Create History (mentioned rules of history createion already in last entries)

    Wednesday, October 30, 2013 11:27 AM
  • So Allen,

    According to this structure,trigger can not be solution at all.i need write script for step 3.Any more ideas ?

    Wednesday, October 30, 2013 11:28 AM
  • Don't truncate the table. Merge it instead.

    Wednesday, October 30, 2013 12:31 PM
  • Yes you are right.I can merge it also,but event if i do merge Allen's trigger does not work properly.it updates version key only.but i want to keep old version of data (employee trasanctions tupple). 

    Wednesday, October 30, 2013 12:39 PM
  • I don't understand it. Another possible solution:

    USE tempdb;
    
    CREATE TABLE SalesBatch
        (
          BatchID INT IDENTITY(1, 1)
                      NOT NULL
                      PRIMARY KEY ,
          SnapshotDate DATETIME NOT NULL
                                DEFAULT ( GETDATE() )
        );
    
    CREATE TABLE Sales
        (
          id_emp INT NOT NULL ,
          key_transaction INT NOT NULL ,
          CONSTRAINT PK_Sales PRIMARY KEY ( id_emp, key_transaction )
        );
    
    CREATE TABLE SalesHistory
        (
          BatchID INT NOT NULL ,
          id_emp INT NOT NULL ,
          key_transaction INT NOT NULL ,
          CONSTRAINT PK_SalesHistory PRIMARY KEY ( BatchID, id_emp, key_transaction )
        );
    GO
    
    DECLARE @BatchID TABLE ( BatchID INT );
    DECLARE @Batch TABLE
        (
          id_emp INT NOT NULL ,
          key_transaction INT NOT NULL
        );
    
    -- Batch 1
    DELETE  FROM @Batch;
    DELETE  FROM @BatchID;
    INSERT  INTO @Batch
    VALUES  ( 1, 1 ),
            ( 2, 1 );
    
    INSERT  INTO SalesBatch
    OUTPUT  INSERTED.BatchID
            INTO @BatchID
    VALUES  ( DEFAULT );
    
    INSERT  INTO SalesHistory
            SELECT  BID.BatchID ,
                    B.*
            FROM    @BatchID BID
                    CROSS JOIN @Batch B;
    
    TRUNCATE TABLE Sales;
    INSERT  INTO Sales
            SELECT  *
            FROM    @Batch;
    
    -- Batch 2
    DELETE  FROM @Batch;
    DELETE  FROM @BatchID;
    INSERT  INTO @Batch
    VALUES  ( 1, 1 ),
            ( 2, 1 ),
            ( 1, 2 ),
            ( 2, 2 );
    
    INSERT  INTO SalesBatch
    OUTPUT  INSERTED.BatchID
            INTO @BatchID
    VALUES  ( DEFAULT );
    
    INSERT  INTO SalesHistory
            SELECT  BID.BatchID ,
                    B.*
            FROM    @BatchID BID
                    CROSS JOIN @Batch B;
    
    TRUNCATE TABLE Sales;
    INSERT  INTO Sales
            SELECT  *
            FROM    @Batch;
    
    -- Batch 3
    DELETE  FROM @Batch;
    DELETE  FROM @BatchID;
    INSERT  INTO @Batch
    VALUES  ( 1, 1 ),
            ( 2, 1 ),
            ( 2, 2 );
    
    INSERT  INTO SalesBatch
    OUTPUT  INSERTED.BatchID
            INTO @BatchID
    VALUES  ( DEFAULT );
    
    INSERT  INTO SalesHistory
            SELECT  BID.BatchID ,
                    B.*
            FROM    @BatchID BID
                    CROSS JOIN @Batch B;
    
    TRUNCATE TABLE Sales;
    INSERT  INTO Sales
            SELECT  *
            FROM    @Batch;
    
    -- Results
    SELECT  *
    FROM    Sales;
    SELECT  *
    FROM    SalesBatch;
    SELECT  *
    FROM    SalesHistory;
    GO
    
    DROP TABLE Sales;
    DROP TABLE SalesBatch;
    DROP TABLE SalesHistory;
    GO

    Wednesday, October 30, 2013 1:16 PM
  • Hello Stefan,I think you did understand question,but you did snaphot of sales table in every process. even if nothing changes.so history table will be bigger.i just need to snapshot of changed employee transactions.
    Wednesday, October 30, 2013 1:38 PM
  • so i mean,

    Batch 1:

    id_emp, key_transaction

    1,1

    1,2

    Batch 2 :

    id_emp, key_transaction

    1,1

    1,2

     

    Batch 2 :

     

    id_emp, key_transaction

    1,1

    1,2

    1,3

    so history table will be

    id_emp, key_transaction,batchid

    1,1,1

    1,2,1

    1,1,2

    1,2,2

    1,2,3

    Wednesday, October 30, 2013 1:41 PM
  • sorry should be like that

    Batch 1:

    id_emp, key_transaction

    1,1

    1,2

    Batch 2 :

    id_emp, key_transaction

    1,1

    1,2

     

    Batch 3 :

     

    id_emp, key_transaction

    1,1

    1,2

    1,3

    so history table will be

    id_emp, key_transaction,batchid

    1,1,1

    1,2,1

    1,1,3

    1,2,3

    1,3,3


    • Edited by altuko Wednesday, October 30, 2013 2:29 PM
    Wednesday, October 30, 2013 2:26 PM
  • Yup, I agree. But I don't really understand the context, thus your goal. E.g.

    USE tempdb;
    
    CREATE TABLE Sales
        (
          id_emp INT NOT NULL ,
          key_transaction INT NOT NULL ,
          payload INT NOT NULL ,
          CONSTRAINT PK_Sales PRIMARY KEY ( id_emp, key_transaction )
        );
    GO
    
    DELETE  FROM sales;
    
    DECLARE @BatchID INT;
    DECLARE @Batch TABLE
        (
          id_emp INT NOT NULL ,
          key_transaction INT NOT NULL ,
          payload INT NOT NULL
        );
    DECLARE @BatchChanges TABLE
        (
          batch_id INT ,
          [action] VARCHAR(255) ,
          id_emp INT ,
          key_transaction INT ,
          old_payload INT ,
          new_payload INT ,
          has_changed BIT
        );
    
    
    -- Batch 1
    SET @BatchID = 1;
    DELETE  FROM @Batch;
    INSERT  INTO @Batch
    VALUES  ( 1, 1, 1 ),
            ( 2, 2, 2 );
    
    MERGE Sales AS Dst
        USING 
            ( SELECT    id_emp ,
                        key_transaction ,
                        payload
              FROM      @Batch
            ) AS Src
        ON ( Dst.id_emp = Src.id_emp
             AND Dst.key_transaction = Src.key_transaction
           )
        WHEN MATCHED 
            THEN 
            UPDATE
              SET   payload = Src.payload
        WHEN NOT MATCHED 
            THEN	
    	    INSERT  (
                      id_emp ,
                      key_transaction ,
                      payload 
                    )
              VALUES
                    ( Src.id_emp ,
                      Src.key_transaction ,
                      Src.payload
                    )
        OUTPUT
            @BatchID ,
            $action ,
            INSERTED.id_emp ,
            INSERTED.key_transaction ,
            DELETED.payload ,
            INSERTED.payload ,
            CASE WHEN COALESCE(DELETED.payload, INSERTED.payload) != INSERTED.payload
                      OR $action = 'INSERT' THEN 1
                 ELSE 0
            END AS payload_changed
            INTO @BatchChanges;
    
    -- Batch 2
    SET @BatchID = 2;
    DELETE  FROM @Batch;
    INSERT  INTO @Batch
    VALUES  ( 1, 1, 1 ),
            ( 1, 2, 2 ),
            ( 2, 1, 1 ),
            ( 2, 2, 3 );
    
    MERGE Sales AS Dst
        USING 
            ( SELECT    id_emp ,
                        key_transaction ,
                        payload
              FROM      @Batch
            ) AS Src
        ON ( Dst.id_emp = Src.id_emp
             AND Dst.key_transaction = Src.key_transaction
           )
        WHEN MATCHED 
            THEN 
            UPDATE
              SET   payload = Src.payload
        WHEN NOT MATCHED 
            THEN	
    	    INSERT  (
                      id_emp ,
                      key_transaction ,
                      payload 
                    )
              VALUES
                    ( Src.id_emp ,
                      Src.key_transaction ,
                      Src.payload
                    )
        OUTPUT
            @BatchID ,
            $action ,
            INSERTED.id_emp ,
            INSERTED.key_transaction ,
            DELETED.payload ,
            INSERTED.payload ,
            CASE WHEN COALESCE(DELETED.payload, INSERTED.payload) != INSERTED.payload
                      OR $action = 'INSERT' THEN 1
                 ELSE 0
            END AS payload_changed
            INTO @BatchChanges;
    
    -- Batch 3
    SET @BatchID = 3;
    DELETE  FROM @Batch;
    INSERT  INTO @Batch
    VALUES  ( 1, 1, 1 ),
            ( 2, 2, 2 );
    
    MERGE Sales AS Dst
        USING 
            ( SELECT    id_emp ,
                        key_transaction ,
                        payload
              FROM      @Batch
            ) AS Src
        ON ( Dst.id_emp = Src.id_emp
             AND Dst.key_transaction = Src.key_transaction
           )
        WHEN MATCHED 
            THEN 
            UPDATE
              SET   payload = Src.payload
        WHEN NOT MATCHED 
            THEN	
    	    INSERT  (
                      id_emp ,
                      key_transaction ,
                      payload 
                    )
              VALUES
                    ( Src.id_emp ,
                      Src.key_transaction ,
                      Src.payload
                    )
        OUTPUT
            @BatchID ,
            $action ,
            INSERTED.id_emp ,
            INSERTED.key_transaction ,
            DELETED.payload ,
            INSERTED.payload ,
            CASE WHEN COALESCE(DELETED.payload, INSERTED.payload) != INSERTED.payload
                      OR $action = 'INSERT' THEN 1
                 ELSE 0
            END AS payload_changed
            INTO @BatchChanges;
    
    -- Results (stuff it in your history)
    SELECT  *
    FROM    @BatchChanges
    WHERE   has_changed = 1;
    GO
    
    DROP TABLE Sales;
    GO

    Wednesday, October 30, 2013 3:15 PM
  • I am very sorry Stefan,but i dont care about change of payload,But i think i got why you did not understand the problem.

    The important point of this problem is key_transaction,forget about amount or quantity.key_transaction is not identity column for sales table,it is coming from source.so,my goal is capturing history of employee and key_transaction set.and you alread know i always truncate-load to sales table.anyway i hope i can explain it properly this time.

    think i have procedure,and i can run this procedure anytime.and this procedure is used for sales mapping which is not relatad to our problem.but let me explain history.i have already sales table,and run procedure,truncate it,and get data from source and do some manipulation on data,and load again.so think at first employee a has transactions (1,2,3) ok.after run procedure,in sales table i see employee a transactions just for 1,2.so it means i need to create history.

    sales table before run procedure for employee a

    emp,transid

    a,1

    a,2

    a,3

    sales table after run procedure for employee a

    a,1

    a,2

     

    so as you see set of employee a's transactions changed,right ? it means i should have two version in history table

    history table should be like below

    emp,transid,version

    a,1,1

    a,2,1

    a,3,1

    a,1,2

    a,2,2

     

    but i just need to create version just for adding new trans or deleting existing trans from employee a.

     


    • Edited by altuko Wednesday, October 30, 2013 8:49 PM
    Wednesday, October 30, 2013 5:09 PM
  • any more ideas ?

    Wednesday, October 30, 2013 6:38 PM
  • It is my solution.but it does not perform well.

    ;with salesCTE as (
    select *,ROW_NUMBER() over ( partition by id_emp order by key_transaction ) rn 
      from Sales  
    )
    
    , Sales_HistoryCTE as (
    select *,ROW_NUMBER() over ( partition by id_emp order by key_transaction ) rn 
      from Sales_History sh1
     where sh1.version_key = ( select max(sh2.version_key) from Sales_History sh2 where sh1.id_emp = sh2.id_emp ) 
    )
    
    
    insert into Sales_History ( id_emp,key_transaction,version_key)
    
    
    select id_emp,key_transaction,(select isnull(max(version_key) + 1,1) from Sales_History sh where s.id_emp = sh.id_emp ) 
      from Sales s
     where id_emp in ( 
    				select s.id_emp
    				  from salesCTE s
    				 where not exists ( select null from Sales_HistoryCTE sh where s.id_emp = sh.id_emp and s.rn = sh.rn and s.key_transaction = sh.key_transaction  ) ) 
    
    
    
    union 
    
    select id_emp,key_transaction,(select isnull(max(version_key) + 1,1) from Sales_History sh where s.id_emp = sh.id_emp ) 
      from Sales s
     where id_emp in ( 
    				select s.id_emp
    				  from Sales_HistoryCTE sh
    				 where not exists ( select null from SalesCTE s where s.id_emp = sh.id_emp and s.rn = sh.rn and s.key_transaction = sh.key_transaction  ) ) 
    
    
    
    



    • Edited by altuko Wednesday, October 30, 2013 11:40 PM
    • Marked as answer by altuko Thursday, October 31, 2013 7:56 AM
    • Unmarked as answer by altuko Thursday, October 31, 2013 7:57 AM
    • Marked as answer by altuko Thursday, October 31, 2013 11:19 PM
    Wednesday, October 30, 2013 10:27 PM