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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter