Its the itemNumber and itemValue. For every change in ItemValue for an ItemNumber, a new record should be inserted. But in the current table,even when there was no change in the ItemValue, there is a new record. Hope this helps.

Also, while this solves your problem from the logical sense, it does not solve it from the logistical sense. How big is this history table? If you are working on systems with millions or billions of rows, you cannot simply run this query against it. You will likely need to batch this as well as a strategy to handle changes while the batch process is running. If your table is small and you can handle a brief outage, you might be able to get away with locking the table and doing a MERGE.

Lazerath...seems like Bandi solution solves the problem(Not sure if i missing anything there). Yeah, i want to collapse the periods too but only if the ItemValue changes. BTW..even you solution works with CTE's.

For example...for ItemNumber 111, item value didn't changed so i wanted to collapse all 3 records into one. But for ItemNumber 222...itemValue changed from 2 to 3 and then from 3 to 2. So i need 3 records for ItemNumber 222.

Take a look at the code I posted. You can run the whole thing and it walks you through the differences between Bandi's solution and the end state you were looking for. The major difference is that Bandi's solution did not collapse the records, all it did was identify the last item value record before a change. This is key to the final solution, but is not the solution in and of itself.

For instance, for item 111, you indicated this should be the result:111 12/15/2010 9:26 2 12/31/2099 0:00

Notice the Start Date. Bandi's solution does not collapse the 3 records into 1, it simply returns the last record. I had to add code to properly group sequential history records that did not change. This was accomplished with this code:

WITH cte1
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ItemNumber ORDER BY StartDate) AS SEQUENCE,
CASE WHEN ItemValue = LEAD(ItemValue) OVER(PARTITION BY ItemNumber ORDER BY StartDate) THEN 0 ELSE 1 END AS ChangeFlag
FROM @StartingData
)
, cte2
AS
(
SELECT *,
MIN(CASE ChangeFlag WHEN 1 THEN SEQUENCE END) OVER(PARTITION BY ItemNumber ORDER BY SEQUENCE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) SeqGroup
FROM cte1
)
SELECT c.ItemNumber, MIN(c.StartDate) AS StartDate, c.ItemValue, MAX(c.EndDate) AS EndDate
FROM cte2 AS c
GROUP BY c.ItemNumber, c.ItemValue, c.SeqGroup
ORDER BY c.ItemNumber, MIN(c.StartDate)

cte1 gets the change boundaries as Bandi provided but adds in a Sequence. cte2 returns the MIN sequence with a change that is greater than or equal to the current record. This allows us to group sequential entries that do not change and produce the result you requested.