SQL Example 1

USE Operations;

--Temp Table WITH OFS
IF OBJECT_ID('tempdb..#ofs') IS NOT NULL BEGIN DROP TABLE #ofs END

--fill ofs temp table
SELECT 
	Location
	,EFSPName
	,CaseNumber
	,CaseCategory
	,CaseTypeDesc
	,OrderNumber
	,EnvelopeID
	,FilingID
	,FilingType
	,FilingCodeDescription
	,FilingCode
	,RejectCode
	,RejectComment
	,FirmName
	,FilerEmail
	,FilerFirstName
	,FilerLastName
	,Status
	,DD2.Date AS ReviewedDate
	,ReviewerEmail
	,ReviewerFirstName
	,ReviewerLastName
	,DD.Date As SubmittedDate
	,NLR.CountyNum
	,NLR.County
INTO #ofs
FROM [OdyReporting].[Report].[OFSReporting] OFS
	LEFT JOIN OdyReporting.dbo.NodeListReporting NLR ON OFS.Location = NLR.OrgUnitName
	LEFT JOIN OdyReporting.Shared.DateDim DD ON OFS.DateSubmittedKey = DD.DateKey
	LEFT JOIN OdyReporting.Shared.DateDim DD2 ON OFS.DateReviewKey = DD2.DateKey
 WHERE 
 	CaseCategory = 'Civil'
  		AND 
  	--filter to just complaints
	(FilingCodeDescription LIKE '%complaint%' 
		AND FilingCodeDescription NOT LIKE '%Amended%' 
		AND FilingCodeDescription NOT LIKE '%Motion%'
		AND FilingCodeDescription NOT LIKE '%Answer%');

--cte combining document information AND creating preference rank for case number link
WITH doc AS (
SELECT Distinct
	D.DocumentID
	,D.DocumentTypeID
	,D.Name
	,D.Description
	,D.TimestampCreate
	,Case 
		When D.TimestampChange is Null then D.TimestampCreate
		Else D.TimestampChange
	End AS TimeStamp
	,D.DocumentOrigMethodID
	,D.SecurityToken
	,D.OriginatingDocumentID
	,DV.DocumentVersionID
	,DV.EffectiveDate
	,DV.DocumentSecurityGroupID
	,DSC.NotForPublicView
	,SecC.Code SecGrpCode
	,SecC.Description SecGrpDes
	,DocC.Code DocTypeCode
	,DocC.Description DocTypeDes
	,PL.ParentTypeID
	,PL.ParentID
	,SPT.Description parentType
	,Case When PL.ParentTypeID  = 1 Then 1
		When PL.ParentTypeID = 2 Then 2
		When PL.ParentTypeID = 3 Then 3
		When PL.ParentTypeID = 4 then 4
		Else 5
	End AS Likerate
FROM dbo.Doc D
	LEFT JOIN dbo.DocVersion DV ON D.DocumentID = DV.DocumentID
	LEFT JOIN dbo.uDocSecGrp DSC ON DV.DocumentSecurityGroupID = DSC.DocumentSecurityGroupID
	LEFT JOIN dbo.uCode SecC ON DV.DocumentSecurityGroupID = SecC.CodeID
	LEFT JOIN dbo.uCode DocC ON D.DocumentTypeID = DocC.CodeID
	LEFT JOIN dbo.ParentLink PL ON D.DocumentID = PL.DocumentID
	LEFT JOIN Operations.dbo.sParentType sPT ON PL.ParentTypeID = SPT.ParentTypeID
WHERE 
	
			--filter to just complaints
	Name LIKE '%complaint%' 
	AND Name NOT LIKE '%Amended%' 
	AND Name NOT LIKE '%Motion%'
	AND Name NOT LIKE '%Answer%'
	AND Name NOT LIKE 'Summons%'
	AND Name NOT LIKE '%Affidavit%'
	AND D.TimestampCreate > '2016-01-01'
),

--cte pulling in case number AND row_number() ranking based on previous preference rank
DocCase AS (
SELECT 
	d.*
	,CAHsub.CaseNbr As CaseNumber
	,CAHsub.CountyNum

	,ROW_NUMBER() Over(Partition By d.DocumentID Order By d.LIKErate) AS RankCaseID -- rank by prefered link method
FROM doc d

	LEFT JOIN (
		SELECT 
			CAH.CaseID
			,CAH.CaseNbr
			,NLR.CountyNum
		FROM Justice.dbo.CaseAssignHist CAH
			JOIN OdyReporting.dbo.NodeListReporting NLR ON CAH.NodeID = NLR.NodeID
			) CAHsub ON d.parentID = CAHsub.caseID AND d.ParentTypeID = 1 
	)
--combining OFS temp table WITH final DocCase cte
--joining on CaseNumber for perfered link types AND e files WITH status accepted 

SELECT Distinct
	ofs.CaseNumber
	,CaseCategory
	,Location
	,ofs.FilingID
	,ofs.FilingCodeDescription
	,dc.Name As DocDesc
	,ofs.FilingType
	,ofs.FilerLastName
	,ofs.FilerFirstName
	,ofs.FilerEmail
	,dc.DocumentID
	,ofs.Status
	,ofs.ReviewerLastName
	,ofs.ReviewerFirstName
	,ofs.ReviewerEmail
	,ofs.SubmittedDate
	,ofs.ReviewedDate
	,dc.TimestampCreate TimestampCreateDoc
	,Cast(dc.EffectiveDate as Date) As EffectiveDate
	,dc.NotForPublicView
	,DATEDIFF(DAY,SubmittedDate,TimestampCreate) As DaysSumbitedToPublic --Calculate hour/24 till timestampcreated

FROM #ofs ofs 
	LEFT JOIN DocCase dc ON ofs.CaseNumber = dc.CaseNumber AND Status = 'Accepted' 
														   AND ofs.CountyNum = dc.CountyNum

WHERE  
	(NotForPublicView = 0
		AND (Cast(ofs.ReviewedDate As date) <= Cast(dc.TimestampCreate As date)
		AND Cast(ofs.SubmittedDate As date) = Cast(dc.EffectiveDate As date)))
		OR Status = 'Rejected'
ORDER BY
	reviewedDate ASC