running totals?

[wbodger]

I have this code

with confirmed as
(
SELECT convert(varchar(3),inidate,100) as [month],datepart(YY, inidate) as [year]
		, COUNT(inidate) as counts
		, row_number() OVER (ORDER BY datepart(YY, inidate), datepart(mm,inidate)) rnk
FROM [db].[dbo].[tblFinalG]
where DELETEFLAG=0
GROUP BY convert(varchar(3),inidate,100),DATEPART(mm,inidate),datepart(YY, inidate)
),

 consented as
(
SELECT convert(varchar(3), STUDY_CONSENT_DATE,100) as [month], datepart(YY, STUDY_CONSENT_DATE) as [year]
		, COUNT(STUDY_CONSENT_DATE) as counts
		, row_number() OVER (ORDER BY datepart(YY, STUDY_CONSENT_DATE), datepart(mm,STUDY_CONSENT_DATE)) rnk
FROM [db].[dbo].TBLSTUDYDOC
where DELETEFLAG=0 and ENTRYFLAG=1
GROUP BY convert(varchar(3), STUDY_CONSENT_DATE,100),DATEPART(mm, STUDY_CONSENT_DATE),datepart(YY, STUDY_CONSENT_DATE)
)

select cs.[month], cs.[year], cs.counts as consented, cf.counts as confirmed from consented cs full join confirmed cf
	on cs.[month]=cf.[month] and cs.[year]=cf.[year]
order by cs.rnk, cf.rnk

To give me this dataset

month	year	consented	confirmed
Sep	2011	1	NULL
Oct	2011	23	NULL
Nov	2011	47	17
Dec	2011	60	26
Jan	2012	101	53
Feb	2012	87	86
Mar	2012	112	86
Apr	2012	130	77
May	2012	165	137
Jun	2012	165	108
Jul	2012	172	118
Aug	2012	184	167
Sep	2012	182	151
Oct	2012	252	231
Nov	2012	262	189
Dec	2012	196	209
Jan	2013	288	242
Feb	2013	280	230
Mar	2013	127	95

but I would like to insert a running total for consented and confirmed, so

month	year	consented   constotal	confirmed  conftotal
Sep	2011	1	    1           NULL       NULL
Oct	2011	23	    24          NULL       NULL
Nov	2011	47	    71          17         17
Dec	2011	60	    131         26         43
Jan	2012	101	    232         53         96
Feb	2012	87	    219         86         182

I was trying to avoid doing it in the code on a web page, but am not quite sure how to go about it. Any help?

wb

 

[wbodger]

What do you know, I got this one to work! I jsut changed the final query to:

select cs.[month], 
	cs.[year], 
	cs.counts as consented, 
	(select SUM(cs2.counts) from consented cs2 where cs2.rnk<=cs.rnk) as constotal,
	cf.counts as confirmed,
	(select SUM(cf2.counts) from confirmed cf2 where cf2.rnk<=cf.rnk) as conftotal
from consented cs full join confirmed cf on cs.[month]=cf.[month] and cs.[year]=cf.[year]
order by cs.rnk, cf.rnk

and it gave me just what I was looking for.

wb