Selecting time intervals of value live - missing first and last intervals

I've got a table with following structure

| ChangedDate | IDParameter | ChangedTo(bit column) |

So I need to get time intervals when my parameter is True or False, like following

| IDParameter | ChangedToDate1 | ChangedToDate2 | ChangedTo(true to false || false to true)

and I do

With RankedDates As
(
    Select T1.[ChangedDate], T1.ID, T1.[ChangedToValue]
    , Row_Number() Over( Partition By T1.ID Order By T1.[ChangedDate] ) As Num
    From [Changes] As T1
 )

  SELECT T1.[ID]
      ,T2.[ChangedToValue]
      ,T1.[ChangedDate] AS startDate
      ,T2.[ChangedDate] AS endDate
  FROM [RankedDates] AS T1
    Join RankedDates开发者_Go百科 As T2
                    On T2.ID = T1.ID
                          And T2.Num = T1.Num + 1
                          And T2.[ChangedToValue] <> T1.[ChangedToValue]
                    Order By T2.[ChangedDate]

The trouble is that I am missing first and last intervals here. it must be NULL for start date if that is first and NULL for endDate for last interval for each Parameter ID. I guess I need add it with UNION but my trouble I can't understand how to add it for each IDParameter.

I don't know when value were changed first time and I don't know if the value will be changed in any time so I need NULL or some mindate for first intervals and NULL or some maxdate for last intervals.

ms sql server 2008

sorry for such complex question.

Example :

08.03.2011 ID1 0 -> 1 
09.03.2011 ID1 1 -> 0
09.03.2011 ID2 0 -> 1 
10.03.2011 ID1 0 -> 1 
10.03.2011 ID2 1 -> 0

--->

NULL , 08.03.2011 ID1 is 0
NULL , 09.03.2011 ID2 is 0
08.03.2011, 09.03.2011 ID1 is 1
09.03.2011, 10.03.2011 ID2 is 1
09.03.2011, 10.03.2011 ID1 is 0
10.03.2011, NULL ID1 is 1
10.03.2011, NULL ID2 is 0

---

how about using FULL JOIN instead of JOIN?

Does it solve your problem?

EDIT:

I think this should work as you want.

select  isnull(T1.ID, T2.ID) as ID
        ,isnull(T2.[ChangedToValue], case when T1.[ChangedToValue] = 1 then 0 else 1 end) as [ChangedToValue]
        ,T1.[ChangedDate] as startdate
        ,T2.[ChangedDate] as enddate
from    [RankedDates] T1
full join [RankedDates] T2
    on T2.num = T1.num +1
    and T2.ID = T1.ID
    and T1.[ChangedToValue] <> T2.[ChangedToValue]

order by 
    case when T2.[ChangedDate] is null then 1 else 0 end
    ,T2.[ChangedDate]

You where right about the ChangedToValue, I modified it to show the opposite now, if T2 is null.

---

Assuming thats how your base table looks:
ChangeDate IDParameter ChangedTo
2011-03-08 ID1 True
2011-03-09 ID1 False
2011-03-09 ID2 True
2011-03-10 ID1 True
2011-03-10 ID2 False

SELECT  (SELECT TOP 1 t0.[ChangeDate] FROM [calendardb].[dbo].[Table_1] t0 
WHERE t0.IDParameter = t1.IDParameter AND t0.ChangeDate < t1.ChangeDate ORDER 
BY t0.ChangeDate DESC),
   [ChangeDate]
  ,[IDParameter]
  ,[ChangedTo]
FROM [calendardb].[dbo].[Table_1] t1
UNION 
SELECT MAX(ChangeDate) as maxd ,NULL,[IDParameter],
(SELECT ChangedTo FROM [calendardb].[dbo].[Table_1] t0 WHERE t0.ChangeDate =    (SELECT MAX(ChangeDate) FROM [calendardb].[dbo].[Table_1]
GROUP BY [IDParameter] HAVING IDParameter = t1.IDParameter) AND t1.IDParameter = t0.IDParameter)

FROM [calendardb].[dbo].[Table_1] t1
GROUP BY [IDParameter]

will give you result like this:

NULL 2011-03-08 ID1 1
2011-03-08 2011-03-09 ID1 0
NULL 2011-03-09 ID2 1
2011-03-09 2011-03-10 ID1 1
2011-03-09 2011-03-10 ID2 0
2011-03-10 NULL ID1 1
2011-03-10 NULL ID2 0