WITH Pvt AS
	(
	SELECT
		issue_id,[created]  ,[assign],[Inprogress],[Complete]
	  FROM
		(
			SELECT
				a.issue_id,
				a.created_dt,
				f.name AS status_new
			  FROM jrn a INNER JOIN details b
				ON a.id = b.jrn_id
			CROSS APPLY
			(
			 SELECT name FROM status f
			  WHERE id = b.new AND b.prop_key = 'status_id'
			) f
		) t1
	PIVOT
		(
		 MIN(created_dt) FOR status_new IN ([created],[assign],[Inprogress],[Complete])
		 ) AS pvt
	)
SELECT
	a.issue_id,
	c.name AS [Status],
	a.created AS Created_dt,
	a.assign AS Assign_dt,
	a.inprogress AS Inprogress_dt,
	a.Complete AS Complete_dt,
	b.[user]
  FROM PVT a LEFT OUTER JOIN
	(
	SELECT
		a.issue_id,
		c.name AS [USER]
	  FROM jrn a INNER JOIN details b
		ON b.jrn_id = a.id INNER JOIN users c
		ON b.new = c.id
	 WHERE b.prop_key = 'user_id'
	 ) b
ON a.issue_id = b.issue_id LEFT OUTER JOIN
	(
	SELECT
		a.ISSUE_ID,
		b.name
	  FROM 
		(
		SELECT
			a.issue_id,
			MAX(b.new) AS status_id
		  FROM jrn a INNER JOIN details b
			ON b.jrn_id = a.id 
		 GROUP BY a.issue_id
		) a INNER JOIN status b
		ON a.status_id = b.id
	) c
    ON a.issue_id = c.issue_id