SQLTeam.com | Weblogs | Forums

How can I update the old records with current record


#1

How can i update the batchid for the Previous records.

Example First have two records with LEPCUR and LEPCFS,in that scenario I have one record,from both two records.

After that i have given update for LEPCUR and i need to get the Updated LEPCUR and Old LEPCFS as a single record.
I need to update the LEPCFS BatchId also.
I.e I need to update the LEPFS batchid to LEPCURS(value(6) in Fact Table) BatchId.Based on History ids.
I have this qury for the Updation of batchIds.
IF OBJECT_ID('tempdb..#AggStatisticFactHistoryIDs') IS NOT NULL
DROP TABLE #AggStatisticFactHistoryIDs

SELECT MAX(ag.HistoryId) HistoryId INTO #AggStatisticFactHistoryIDs FROM SIF.ObjaggregateStatisticFact_History Agf
INNER JOIN SIF.ObjaggregateStatistic_History ag ON agf.statisticRefId=Ag.RefId AND agf.CollectionId=Ag.CollectionId
WHERE Agf.BatchId=@BatchId 
GROUP BY ag.RefId

UPDATE ag SET BatchId=@BatchId
FROM SIF.ObjaggregateStatistic_History ag JOIN #AggStatisticFactHistoryIDs sh ON sh.HistoryId=ag.HistoryId

Could any one please help me.
Please refer the screen shot.