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
Comments