Change Data Capture to get a paricular set of change within a time span

The "fn_cdc_map_time_to_lsn" is especially useful

 

DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10)


SET @begin_time = '11/10/2010 10:00 AM'


SET @end_time = '11/10/2010 8:00 PM'


SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);


SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);



SELECT


    _OU.unit_natural_id AS Order_Id


    , CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(80), _OR.return_body.query('


     declare default element namespace ''urn:com:maritz:research:caf:returns:BIE:1:0'';


        /Return/ReturnHeader/ReturnDate/text()


    ')), 10), 126) AS ReturnReceivedDate


FROM ODS.Unit AS _OU


    LEFT OUTER JOIN ODS.[Return] AS _OR


        ON _OU.unit_key = _OR.unit_key


WHERE


    _OU.unit_guid IN (SELECT population_guid FROM cdc.fn_cdc_get_all_changes_Clean_Collection(@from_lsn, @to_lsn, 'all'))


ORDER BY


    CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(80), _OR.return_body.query('


     declare default element namespace ''urn:com:maritz:research:caf:returns:BIE:1:0'';


        /Return/ReturnHeader/ReturnDate/text()


    ')), 10), 126)


GO

Print | posted on Wednesday, November 10, 2010 5:38 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski