declare @sessionid varchar(36)
declare @started table (tname nvarchar(8), year nvarchar(4), month nvarchar(9), sort tinyint)
declare @ended table (tname nvarchar(8), year nvarchar(4), month nvarchar(9), sort tinyint)
declare @onhold table (tname nvarchar(8), year nvarchar(4), month nvarchar(9), sort tinyint)
declare @resolved table (tname nvarchar(8), year nvarchar(4), month nvarchar(9), sort tinyint)
declare @summary table (year nvarchar(4), month nvarchar(9), started smallint, ended smallint, onhold smallint, resolved smallint, [open] smallint)
declare @tempsummary table (year nvarchar(4), month nvarchar(9), started smallint, ended smallint, onhold smallint, resolved 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, year, month, sort)
    select 'started', DATEPART(yy, ProcessStarted), DATENAME(m, ProcessStarted), DATEPART(m, ProcessStarted)
    from ReportProcess with (NOLOCK)
    where SessionID = @sessionid
    insert into @ended (tname, year, month, 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, year, month, 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'
    insert into @resolved (tname, year, month, sort)
    select 'resolved', 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.Status = 'Resolved'
    and rp.Result = 'Resolved'
	fetch next from id into @sessionid
end
commit transaction [doit]
end try
begin catch
        select @sessionid, @@ERROR
        rollback transaction [doit]
end catch
close id
deallocate id

insert into @tempsummary
select oa.year, oa.month, COUNT(A.tname), COUNT(B.tname), COUNT(C.tname), COUNT(D.tname)
from @started A
full outer join @ended B on A.year = B.year and A.month = B.month and A.tname = B.tname
full outer join @onhold C on A.year = C.year and A.month = C.month and A.tname = C.tname
full outer join @resolved D on A.year = D.year and A.month = D.month and A.tname = D.tname
outer apply (select A.* union all select B.* union all select C.* union all select D.*) as oa
where oa.year is not null
group by oa.year, oa.month, oa.sort
order by oa.year, oa.sort

declare @cutoff nvarchar(17)

while (select COUNT(*) from @tempsummary) > 0
begin
        set @cutoff = (select top 1 '01 ' + month + ' ' + year from @tempsummary)
        insert into @summary
        select top 1 year, month, started, ended, onhold, resolved,
        ((select top 1 COUNT(*) from @started
          where CONVERT(smalldatetime, '01 ' + month + ' ' + year) <= CONVERT(smalldatetime, @cutoff))
        -
        (select top 1 COUNT(*) from @ended
         where CONVERT(smalldatetime, '01 ' + month + ' ' + year) <= CONVERT(smalldatetime, @cutoff)))
         from @tempsummary
        delete top (1) from @tempsummary
end

select year as [Year], month as [Month], started as [Open], ended as [Closed], onhold as [On Hold], resolved as [Resolved], [open] as [Still Open]
from @summary

-- Validation scripts
select year, month, COUNT(tname) as [Open] from @started
group by year, month, sort
order by year, sort

select year, month, COUNT(tname) as [Closed] from @ended
group by year, month, sort
order by year, sort

select year, month, COUNT(tname) as [On Hold] from @onhold
group by year, month, sort
order by year, sort

select year, month, COUNT(tname) as [Resolved] from @resolved
group by year, month, sort
order by year, sort