How to get the cumulative output for this query.

I have 2 tables.
1. geotable containing - state,district,source.
2. mstchvs where dataentry is done.
It contains state,source,district,rfeeddate,other fields.
I want this kind of output for given range of rfeeddate.
State district source Count Cum.Count

I am using the following query.


select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0))
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1,2,3

This is the result for between dates.
Now to get the cumulative result i will write rfeeddate<='2012-3-31'

But how to append the cumulative result for above query
searchsanjaysharmaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pratima PharandeConnect With a Mentor Commented:
Select X.state,X.district,X.source,X.res,Y.cum_res From

(select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as  res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) X
inner join
(
select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as cum_res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 andrfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) Y
on X.state = Y.state and X.district = Y.district and X.source = Y.source
0
 
searchsanjaysharmaAuthor Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.