SQL Faster execution with Joins instead of Inner query Example

PHOTO EMBED

Thu Jul 07 2022 08:27:08 GMT+0000 (UTC)

Saved by @paulbarry

	SELECT DISTINCT
	t.PSID,
	Count (t.DocID) AS Overall_Total,
	a.Annual_Total,
	b.MinDocDate,
	c.MaxDocDate,
	d.MinStoreDate,
	e.MaxStoreDate,
	
	SUM(IIF(MONTH(t.DocDate)= 1 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS JAN, 
	SUM(IIF(MONTH(t.DocDate)= 2 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS FEB, 
	SUM(IIF(MONTH(t.DocDate)= 3 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS MAR,
	SUM(IIF(MONTH(t.DocDate)= 4 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS APR,
	SUM(IIF(MONTH(t.DocDate)= 5 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS MAY,
	SUM(IIF(MONTH(t.DocDate)= 6 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS JUN,
	SUM(IIF(MONTH(t.DocDate)= 7 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS JUL,
	SUM(IIF(MONTH(t.DocDate)= 8 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS AUG,
	SUM(IIF(MONTH(t.DocDate)= 9 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS SEP,
	SUM(IIF(MONTH(t.DocDate)= 10 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS OCT,
	SUM(IIF(MONTH(t.DocDate)= 11 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS NOV,
	SUM(IIF(MONTH(t.DocDate)= 12 AND YEAR(t.DocDate) = YEAR(GETDATE()),1,0)) AS DEC
	FROM tbl_OLIS_DOCS_DocumentIndex t

	LEFT JOIN 
	(SELECT PSID, COUNT(DOCID) AS Annual_Total FROM tbl_OLIS_DOCS_DocumentIndex WHERE YEAR(DocDate) = YEAR(GETDATE()) GROUP BY PSID) a
	ON t.PSID = a.PSID

	LEFT JOIN
	(SELECT DISTINCT PSID, min(DocDate) AS MinDocDate FROM tbl_OLIS_DOCS_DocumentIndex GROUP BY PSID) b
	ON t.PSID = b.PSID

	LEFT JOIN
	(SELECT distinct PSID, max(DocDate) AS MaxDocDate FROM tbl_OLIS_DOCS_DocumentIndex GROUP BY PSID) c
	ON t.PSID = c.PSID

		LEFT JOIN
	(SELECT DISTINCT PSID, min(DocStoredDate) AS MinStoreDate FROM tbl_OLIS_DOCS_DocumentIndex GROUP BY PSID) d
	ON t.PSID = d.PSID

	LEFT JOIN
	(SELECT distinct PSID, max(DocStoredDate) AS MaxStoreDate FROM tbl_OLIS_DOCS_DocumentIndex GROUP BY PSID) e
	ON t.PSID = e.PSID

	GROUP BY t.PSID, a.Annual_Total, 	b.MinDocDate, c.MaxDocDate, d.MinStoreDate, e.MaxStoreDate
	ORDER BY T.PSID;
	
	


	--G/2478 & G/2503


content_copyCOPY