SQL - Yıllık Süreç İstatistikleri

declare @month as int
declare @year as int

/* Hangi yıldan alınmasını istiyorsanız belirtiniz */
select @month=1,@year=2024





IF OBJECT_ID('dbo.SurecStats', 'U') IS NULL
    BEGIN
    
	CREATE TABLE SurecStats (
								Name nvarchar(200), 
								Sayi int, 
								Yil int, 
								ay int 
	)
    
	END
ELSE
BEGIN
    truncate table dbo.SurecStats
END
 

  
  
WHILE (@year <= datepart(year,getdate()))
BEGIN
  
    WHILE (@month < 13)
    BEGIN
        INSERT INTO SurecStats
		select p.Name as 'Süreç İsmi',count(p.Name), MIN(DATEPART(year,i.StartDate)),MIN(DATEPART(MONTH,i.StartDate))
        from wflWorkItems wi with (nolock) left outer join
                      wflInstances i with (nolock) on i.Id=wi.Instance left outer join
                      wflProcessVersions pv on pv.Id =i.ProcessVersion left outer join
                      wflProcesses p on p.Id=pv.Process
                      where DATEPART(year,i.StartDate)=@year and DATEPART(MONTH,i.StartDate) =@month and i.Parent is null and wi.Previous is null
                      group by p.Name
                      order by 2 desc
  
        select @month=@month+1
    END
    select @year=@year+1
    select @month=1
END
  


DECLARE		@query nvarchar(max),
			@tblname nvarchar(max), 
			@colsUnpIvot nvarchar(max)='',
			@unwnatedcols nvarchar(max)='' 
	SET @tblname='SurecStats';
	SET @query=N'
					SELECT 
							@COLSUNPIVOT=STUFF((SELECT '+CHAR(39)+', '+CHAR(39)+'+  QUOTENAME(Yil)      
					FROM '+ @TBLNAME +' 
					group by QUOTENAME(Yil)
					FOR  XML PATH (' +CHAR(39)+''+CHAR(39)+')), 1, 1, ' +CHAR(39)+''+CHAR(39)+ ')'    

	execute sp_executesql @query, N'@COLSUNPIVOT NVARCHAR(MAX) output',        
	@COLSUNPIVOT = @COLSUNPIVOT OUTPUT


declare @dnmkSQK nvarchar(max) = '
									select 
										* 
									from ( 
											select 
												Name,
												Yil,
												Sum(sayi) as Sayi 
											from SurecStats 
									group by Name,Yil
									) src pivot (sum(Sayi) for Yil in ('+@COLSUNPIVOT+')) pvt
									order by 3 desc'
  


exec sp_executesql @dnmkSQK