toks variantas atrodo veikia gerai:

WITH CteTable AS (
	SELECT [ELEMENT], [START], [END], [VALUE]
	FROM [Test].[dbo].[Table1] t1
	WHERE NOT EXISTS (SELECT 1 FROM [Test].[dbo].[Table1] t2
						WHERE t1.[ELEMENT] = t2.[ELEMENT] AND t1.[VALUE] = t2.[VALUE] AND 
DATEADD(DAY, 1, t1.[END]) = t2.[START])

	UNION ALL

	SELECT t3.[ELEMENT], t3.[START], c.[END], t3.[VALUE]
	FROM [Test].[dbo].[Table1] t3
    INNER JOIN CteTable c
        ON t3.[ELEMENT] = c.[ELEMENT] AND t3.[VALUE] = c.[VALUE]
			AND DATEADD(DAY, 1, t3.[END]) = c.[START]
),
CteTable2 as (
select [ELEMENT], MIN([START]) as [START], [END], MAX([VALUE]) as [VALUE]
from CteTable
group by [ELEMENT], [END]
)
select [ELEMENT], [START], MAX([END]) as [END], MAX([VALUE]) as [VALUE]
from CteTable2
group by [ELEMENT], [START]




"blah" <blah@blah.bl> wrote in message 
news:l1n318$qte$1@trimpas.omnitel.net...
> kitas klausimas ar gali buti keli intervalai su vienom ELEMENT ir VALUE 
> reiksmemis, tada sitas selectas blogas
>
>
> "blah" <blah@blah.bl> wrote in message 
> news:l1n2r6$qq1$1@trimpas.omnitel.net...
>> reiktu paoptimizuoti ir negarantuoju, kad nepalikta koks bugas, bet 
>> pataiktam pavyzdziui veikia :)
>>
>> WITH CteTable AS (
>> SELECT [ELEMENT], [START], [END], [VALUE]
>> FROM [Test].[dbo].[Table1] t1
>> WHERE NOT EXISTS (SELECT 1 FROM [Test].[dbo].[Table1] t2
>> WHERE t1.[ELEMENT] = t2.[ELEMENT] AND t1.[VALUE] = t2.[VALUE] AND 
>> DATEADD(DAY, 1, t1.[END]) = t2.[START])
>>
>> UNION ALL
>>
>> SELECT t3.[ELEMENT], t3.[START], c.[END], t3.[VALUE]
>> FROM [Test].[dbo].[Table1] t3
>>    INNER JOIN CteTable c
>>        ON t3.[ELEMENT] = c.[ELEMENT] AND t3.[VALUE] = c.[VALUE]
>> AND DATEADD(DAY, 1, t3.[END]) = c.[START]
>> )
>> SELECT [ELEMENT], MIN([START]) as [START], MAX([END]) as [END], [VALUE]
>> FROM CteTable GROUP BY [ELEMENT], [VALUE]
>>
>>
>>
>> "NicMC" <jzs@freemail.lt> wrote in message 
>> news:l1hjig$rja$1@trimpas.omnitel.net...
>>> Įdomus klausimas - kas šiuo atveju yra anchoras ir kas rekursija? Ir 
>>> kaip iš rekursijos permesti tą periodo pabaigą į anchorą (anchoras 
>>> selectinasi visada, todėl turi nesiselektinti rekursijoj)?
>>>
>>> On 2013.09.20 16:19, blah@w wrote:
>>>> googlink CTE (Common Table Expressions) ir tau pavyks
>>>>
>>>> "NicMC" <jzs@freemail.lt> wrote in message
>>>> news:l1hhis$qdh$1@trimpas.omnitel.net...
>>>>> Yra lentelė su periodais ir reikšmėmis.
>>>>>
>>>>> ELEMENT START END VALUE
>>>>> 1 2012-01-12 2012-02-03 10
>>>>> 1 2012-02-04 2012-05-01 20
>>>>> 1 2012-05-02 2012-08-01 20
>>>>> 1 2012-08-02 2012-08-15 30
>>>>> 2 2010-04-13 2012-01-07 17
>>>>>
>>>>> 2 ir 3 eilučių reikšmės yra vienodos, o periodai eina nuosekliai. Kaip
>>>>> išselektinti taip, kad periodai būtų apjungti? Su periodais aišku, bet
>>>>> jeigu jų neapibrėžtas iš anksto skaičius?
>>>>
>>>