I have the following query. I've tried to update a table (virtual in a SP) with the results, but I can't get it to work.
I've tried INSERT INTO (Fields.....) before the select last select statement, and I've also tried to copy the output into a ##TempTable (I don't want this, just experimenting) and neither process works.
How would I output the following query to a table?
if object_id('tempdb..#tmp_events','u') is not null
begin drop table #tmp_events end;
create table #tmp_events (RN int identity primary key, [date] datetime, EventClass char(1),StartDate datetime, EndDate datetime, [node] int);
insert #tmp_events ([date],EventClass,StartDate, EndDate,[node])
,case when [EVENT] = 'START EVENT' then 'S'
when [EVENT] = 'END EVENT' then 'E' else 'G' end as [EventClass]
,case when [EVENT] = 'START EVENT' THEN [DATE] ELSE NULL END AS [StartDate]
,case when [EVENT] = 'END EVENT' THEN [DATE] else NULL END AS [EndDate]
from #your_data order by [node],[date];
; with cte2 as
( select C.RN,C.[date],C.[EventClass],C.[node],C.StartDate, C.EndDate
,case when C.[Node] <> isnull(P.[Node],0) then 'Y' else 'N' end as INC_Node
,case when C.startdate is not null and P.Startdate is null then 'Y' else 'N' end as INC_Start
,case when C.enddate is null and P.enddate is not null then 'Y' else 'N' end as INC_End
from #tmp_events C
left outer join #tmp_events P on P.RN = C.RN-1 -- previous row
select RN,[DATE],[EventClass], [NODE],groupvalue --,StartDate,EndDate, INC_Node, INC_Start, INC_End --, dense_rank() over (order by groupvalue) as [Grouping]
from cte2 C
cross apply (select count(*) as groupvalue from cte2 C2 where C2.rn <= C.rn and (c2.INC_Start = 'Y' or c2.INC_Node = 'Y' OR c2.INC_End = 'Y') ) G
order by RN;