SQL - Süreç istatistikleri
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

declare @month as int
declare @year as int
 
select @month=1,@year=2017
 
 
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
 
select * from ( select Name,Yil,Sum(sayi) as Sayi from SurecStats group by Name,Yil) src  pivot (sum(Sayi) for Yil in ([2018],[2019])) pvt
order by 3 desc
  • No labels