Page tree
Skip to end of metadata
Go to start of metadata
SQL - Süreç istatistikleri
IF OBJECT_ID('dbo.SurecStats', 'U') IS NULL 
	CREATE TABLE SurecStats (Name  nvarchar(200), Sayi  int, Yil  int, ay  int )

declare @month as int
declare @year as int

select @month=1,@year=2017


WHILE (@year < 2019)
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 having count(p.Name) > 10
                      order by 2 desc

select @month=@month+1
END
select @year=@year+1
select @month=1
END

select top 20 * from ( select Name,Yil,Sum(sayi) as Sayi from SurecStats group by Name,Yil) src  pivot (sum(Sayi) for Yil in ([2017],[2018])) pvt
order by 3 desc
  • No labels