declare @sessionid varchar(36)
declare @started table (tname nvarchar(7), a_year nvarchar(4), a_month nvarchar(9), a_sort tinyint)
declare @ended table (tname nvarchar(7), b_year nvarchar(4), b_month nvarchar(9), b_sort tinyint)
declare @onhold table (tname nvarchar(7), c_year nvarchar(4), c_month nvarchar(9), c_sort tinyint)
declare @summary table (d_year nvarchar(4), d_month nvarchar(9), d_started smallint, d_ended smallint, d_onhold smallint, d_open smallint)
declare @tempsummary table (e_year nvarchar(4), e_month nvarchar(9), e_started smallint, e_ended smallint, e_onhold smallint)
if (exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'ImIncidentTicket'))
    begin
        declare id cursor for
            select SessionID from ImIncidentTicket with (NOLOCK)
    end
else
    begin
        declare id cursor for
            select session_id from ServiceDeskIncidentManagement with (NOLOCK)
    end
begin try
begin transaction [doit]
open id
    fetch next from id into @sessionid
while @@FETCH_STATUS = 0
begin
    insert into @started (tname, a_year, a_month, a_sort)
    select 'started', DATEPART(yy, ProcessStarted), DATENAME(m, ProcessStarted), DATEPART(m, ProcessStarted)
    from ReportProcess with (NOLOCK)
    where SessionID = @sessionid
    insert into @ended (tname, b_year, b_month, b_sort)
    select 'ended', DATEPART(yy, isnull(ProcessEnded, ProcessStarted)), DATENAME(m, isnull(ProcessEnded, ProcessStarted)), DATEPART(m, isnull(ProcessEnded, ProcessStarted))
    from ReportProcess with (NOLOCK)
    where SessionID = @sessionid
    and (ProcessEnded is not null or PercentComplete = 100)
    insert into @onhold (tname, c_year, c_month, c_sort)
    select 'onhold', DATEPART(yy, rpsh.DatePosted), DATENAME(m, rpsh.DatePosted), DATEPART(m, rpsh.DatePosted)
    from ReportProcess rp with (NOLOCK)
    join ReportProcessStatusHistory rpsh with (NOLOCK) on rp.SessionID = rpsh.SessionID
    where rp.SessionID = @sessionid
    and rpsh.ModelName = 'EmbeddedComponentModel'
    and rp.Result = 'Hold'
        fetch next from id into @sessionid
end
commit transaction [doit]
end try
begin catch
        select @sessionid
        rollback transaction [doit]
end catch
close id
deallocate id

insert into @tempsummary
select oa.a_year, oa.a_month, COUNT(A.tname), COUNT(B.tname), COUNT(C.tname)
from @started A
full outer join @ended B on A.a_year = B.b_year and A.a_month = B.b_month and A.tname = B.tname
full outer join @onhold C on A.a_year = C.c_year and A.a_month = C.c_month and A.tname = C.tname
outer apply (select A.* union all select B.* union all select C.*) as oa
where oa.a_year is not null
group by oa.a_year, oa.a_month, oa.a_sort
order by oa.a_year, oa.a_sort

declare @cutoff nvarchar(17)

while (select COUNT(*) from @tempsummary) > 0
begin
        set @cutoff = (select top 1 '01 ' + e_month + ' ' + e_year from @tempsummary)
        insert into @summary
        select top 1 e_year, e_month, e_started, e_ended, e_onhold,
        ((select top 1 COUNT(*) from @started
          where CONVERT(smalldatetime, '01 ' + a_month + ' ' + a_year) <= CONVERT(smalldatetime, @cutoff))
        -
        (select top 1 COUNT(*) from @ended
         where CONVERT(smalldatetime, '01 ' + b_month + ' ' + b_year) <= CONVERT(smalldatetime, @cutoff)))
         from @tempsummary
        delete top (1) from @tempsummary
end

select d_year as [Year], d_month as [Month], d_started as [Started], d_ended as [Ended], d_onhold as [On Hold], d_open as [Open]
from @summary

-- Validation scripts
select a_year, a_month, COUNT(tname) as [open] from @started
group by a_year, a_month, a_sort
order by a_year, a_sort

select b_year, b_month, COUNT(tname) as [close] from @ended
group by b_year, b_month, b_sort
order by b_year, b_sort

select c_year, c_month, COUNT(tname) as [hold] from @onhold
group by c_year, c_month, c_sort
order by c_year, c_sort