I am trying to calculate the average days per workflow.

Try it out

DROP TABLE IF EXISTS #ProcessLog;
GO

CREATE TABLE #ProcessLog
(
WorkFlow        VARCHAR(100),
ExecutionDate   DATE,
PRIMARY KEY (WorkFlow, ExecutionDate)
);
GO

INSERT INTO #ProcessLog VALUES
('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'),
('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'),
('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018');
GO

I found one solution with the INNER JOIN function, but i am wondering whetver there are a more simple method for example with PIVOT or UNPIVOT or Window function?

WITH cte as ( SELECT ROW_NUMBER() OVER(ORDER by Workflow) as n, * 
              FROM       #ProcessLog as p1 )

SELECT cte.workflow, (abs(DATEDIFF(DAY, cte.ExecutionDate, cte2.ExecutionDate))
        abs(DATEDIFF(DAY, cte2.ExecutionDate, cte3.ExecutionDate)) ) / 2 as average_days
FROM       CTE  
INNER JOIN CTE as cte2  ON cte.n  1 = cte2.n  AND cte.WorkFlow  = cte2.WorkFlow
INNER JOIN CTE as cte3  ON cte2.n  1 = cte3.n AND cte2.WorkFlow = cte3.WorkFlow  

CodePudding user response：

You need the LAG of the date in the CTE and the you can use DATEDIFF

DROP TABLE IF EXISTS #ProcessLog;


CREATE TABLE #ProcessLog
(
WorkFlow        VARCHAR(100),
ExecutionDate   DATE,
PRIMARY KEY (WorkFlow, ExecutionDate)
);


INSERT INTO #ProcessLog VALUES
('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'),
('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'),
('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018');

WITH CTE As(
  SELECT
  WorkFlow
  ,ExecutionDate
  ,  LAG(ExecutionDate) OVER(PARTITION BY WorkFlow ORDER BY ExecutionDate) date2 
  FROM
  #ProcessLog)
SELECT WorkFlow,AVG(DATEDIFF(day, date2,ExecutionDate)) FROM CTE GROUP BY WorkFlow