How can I find the cumulative sum of ClosedIssueCount column? I have not been able to find the cumulative sum of this column.
Declare @FromDate DATE= '1/1/2016';
Declare @ToDate DATE='12/30/2016';
Declare @buid INT = 1;
with months (date)
AS
(
SELECT @FromDate
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<=@ToDate
)
select distinct YEAR(Date) as years,
MONTH(Date) as monnumber,
[MonthName] = DATENAME(mm ,Date) ,
--CloseIssueCount
((select count(distinct tbl_Issues.VulName) AS C
from tbl_Issues
inner join tbl_apptestdetails on tbl_Issues.testdetailid = tbl_apptestdetails.testdetailid
inner join tbl_applicationlist on tbl_apptestdetails.appid = tbl_applicationlist.appid
inner join tbl_bu on tbl_applicationlist.buid = tbl_bu.buid
where tbl_Issues.Status=2 and DATENAME(mm ,Date) = DATENAME(mm ,tbl_Issues.CloseDt) and tbl_bu.buid IN (@buid)
) )AS ClosedIssueCount
from months
cross join tbl_apptestdetails
inner join tbl_Issues outertbl_issues on tbl_apptestdetails.testdetailid = outertbl_issues.testdetailid
inner join tbl_applicationlist on tbl_apptestdetails.appid=tbl_applicationlist.appid
inner join tbl_bu on tbl_bu.buid=tbl_applicationlist.buid
where tbl_bu.bustatus='Active' and tbl_bu.buid IN (@buid)
