Geek asks and answers

Real-time selection of time intervals - absence of the first and last interval

I have a table with the following structure

| ChangedDate | IDParameter | ChangedTo (column bit) |

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

| IDParameter | ChangedToDate1 | ChangedToDate2 | ChangedTo (true for 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 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 miss the first and last interval here. it must be NULL for the start date, if it is the first, and NULL for endDate for the last interval for each parameter identifier. I suppose I need to add it with UNION, but my problem cannot figure out how to add it for each IDParameter.

, , , , NULL NULL maxdate .

ms sql server 2008

.

:

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
+3
2

FULL JOIN JOIN?

?

EDIT:

, , .

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]

, ChangedToValue, , , T2 null.

+3

, :
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]

:

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

+2

More articles:


All Articles