James McDonald II

USE Database
GO

CREATE PROCEDURE Schema.usp_tbl_SpecificData
AS

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

WITH CreateDate
AS (
	SELECT
		fe.EmailID
		,fe.CreateDate as CreatedDate
	FROM
		Schema.FactEmail as fe (NOLOCK)
	WHERE
		fe.CreateDate BETWEEN GETDATE()-30 and GETDATE()
	ORDER BY
		fe.CreateDate
),
TouchDate
AS
(
	SELECT
		fat.EmailID
		,MIN(fat.LogCreateDate) as TouchedDate
	FROM
		Schema.FactEmailAuditTrail as fat (NOLOCK)
	WHERE
		MIN(fat.LogCreateDate) BETWEEN GETDATE()-30 and GETDATE()
)

SELECT
	EmailID
	,c.CreatedDate
	,t.TouchedDate
	,CASE WHEN DATEDIFF(HOUR,c.CreatedDate,t.TouchedDate) <= 4 THEN 1 ELSE 0 END AS SLAMet
FROM
	CreateDate AS c (NOLOCK)
	INNER JOIN TouchDate AS t (NOLOCK)
		ON c.EmailID = t.EmailID
ORDER BY
	c.CreatedDate
	
END;